cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
coder57
Frequent Visitor

Overcoming delegation with SQL server

Hi There

 

I have read the docs for delegation (delegable-functions ) and my understanding is with SQL server delegation can be achieve using the function FILTER with the operator > (greater than). First question is that correct?

My SQL table has the following structure

 

CREATE TABLE [dbo].[TestTable](
	[ID] [int] NOT NULL,
	[Row] [nvarchar](50) NOT NULL,
	[Status] [nvarchar](50) NOT NULL,
	[StatusCompleteNo] [nvarchar](50) NOT NULL,
	[SortOrder] [int] NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

 

There are over 3000 rows in the table for testing

In my test app I have the following statement for the "on visible" event

 

ClearCollect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>0),ID));

 

However in monitor I get the following

 

"info": "Partial dataset. 500 rows collected.",
    "issue": "Data from [dbo].[TestTable] is/are truncated to 500 (respectively). The function was applied against incomplete dataset and may return incorrect results."
  }

 

Can any point out the issue please

 

Please note the final aim is to apply this to a SQL view but I am having issues with view currently

 

Many Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

I think you misunderstand what delegation really means.  Delegation means that selection of records is done on the server.  But that doesn't mean that the data set returned can be bigger than the data row limit.  The data row limit is a limit on how big a data set can be retrieved.  So if you are using a non-delegable function the data is retrieved up to the limit and then the function is applied.  With a delegable function the selection is done on the server and the resulting data set is retrieved up to the size imposed by the data row limit.  To collect all 3,000 rows, even using a delegable function, you would need to issue 6 Collect() commands.

ClearCollect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>0),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=501 && ID <1001),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=1001 && ID <1501),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=1501 && ID <2001),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=2001 && ID <2501),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=2501 && ID <3001),ID));

  But I don't recommend that approach since it can be slow.  Use delegable functions to retrieve only the records you need, not the entire data set.  If you need to work on 3,000 records con-currently consider writing a stored procedure and invoking it through Power Automate. 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

1 REPLY 1
Pstork1
Dual Super User III
Dual Super User III

I think you misunderstand what delegation really means.  Delegation means that selection of records is done on the server.  But that doesn't mean that the data set returned can be bigger than the data row limit.  The data row limit is a limit on how big a data set can be retrieved.  So if you are using a non-delegable function the data is retrieved up to the limit and then the function is applied.  With a delegable function the selection is done on the server and the resulting data set is retrieved up to the size imposed by the data row limit.  To collect all 3,000 rows, even using a delegable function, you would need to issue 6 Collect() commands.

ClearCollect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>0),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=501 && ID <1001),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=1001 && ID <1501),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=1501 && ID <2001),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=2001 && ID <2501),ID));
Collect(colQC13List,Sort(Filter('[dbo].[TestTable]',ID>=2501 && ID <3001),ID));

  But I don't recommend that approach since it can be slow.  Use delegable functions to retrieve only the records you need, not the entire data set.  If you need to work on 3,000 records con-currently consider writing a stored procedure and invoking it through Power Automate. 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,699)