Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server-2008 , +
Hello everyone,
 
I need some kind of caching mechanism in sql or in asp.net for storing data (records) retrieved from sql stored procedure..
 
Actual scenario :
 
I have one stored procedure in sql which fetches 7 tables and gives the result , the result obtained is too huge i.e. around 5000 records..
 
Now i display that records in asp.net gridview control which takes too long time to bind that record and display it on screen
 
Now when i insert some record suppose say 10 records, it will again execute that storedprocedure and fetch 5010 records and then will get bind to the gridview control
 
Please suggest something by which i could reduce this execution time which occurs in fetching record and displaying record in front end....
 
I need some way that i could store 5000 records and when 10 records are inserted , it fetches only 10 records and these 10 records are appended to those 5000 records...
 

Please suggest...
 
Regards,
Krunal Panchal
Posted 5-Sep-12 2:42am
Edited 5-Sep-12 2:44am
v2
Comments
Ankur\m/ at 23-Nov-12 2:07am
   
You call 5000 huge?! ;)
 
Paging is the way to go, as Dylan suggested! Also since you are making edits and if that is frequent, Caching isn't appropriate.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

What you really want to do is implement a decent paging solution. This will solve both your problems, you won't have slow binding times and you won't use lots of server memory by caching larges amounts of data.
 
Humans can't handle 5000 records at once, it's too much information. Opinions vary, but for grid data I think 25-50 rows per page is about right - for what the eye can read and comprehend. So, if you're only displaying this amount of records at any one time, you only need to fetch that amount from your database. As you click 'next page', your grid can make an ajax request and fetch the next set of data to display.
 
Here's a GridView example using Update Panels http://www.c-sharpcorner.com/uploadfile/prathore/gridview-paging-and-sorting-using-ajax/[^], but I strongly suggest you spend a bit of time searching - that's quite a basic example.
 
Using this technique, we have grids that happily display thousands of records.
  Permalink  
Comments
Dylan Morley at 6-Sep-12 3:51am
   
Hi,
 
The point is, you *dont* ever retrieve 5000 records. You only retrieve the records you want to display, 25-50 (or whatever your page size is) from the database. As you page through your UI, you retrieve the next set of records from the database
 
Have a look at the SQL technique to retrieve paged records
 
http://blog.sqlauthority.com/2007/06/11/sql-server-2005-t-sql-paging-query-technique-comparison-over-and-row_number-cte-vs-derived-table/
 
So, if you inserted new records, they would be retrieved when you paged through the UI
Ankur\m/ at 23-Nov-12 2:09am
   
Absolutely, paging is the way yo go here.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You will love this feature: SqlCacheDependency.
http://msdn.microsoft.com/en-us/library/9dz445ks.aspx[^]
 
Using SqlDependency for data change events[^]
 
Hope it helps
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

You can use SQL paginiation techniq.
PLease check below URL :
 
http://shrikantlandge.blogspot.com/2009/05/pagination-with-mssql-server-2005.html[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 861
1 Kishore Pogaru 265
2 OriginalGriff 240
3 Volynsky Alex 205
4 Richard Deeming 140
0 OriginalGriff 5,640
1 Sergey Alexandrovich Kryukov 4,693
2 CPallini 4,540
3 George Jonsson 3,132
4 Gihan Liyanage 2,445


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 5 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100