Click here to Skip to main content
13,042,421 members (115,911 online)
Rate this:
Please Sign up or sign in to vote.
See more: , +
Hello everyone,

I need some kind of caching mechanism in sql or in 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 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...

Krunal Panchal
Posted 5-Sep-12 2:42am
Updated 5-Sep-12 2:44am
Ankur\m/ 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
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[^], 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.
krunal111 6-Sep-12 1:55am
Hi Dylan,

Thanks a lot .. but what if i have inserted 10 more records.. it would again call the stored procedure and will fetch all 5010 records.. this will consume my memory..

Moreover this 5000 records are only for saying it would be many more than this....

Please help
Dylan Morley 6-Sep-12 3:51am

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

So, if you inserted new records, they would be retrieved when you paged through the UI
Ankur\m/ 23-Nov-12 2:09am
Absolutely, paging is the way yo go here.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You will love this feature: SqlCacheDependency.[^]

Using SqlDependency for data change events[^]

Hope it helps
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

You can use SQL paginiation techniq.
PLease check below URL :[^]

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 5 Sep 2012
Copyright © CodeProject, 1999-2017
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