locked
How to load a huge data to a webgrid without using paging? RRS feed

  • Question

  • User-1651604128 posted

    In my asp.ne mvc web app, I need to display a webgrid with a huge log data records ( about 960000 records, and it will increase every day),

    since it is converted from a legacy system and the original design used data grid with scroll feature, so I want to apply the same in my mvc webgrid, 

    since the data is huge, it is not possible to load all data, I want to use finite scroll feature to only load 20 records and it will add next 20 more records when user scroll bar.

    but I am stuck at the beginning of this line of codes:

    var GridData = db.Database.SqlQuery<tbl_Product_Log>("SELECT * FROM tbl_Product_Log").Take(20).ToList();

    I am intended to only get the first 20 records, actually, it seems it query all records which caused the system frozen and crushed.

    Does anybody know how to make it working to only query the first 20 records?

    Thanks a lot,

    Tuesday, February 5, 2019 7:07 PM

Answers

  • User475983607 posted

    Your SQL query specifically returns ever record in the table.  

    SELECT [CustomerID]
          ,[LastName]
      FROM [SalesLT].[Customer]
      ORDER BY [CustomerID], [LastName]
      OFFSET 0 ROWS
      FETCH NEXT 10 ROWS ONLY;

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 7:38 PM
  • User-474980206 posted

    the following:

      var GridData = db.Database.SqlQuery<tbl_Product_Log>("SELECT * FROM tbl_Product_Log").Take(20).ToList();

    because the query is provided, the .Take(20) is  applied after the query is run (just reads the first 20 rows of the returned dataset). if linq to sql is used

       var GridData = db.Database.tbl_Product_Log.Take(20).ToList();

    the Take(20) becomes part of the query. as suggested you need to move the take into the query.

        var GridData = db.Database.SqlQuery<tbl_Product_Log>("SELECT top 20 * FROM tbl_Product_Log").ToList();

    to do paging you will need an order by a column, so you can fetch a page:

    var pageSize = 20;
    var pageNumber = 2;
    var GridData = db.Database.SqlQuery<tbl_Product_Log>($@"
       select * 
       from tbl_Product_Log 
       order by MyCol 
       offset {pageSize * (pageNumber - 1)} rows
       fetch next {pageSize} rows only;
    ").ToList();
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 9:06 PM
  • User1120430333 posted

    Myself, I wouldn't even bother with EF and Linq. I would use  the EF backdoor, ADO.NET, SQL Command Object, a paging stored procedure, a datareader and a custom object in a collection loading the grid as a data source.

    https://www.sqlservergeeks.com/t-sql-paging-stored-procedure/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 9:22 PM

All replies

  • User475983607 posted

    Your SQL query specifically returns ever record in the table.  

    SELECT [CustomerID]
          ,[LastName]
      FROM [SalesLT].[Customer]
      ORDER BY [CustomerID], [LastName]
      OFFSET 0 ROWS
      FETCH NEXT 10 ROWS ONLY;

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 7:38 PM
  • User753101303 posted

    Hi,

    And the old system is not even offering some filtering capabilities? Usually you move filtering earlier ie before showing a full grid of data compared with a desktop app.

    It's hard to believe a system that just allow a user browse through 960000 rows could be satisfactory.

    Tuesday, February 5, 2019 8:11 PM
  • User475983607 posted

    PatriceSc

    It's hard to believe a system that just allow a user browse through 960000 rows could be satisfactory.

    Agreed, especially if there are no indexes. 

    Tuesday, February 5, 2019 8:43 PM
  • User-474980206 posted

    the following:

      var GridData = db.Database.SqlQuery<tbl_Product_Log>("SELECT * FROM tbl_Product_Log").Take(20).ToList();

    because the query is provided, the .Take(20) is  applied after the query is run (just reads the first 20 rows of the returned dataset). if linq to sql is used

       var GridData = db.Database.tbl_Product_Log.Take(20).ToList();

    the Take(20) becomes part of the query. as suggested you need to move the take into the query.

        var GridData = db.Database.SqlQuery<tbl_Product_Log>("SELECT top 20 * FROM tbl_Product_Log").ToList();

    to do paging you will need an order by a column, so you can fetch a page:

    var pageSize = 20;
    var pageNumber = 2;
    var GridData = db.Database.SqlQuery<tbl_Product_Log>($@"
       select * 
       from tbl_Product_Log 
       order by MyCol 
       offset {pageSize * (pageNumber - 1)} rows
       fetch next {pageSize} rows only;
    ").ToList();
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 9:06 PM
  • User1120430333 posted

    Myself, I wouldn't even bother with EF and Linq. I would use  the EF backdoor, ADO.NET, SQL Command Object, a paging stored procedure, a datareader and a custom object in a collection loading the grid as a data source.

    https://www.sqlservergeeks.com/t-sql-paging-stored-procedure/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 5, 2019 9:22 PM
  • User-1651604128 posted

    Hi,

    And the old system is not even offering some filtering capabilities? Usually you move filtering earlier ie before showing a full grid of data compared with a desktop app.

    It's hard to believe a system that just allow a user browse through 960000 rows could be satisfactory.

    Hi PatriceSc,

    Thanks a lot for your comments, the old system did have some filtering capabilities with some checkboxes and dropdown list, there are Clear and Filter button doing that, but in default, when the form (it is MS Access form) is loaded, it is loading the entire data there, then user can filter it by using those filter fields. Even in Access form the data is taking a bit of time to load, and in asp.net mvc, it crushes the application if I allow the entire data is loaded.

    I will use Finite scrolling feature to Webgrid to load 20 records every move of scroll bar, similar as paging.

    Please let me know if you have any other better idea,

    again, thank you for your help,

    Wednesday, February 6, 2019 12:15 PM
  • User-1651604128 posted

    the following:

      var GridData = db.Database.SqlQuery<tbl_Product_Log>("SELECT * FROM tbl_Product_Log").Take(20).ToList();

    because the query is provided, the .Take(20) is  applied after the query is run (just reads the first 20 rows of the returned dataset). if linq to sql is used

       var GridData = db.Database.tbl_Product_Log.Take(20).ToList();

    the Take(20) becomes part of the query. as suggested you need to move the take into the query.

        var GridData = db.Database.SqlQuery<tbl_Product_Log>("SELECT top 20 * FROM tbl_Product_Log").ToList();

    to do paging you will need an order by a column, so you can fetch a page:

    var pageSize = 20;
    var pageNumber = 2;
    var GridData = db.Database.SqlQuery<tbl_Product_Log>($@"
       select * 
       from tbl_Product_Log 
       order by MyCol 
       offset {pageSize * (pageNumber - 1)} rows
       fetch next {pageSize} rows only;
    ").ToList();

    Hi bruce, Thank you so much for your help, this is exactly what I wanted.  Stored procedure may be much more efficient, but that needs the updates of DB, so I will implement your code so far.

    Again, much appreciated your help,

    Peter

    Wednesday, February 6, 2019 3:37 PM
  • User-2054057000 posted

    Execute some stored procedure that implements custom paging with your Entity Framework code and you will start getting results without timeout.

    You can properly understand the implementation part of Custom Paging Stored Procedure at this tutorial.

    Wednesday, February 6, 2019 4:00 PM