how to retrive data faster ?

Question

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

Salman Beg · 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 Beg · Answer

Yup. You're Welcome.

Madan Shekar · Answer

salman Beg Thanks for sharing usefull resource

Subin Thomas · Answer

Salman Beg thanks you so much for the explanation with the links.

Madan Shekar · Answer

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 Thomas · Answer

its a already build application which runs through Sql Query.

Madan Shekar · Answer

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