Hi,
I'm trying to switch our grids to virtual mode with datasets that can go to over a million rows. i have a basic implementation done and working, but I'm concerned about the number of database round trips. However, it seems that every time a row is shown for the first time, I'm making a database call. So, when the first page of data is shown, it's about 30 rows and therefore 30 db calls. I page done, another 30 calls. What is the best strategy that you would recommend to minimize database calls? Any way you could recommend to make a single db call with 30 IDs, instead?
Another scenario is exporting rows from the grid to Excel. I imagine with data source in virtual mode, this could cause up to a million separate queries. Any way that you would recommend for avoiding this?
Thanks!
BG
After some thought, that's what I did. However, to avoid fetching records multiple times I had to write code to check keys against a local cache and it got quite cumbersome, especially with re-sorting, etc. I was simply curious if anything in the Infragistics framework anticipated this. You had answered my question. Thanks!
Hi BG,
The WinGrid is designed to load data on-demand from it's data source (UltraDataSource, usually). It doesn't get involved in the back end.
As far as I am aware, there isn't any really efficient method for retrieving data from the back end in an on-demand way in DotNet.
One thing you could do is - you could fetch more rows than you need. For example, then the UltraDataSource's CellDataRequested event fires, you are probably going out the database and retrieving only the row you need.
But chances are pretty good that if a row is scrolled into view in the grid that the row below it will also be needed. And the same goes for the row above it. So you could retrieve the data in a chunk of any size you like. You could retrieve the row you need and 1 row on either side of it, or 10 rows on either side of it, or 100 rows on either side of it.