• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Reduce time for DataAdapter to fill rows

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.
0
vbhaha
Asked:
vbhaha
  • 2
  • 2
  • 2
  • +5
2 Solutions
 
DabasCommented:
Can you please post the Adapter's Select Command?
0
 
arif_eqbalCommented:
you can go for multiThreading so that the user simulteneously Browses as Records get filled???
0
 
Hans LangerCommented:
Hi,
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?

GL
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
vbhahaAuthor Commented:
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 ?



0
 
flavoCommented:
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.

Dave
0
 
razoCommented:
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
0
 
arif_eqbalCommented:
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)
            loadThread.Start()

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.

0
 
checooCommented:
you can populate the datagrid in a background thread, take a look at the following article

http://support.microsoft.com/default.aspx?scid=kb;en-us;318604
0
 
vbhahaAuthor Commented:
arif_eqbal, it sounds good, could you give me more details or examples about it ?
0
 
etmendzCommented:
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.
0
 
Hans LangerCommented:
Nice summary ! :)
GL
0

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!

  • 2
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now