Go Premium for a chance to win a PS4. Enter to Win


Reduce time for DataAdapter to fill rows

Posted on 2004-10-10
Medium Priority
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?


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 800 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


Author Comment

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

Assisted Solution

etmendz earned 200 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

972 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