Reduce time for DataAdapter to fill rows

Posted on 2004-10-10
Last Modified: 2012-08-14
I have a table with few hundred thousand rows. It needs a minute to fill(DataAdapter.fill(datatable)) all rows from the server for records browsing. Unfortunately it is necessary to allow user to browse all records!!!

How can I speedup the time for fill rows and allow user to browse all records.

Thanks in advance.
Question by:vbhaha
  • 2
  • 2
  • 2
  • +5
LVL 27

Expert Comment

ID: 12273874
Can you please post the Adapter's Select Command?
LVL 19

Expert Comment

ID: 12273909
you can go for multiThreading so that the user simulteneously Browses as Records get filled???
LVL 10

Expert Comment

by:Hans Langer
ID: 12273914
1 Way :
Optimize your Query to DB.

2 Way:  
use SqlDataReader in place DataSet, SqlDataReader is a least 10 time faster than DataSet.
Create the Query only with the filters that each search need.

What DB are you using?

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 12274152
1. There is nothing can be changed in the "Select" statement.  Just like that "Select * from table"

2. It should allow user to update, so cannot use datareader.

3. arif_eqbal, would you give me more details ?

LVL 34

Expert Comment

ID: 12274194
Cant you filter the records a little, ie

Select * from table where something = 'something';

Surely the user has an idea of what they are after, and surely they arent going to look at all the records.


Expert Comment

ID: 12274233
why dont u use a caching mechanism where u keep using the dataset in the cache unless a user updates the dataset then u reapply the query
LVL 19

Accepted Solution

arif_eqbal earned 400 total points
ID: 12274239
Well what I suggest is on Form Load you can create a New thread. this Thread will load the data and parallely the form will get loaded so by the time the form renders itself say one fourth of the data is loaded and while the user browses through the already loaded data the rset will keep loading.

in case you need some help on creating Threads here's a small eg.

create a class and declare

Private loadThread As System.Threading.Thread

then say on a Function LoadData in this Class write

            loadThread = New System.Threading.Thread(AddressOf StartLoading)

Then in the Function StartLoading write the code to Select data from database and fill wherever you want

Now from the main Form on FormLoad create an instance of this class and call the loaddata function. Sometimes the Form rendering gets held up for a few seconds you can add one or two DoEvents before creating the thread and in the function StartLoading where actually you are fetching the data.


Expert Comment

ID: 12274275
you can populate the datagrid in a background thread, take a look at the following article;en-us;318604

Author Comment

ID: 12274293
arif_eqbal, it sounds good, could you give me more details or examples about it ?

Assisted Solution

etmendz earned 100 total points
ID: 12274883
No one ever said that the DataSet is the answer to everything you need. In fact, for large amounts of data, you might want to consider NOT using DataSet.

1. The suggestion to use the SqlDataReader to load data to your DataGrid is good. In fact, it would be best to consider pagination which should prevent you from loading too many at once. With pagination, you can still allow all records to be browsed without necessarily making the user wait for everything to load. A user can only handle so little at any given time anyways.

2. The suggestion to use a data cache technique is also good. Combined with pagination, you can save your user a lot of time when navigating back and forth through the pages of the query result. Beware of course of limitations on the target machine's memory capacity.

3. The suggestion to add filtering to your query is also good. Add filtering features to your form so that the size of the list may be reduced. It is correct to say that the user can only target to work on some records and, likely, not everything at once.

4. The suggestion to use threading to load data in the background is also good. However, this adds some level of complexity especially when you need to make sure that the user does not attempt to restart loading the data at some point and, therefore, invalidate the early efforts of a thread. You can, however, use this technique to fill your data cache for #2 above during "idle" times. Again, beware of limitations on the target machine's memory capacity.

You're worried about updates? Do it one record at a time. Connection pooling is inherent to ADO.Net so it's no big deal. The point is DataSet is not everything. It works for small sets of data. But not when it gets bigger...

Have fun.
LVL 10

Expert Comment

by:Hans Langer
ID: 12277920
Nice summary ! :)

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question