recordset problem
-
Hi, I have an application in which I display records in a grid. I populate the grid using a recordset that contains the results of a query of the database. The problem is that if I have a large database (about 80,000 records and above..) then it takes a few minutes to load the grid with all of the records. I need to make this faster... Any ideas?
-
Hi, I have an application in which I display records in a grid. I populate the grid using a recordset that contains the results of a query of the database. The problem is that if I have a large database (about 80,000 records and above..) then it takes a few minutes to load the grid with all of the records. I need to make this faster... Any ideas?
don't load all the data at once - only load the data your grid is currently showing. Cleek | Image Toolkits | Thumbnail maker
-
don't load all the data at once - only load the data your grid is currently showing. Cleek | Image Toolkits | Thumbnail maker
Yeah - what I thought to do is show the first 400 records (for example), while searching for the other records. I thought to load the data page by page, and display 400 records in each page. So, it would work this way: display 400 records, then display another 400 (800 total), then another 400 (1200 total) and so on until I reach 78000 records. But how can I do this with the recordset object? Do I have to fill the recordset first with 400 records, then with 800 records, then with 1200 records, and eventually with 78000?
-
Yeah - what I thought to do is show the first 400 records (for example), while searching for the other records. I thought to load the data page by page, and display 400 records in each page. So, it would work this way: display 400 records, then display another 400 (800 total), then another 400 (1200 total) and so on until I reach 78000 records. But how can I do this with the recordset object? Do I have to fill the recordset first with 400 records, then with 800 records, then with 1200 records, and eventually with 78000?
SWDevil wrote:
So, it would work this way: display 400 records, then display another 400 (800 total), then another 400 (1200 total) and so on until I reach 78000 records.
only load the ones you need to display. if they scroll, load enough new data to complete the grid, and discard the rest. ideally, if your grid control allows, you can put it in charge of loading the data for the rows it has to display when it needs them. don't make it load data it doesn't need - just let it ask for rows X through Y when it needs to display them, and don't worry about any other rows. Cleek | Image Toolkits | Thumbnail maker
-
SWDevil wrote:
So, it would work this way: display 400 records, then display another 400 (800 total), then another 400 (1200 total) and so on until I reach 78000 records.
only load the ones you need to display. if they scroll, load enough new data to complete the grid, and discard the rest. ideally, if your grid control allows, you can put it in charge of loading the data for the rows it has to display when it needs them. don't make it load data it doesn't need - just let it ask for rows X through Y when it needs to display them, and don't worry about any other rows. Cleek | Image Toolkits | Thumbnail maker
ok - I understand your idea, but I don't understand how to actually implement it... First of all - how can I know if I can put my grid control in charge of loading the data it needs? Second - Let's say my grid control can't be put in charge of loading the correct data, how do I implement your idea? Do I have to perform a new query each time? For example: "SELECT * FROM TABLE1 WHERE field BETWEEN x and y" and then display the recordset that contains the result set? Also - won't this solution take a lot of time also? isn't the solution of loading page by page better?
-
ok - I understand your idea, but I don't understand how to actually implement it... First of all - how can I know if I can put my grid control in charge of loading the data it needs? Second - Let's say my grid control can't be put in charge of loading the correct data, how do I implement your idea? Do I have to perform a new query each time? For example: "SELECT * FROM TABLE1 WHERE field BETWEEN x and y" and then display the recordset that contains the result set? Also - won't this solution take a lot of time also? isn't the solution of loading page by page better?
SWDevil wrote:
how can I know if I can put my grid control in charge of loading the data it needs?
i don't know what you're using for your grid, but some of the standard Windows controls allow this. for example, the CListCtrl can act as a "Virtual list control", which means it will send you a message when it needs data for an item. the MSDN has info on this.
SWDevil wrote:
Do I have to perform a new query each time?
ideally, you would cache some data.
SWDevil wrote:
Also - won't this solution take a lot of time also? isn't the solution of loading page by page better?
you're only ever loading, at most, the number of lines you can display at once, so the control will load faster and won't be required to maintain a large amount of memory to hold all the items. Cleek | Image Toolkits | Thumbnail maker