how to retrive data faster ?

Subin Thomas

7y1.8k.NET

there is application which is build in asp.net contains huge data while doing cruid operation it takes a lot of time to fetch data is there any technique that can be applied so retrive the data faster

thanks,

subin

7 Replies

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

Salman Beg7y ago
Accepted answer
Hi.
Are you retrieving the whole data at a time?
For example:- If you want to retrieve 1Million of records at a time then it will take lot of time and some time it causes timeout issue.
So to retrieve the data from sql server in a faster way, you can use fetch and offset and in this case the performance will be faster.
By using fetch and offset, you can retrieve million or billions of data in a faster way.
so in that case you need to give pagesize and pagenum and by using which you can retrieve the data in a faster way.
So as you are using Stored procedure so for now you need to change that stored procedure based on fetch and offset.
To implement that you can follow this link and in these links also example exists, so based on that you can modify your procedure.
https://www.c-sharpcorner.com/UploadFile/f82e9a/offset-fetch-clause-in-sql-server/
https://www.c-sharpcorner.com/UploadFile/rohatash/offset-and-fetch-next-keywords-in-sql-server-2012/
After that you need to change your asp coding also and please follow this link which contains an gridview and it is retrieveing the data from database using fetch and offset.
https://www.codeproject.com/Articles/1078662/How-to-implement-Paging-in-ASP-NET-at-SQL-Query-Le
If your Sql Query is complex then you need to refactor it and please check the clustered index and joins properly.
Thanks.
Salman Beg7y ago
Yup. You're Welcome.
Madan Shekar7y ago
salman Beg

Thanks for sharing usefull resource
Subin Thomas7y ago
Salman Beg thanks you so much for the explanation with the links.
Madan Shekar7y ago
first find out query taking to much of time to exce or what , in this case sit for writing query in optimized way and then make it as stored procedure
Subin Thomas7y ago
its a already build application which runs through Sql Query.
Madan Shekar7y ago
now are using LINQ query (entity framework) or what please clear me this question

while doing sql operation such as INSERT/DELETE/UPDATE do through stored procedure only

In you application (asp.net) write proper and optimized code

In database when dealing with huge data , primary key instead of varchar use autoincrement or identity column with int or bigint

thanks
if it helps please accept the ans