?
Solved

Reduce time for DataAdapter to fill rows

Posted on 2004-10-10
11
Medium Priority
?
415 Views
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.
0
Comment
Question by:vbhaha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +5
11 Comments
 
LVL 27

Expert Comment

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

Expert Comment

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

Expert Comment

by:Hans Langer
ID: 12273914
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
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!

 

Author Comment

by:vbhaha
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 ?



0
 
LVL 34

Expert Comment

by:flavo
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.

Dave
0
 
LVL 8

Expert Comment

by:razo
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
0
 
LVL 19

Accepted Solution

by:
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)
            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
 
LVL 9

Expert Comment

by:checoo
ID: 12274275
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
 

Author Comment

by:vbhaha
ID: 12274293
arif_eqbal, it sounds good, could you give me more details or examples about it ?
0
 
LVL 6

Assisted Solution

by:etmendz
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.
0
 
LVL 10

Expert Comment

by:Hans Langer
ID: 12277920
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!

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

719 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