RadGridView QueryableCollectionView fed by IQueryable Takes a long time to Hit Last Page

7 posts, 0 answers
  1. Mike
    Mike avatar
    11 posts
    Member since:
    Jun 2014

    Posted 19 Mar 2015 Link to this post

    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?
  2. Mike
    Mike avatar
    11 posts
    Member since:
    Jun 2014

    Posted 19 Mar 2015 Link to this post

    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?
  3. Mike
    Mike avatar
    11 posts
    Member since:
    Jun 2014

    Posted 19 Mar 2015 Link to this post

    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
  4. Mike
    Mike avatar
    11 posts
    Member since:
    Jun 2014

    Posted 19 Mar 2015 Link to this post

    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;
    }
  5. Dimitrina
    Admin
    Dimitrina avatar
    3769 posts

    Posted 21 Mar 2015 Link to this post

    Hi Mike,

    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.

     
  6. Mike
    Mike avatar
    11 posts
    Member since:
    Jun 2014

    Posted 23 Mar 2015 in reply to Dimitrina Link to this post

    Thanks, but is there any way to make it perform better?
  7. Dimitrina
    Admin
    Dimitrina avatar
    3769 posts

    Posted 24 Mar 2015 Link to this post

    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
     

    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.

     
Back to Top