Fastest way to fetch huge amount of data from SQL Server database

Question

I have more than 50,000 records in the database which I have to deal with in my application (and the number is increasing by 2000/day as a minimum).

The way I am currently using is: Linq to entities with a stored procedure in the database which is returning a collection of an object so that I can deal with it.

But I noticed that it is a slow, what is the most efficient way to get such a number of records from the database?

Sample of my code:

   items = (from f in db.spItems().OrderByDescending(f => f.date)
                  group f by f.ID into g
                  select g.FirstOrDefault()).Take(9).ToList();
                  join ar in db.anothertable on f.ID equals ar.ID
                  join t in db.thirdtable on ar.tid equals t.ID

spItems is a stored procedure which gets the results but sometimes i have to do multiple joins on it

Solution 1

I would first try to optimze your query. Is it slow in SSMS? Do you use proper indices? Do you need all columns. Most important: do you need all 50000 rows to be displayed?

50000 records are not many records, but it's unusual to show all in a web application since that means you have to generate the HTML for all records and display it in the client's browser(maybe even using ViewState). So i would suggest to use database paging(f.e. via ROW_NUMBER function) to partition your resultset and query only the data you want to show(f.e. 100 rows per page in a GridView).

Efficiently Paging Through Large Amounts of Data

Solution 2

Please consider following steps. For this kind of issue we need to work at more than one layer.

1.Use Standard way using ADO.net to fetch result. Because LINQ to SQL is ultimately an ORM (additional layer) so obviously it will lower the performance which is noticeable in just like you case. see here

2. Please review again your table design if you have not missed any thing like adding index,selected wrong datatype etc.

3. Improve Stored Procedure performance : see here

4. Divide Your Data.: Moved your old data other table if it is read only(or no longer going to be used) as you are telling that minimum new 2000 rows is added daily. So your table size will be decreasing significantly. Please decide this decision very carefully.