Large Table data fetch issue

Gagan Sharma

8y954

Hi All,

I have a table with more than 45 lakh entries in it.

The table has only 2 columns, that are properly indexed(cluster and nonclustered), Fragmentation on the indexes are only around 5%. I have rebuilded/reorganised the index already.

When i fetch all records from the table it take more than 51 seconds.

Even in the SP's it hurts the performence so badly, as it is joined with other tables

How do i solve this issue?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manav Pandya8y ago
hello

For that instead using sql query i recommend LINQ query

because it is much faster than sql

Go for it

Thanks
Gagan Sharma8y ago
Hi @Manav and @Amit, my requirement is to make the table fast for joins as well as fetching of records.
Manav Pandya8y ago
Hello

As per your situation about lakhs of record , i know its difficult to maintain while displaying

Thus you can do for that like :

Default paging :

In which you can specify how many record you want through grid index

Custom paging :

retrive those number of record that you want to display per page , either linq or using SP

For reference do follow the link :

https://docs.microsoft.com/en-us/aspnet/web-forms/overview/data-access/paging-and-sorting/efficiently-paging-through-large-amounts-of-data-cs

Thanks
Amit Gupta8y ago
To handle large data, you can use pagination functionality