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?


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.

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline


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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 …
Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now