locked
Load Data Fast RRS feed

  • Question

  • User2041008840 posted

    Hi, 
    I have a table in database which is contains more than 8000+ Rows and 8 columns 
    So when i Retrieve it into my asp.net core project it will take almost 40 seconds to load into View. 


    but this same table load full  in less than in 4 seconds in classic asp.net (aspx) page. 

    How do i make it fast loading ? 

    this is my query : 
    _context.Jobplan.ToList()

    in classic aspx 
    select * from Jobplan

    Tuesday, November 12, 2019 4:29 AM

Answers

  • User-1780421697 posted

    If you are performing only read operation then you need to use 

    .AsNoTracking()

    That will improve the performance, On database level you can perform some indexing to improve performance, query optimization play important role.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 12, 2019 5:33 AM
  • User541108374 posted

    Hi,

    indeed, AsNoTracking has a good effect on performance (http://www.krisvandermast.com/post/2018/02/03/Use-AsNoTracking-to-speed-up-Entity-Framework-performance.html). 

    I suggest you also put breakpoints in your code to see where things go slow. If you test the same query or call to the view in SQL Management Studio, does it take the same amount of time (~ 40 seconds)? 

    Kris.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 12, 2019 8:09 AM
  • User1120430333 posted

    If using EF, the data is retrieved using T-SQL too,  like the classic ASP.NET example, but EF martializes each record  into an object.  A select  * from Jobplan  is doing what? All it's doing is selecting the records. It is not taking each record, creating a Jobplan object and loading each object into a List<T> that the EF statment is doing _context.Jobplan.ToList(). So of course,  EF is going to take longer to materialize 8,000+ objects from 8,000+ records read loading them into a List<T>.

    Maybe, you are being too greedy with columns,  and you don't need all the colums, which could affect overall query speed in object materialization.

    Other things to consider.

    https://www.thereformedprogrammer.net/entity-framework-core-performance-tuning-a-worked-example/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 13, 2019 1:03 AM
  • User-1780421697 posted

    I thin you need to perform server side paging that will give you optimized result, instead of getting so many record if you get n number of records that will optimize performance however it need some efforts

    Have a look at https://www.reflectionit.nl/blog/2017/paging-in-asp-net-core-mvc-and-entityframework-core

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 14, 2019 9:39 AM

All replies

  • User-1780421697 posted

    If you are performing only read operation then you need to use 

    .AsNoTracking()

    That will improve the performance, On database level you can perform some indexing to improve performance, query optimization play important role.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 12, 2019 5:33 AM
  • User541108374 posted

    Hi,

    indeed, AsNoTracking has a good effect on performance (http://www.krisvandermast.com/post/2018/02/03/Use-AsNoTracking-to-speed-up-Entity-Framework-performance.html). 

    I suggest you also put breakpoints in your code to see where things go slow. If you test the same query or call to the view in SQL Management Studio, does it take the same amount of time (~ 40 seconds)? 

    Kris.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 12, 2019 8:09 AM
  • User2041008840 posted

    its not work well.
    I added .AsNoTracking() but it still take time to show all data

    i didnt create sql view.
    I running same query in both asp.net core and asp.net classic (aspx) 
    but this query is running fast in aspx but not in asp.net core. 

    i dont know what is the exact problem and to solve it.

    Tuesday, November 12, 2019 8:34 AM
  • User-1780421697 posted

    Are you using the Entity Framework in ASP.Net Web Forms as well? Do you have same type of connection string in both Applications?
    By the way if you are running stored procedure in ASP.Net Web Forms then just for testing purpose you can run SP in Entity Framework as well.

    You can also use the SQL Server Profiler to inspect the queries that are executing under the hood .

    Tuesday, November 12, 2019 10:32 AM
  • User1120430333 posted

    If using EF, the data is retrieved using T-SQL too,  like the classic ASP.NET example, but EF martializes each record  into an object.  A select  * from Jobplan  is doing what? All it's doing is selecting the records. It is not taking each record, creating a Jobplan object and loading each object into a List<T> that the EF statment is doing _context.Jobplan.ToList(). So of course,  EF is going to take longer to materialize 8,000+ objects from 8,000+ records read loading them into a List<T>.

    Maybe, you are being too greedy with columns,  and you don't need all the colums, which could affect overall query speed in object materialization.

    Other things to consider.

    https://www.thereformedprogrammer.net/entity-framework-core-performance-tuning-a-worked-example/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 13, 2019 1:03 AM
  • User2041008840 posted

    due to this list<t> is takes time to load all data.
    After that I tried with .AsQuerytable() 
    it loads faster and more better than ToList()

    and set Cast IQuerytable It works very well 

    Thursday, November 14, 2019 8:13 AM
  • User753101303 posted

    Hi,

    Are you sure this is the db? Showing that many rows often causes most of the time being spent in downloading/rendering the resulting HTML page. Make sure to time each step to see where exactly this time is spent.

    In a web app you usually use paging and/or filtering to avoid showing 8000 rows. How the user will then find those he need ? Could it be made easier for them ?

    Thursday, November 14, 2019 8:34 AM
  • User2041008840 posted

    I am using paging and filter also 
    I am using client side paging using Bootstrap-Tables file

    Thursday, November 14, 2019 9:00 AM
  • User-1780421697 posted

    I thin you need to perform server side paging that will give you optimized result, instead of getting so many record if you get n number of records that will optimize performance however it need some efforts

    Have a look at https://www.reflectionit.nl/blog/2017/paging-in-asp-net-core-mvc-and-entityframework-core

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 14, 2019 9:39 AM
  • User2041008840 posted

    Yes I know Reflection paging 
    Thank You.

    Thursday, November 14, 2019 9:56 AM