We have several paged grids that should hold many records. I am doing a test for a grid to hold our 2 million invoice records. At first I was bringing all of the records into memory using a .ToList() on the Linq query that feeds the grid. That was taking a long time and giving an "out of memory" exception. Then I read " If you provide IQueryable for QueryableCollectionView all operations will be executed directly on your data-base server using the query provider". Again, it is a business requirement that we use paging (page size of 8), so when I bring up the first page, it is fine, and if I page through the first 2 or 3 pages it seems to work pretty well. However, when I click the "Last Page" button, it takes almost 30 seconds (and I have even seen it time out several times). That seemed funny to me, so I wrote a SQL query that essentially does the same thing as going to the last page, and it took less than a second.
So, my first question is ... Can I get hold of the actual T-SQL that it is running on my database?
My 2nd question is ... Can I somehow optimize this query to run faster?
So, my first question is ... Can I get hold of the actual T-SQL that it is running on my database?
My 2nd question is ... Can I somehow optimize this query to run faster?
6 Answers, 1 is accepted
0
Mike
Top achievements
Rank 1
answered on 19 Mar 2015, 06:39 PM
Note: I was able to use SQL Profiler to get the query that it is actually running. Is there a way to get it in visual studio somehow without using Profiler?
0
Mike
Top achievements
Rank 1
answered on 19 Mar 2015, 06:41 PM
In case it helps, Here is my query which takes less than a second:
SELECT *
FROM
(
select fi.InvoiceID, fi.InvoiceNumber, fi.AddDTM,
SUM(fp.AppliedAmount) PaidAmount,
ROW_NUMBER() OVER (ORDER BY fi.InvoiceID) AS RowNumber
from fInvoice fi
LEFT join fPayment fp on fp.InvoiceID = fi.InvoiceID
Group By fi.InvoiceID, fi.InvoiceNumber, fi.AddDTM
) InvoicePaging
WHERE RowNumber >= 1846137
This is what profiler found that it was running:
SELECT TOP (8)
[Project2].[InvoiceID] AS [InvoiceID],
[Project2].[InvoiceNumber] AS [InvoiceNumber],
[Project2].[AddDTM] AS [AddDTM],
[Project2].[C1] AS [C1]
FROM ( SELECT [Project2].[InvoiceID] AS [InvoiceID], [Project2].[InvoiceNumber] AS [InvoiceNumber], [Project2].[AddDTM] AS [AddDTM], [Project2].[C1] AS [C1], row_number() OVER (ORDER BY [Project2].[InvoiceID] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[InvoiceID] AS [InvoiceID],
[Extent1].[InvoiceNumber] AS [InvoiceNumber],
[Extent1].[AddDTM] AS [AddDTM],
(SELECT
SUM([Join1].[A1]) AS [A1]
FROM ( SELECT
CASE WHEN ([Project1].[AppliedAmount] IS NULL) THEN cast(0 as decimal(18)) ELSE [Project1].[AppliedAmount] END AS [A1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT
[Extent2].[AppliedAmount] AS [AppliedAmount]
FROM [dbo].[fPayment] AS [Extent2]
WHERE [Extent1].[InvoiceID] = [Extent2].[InvoiceID] ) AS [Project1] ON 1 = 1
) AS [Join1]) AS [C1]
FROM [dbo].[fInvoice] AS [Extent1]
) AS [Project2]
) AS [Project2]
WHERE [Project2].[row_number] > 1846136
ORDER BY [Project2].[InvoiceID] ASC
SELECT *
FROM
(
select fi.InvoiceID, fi.InvoiceNumber, fi.AddDTM,
SUM(fp.AppliedAmount) PaidAmount,
ROW_NUMBER() OVER (ORDER BY fi.InvoiceID) AS RowNumber
from fInvoice fi
LEFT join fPayment fp on fp.InvoiceID = fi.InvoiceID
Group By fi.InvoiceID, fi.InvoiceNumber, fi.AddDTM
) InvoicePaging
WHERE RowNumber >= 1846137
This is what profiler found that it was running:
SELECT TOP (8)
[Project2].[InvoiceID] AS [InvoiceID],
[Project2].[InvoiceNumber] AS [InvoiceNumber],
[Project2].[AddDTM] AS [AddDTM],
[Project2].[C1] AS [C1]
FROM ( SELECT [Project2].[InvoiceID] AS [InvoiceID], [Project2].[InvoiceNumber] AS [InvoiceNumber], [Project2].[AddDTM] AS [AddDTM], [Project2].[C1] AS [C1], row_number() OVER (ORDER BY [Project2].[InvoiceID] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[InvoiceID] AS [InvoiceID],
[Extent1].[InvoiceNumber] AS [InvoiceNumber],
[Extent1].[AddDTM] AS [AddDTM],
(SELECT
SUM([Join1].[A1]) AS [A1]
FROM ( SELECT
CASE WHEN ([Project1].[AppliedAmount] IS NULL) THEN cast(0 as decimal(18)) ELSE [Project1].[AppliedAmount] END AS [A1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT
[Extent2].[AppliedAmount] AS [AppliedAmount]
FROM [dbo].[fPayment] AS [Extent2]
WHERE [Extent1].[InvoiceID] = [Extent2].[InvoiceID] ) AS [Project1] ON 1 = 1
) AS [Join1]) AS [C1]
FROM [dbo].[fInvoice] AS [Extent1]
) AS [Project2]
) AS [Project2]
WHERE [Project2].[row_number] > 1846136
ORDER BY [Project2].[InvoiceID] ASC
0
Mike
Top achievements
Rank 1
answered on 19 Mar 2015, 06:50 PM
Also, if it helps. Here is the Linq Query that feeds the grid:
public IQueryable<InvoiceTest> GetInvoicesQueryable(FPI.Misc.Backup_Flex_BigEntities context)
{
var result = (from invc in context.fInvoices
select new InvoiceTest
{
InvoiceID = invc.InvoiceID,
InvoiceNumber = invc.InvoiceNumber,
AddDTM = invc.AddDTM,
PaidAmount = invc.fPayments.DefaultIfEmpty().Sum(x => x.AppliedAmount ?? 0)
}).OrderBy(x => x.InvoiceID);
return result;
}
public IQueryable<InvoiceTest> GetInvoicesQueryable(FPI.Misc.Backup_Flex_BigEntities context)
{
var result = (from invc in context.fInvoices
select new InvoiceTest
{
InvoiceID = invc.InvoiceID,
InvoiceNumber = invc.InvoiceNumber,
AddDTM = invc.AddDTM,
PaidAmount = invc.fPayments.DefaultIfEmpty().Sum(x => x.AppliedAmount ?? 0)
}).OrderBy(x => x.InvoiceID);
return result;
}
0
Hi Mike,
Regards,
Dimitrina
Telerik
I will try to explain the way RadDataPager works in some further details. Every time you navigate to a page, it internally builds a query appending Skip and Take clauses and then this query is to be executed on the server.
When binding the pager to a LINQ-enabled data source (i.e. IQueryable), the paging will happen on the server out-of-the-box. It will automatically append Skip(n) and Take(m) statements to the query. You can also check our WPF Demos, more specifically the DataServiceDataSource First Look demo for an example on how the query is built and executed.Regards,
Dimitrina
Telerik
Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.
0
Mike
Top achievements
Rank 1
answered on 23 Mar 2015, 01:57 PM
Thanks, but is there any way to make it perform better?
0
Hello,
In case you already feed RadGridView with IQueryable, there is no more to suggest further. You can also check all our examples on different usages of RadDataPager with RadGridView and the DataSource controls.
Regards,
Dimitrina
Telerik
In case you already feed RadGridView with IQueryable, there is no more to suggest further. You can also check all our examples on different usages of RadDataPager with RadGridView and the DataSource controls.
Regards,
Dimitrina
Telerik
Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.