large rowsets

[mark1110]

I am rewriting a VB6 application that is using ADO to VB.NET using ADO.NET. What I would like to do seems simple enough, I have a form with several textboxes, comboboxes, and checkboxes. I would like to open a Access database, read its contents, then populate the form. I also need to go to the next record, go to the previous record etc. Here is part of the code I wrote:


private frmClient_Load(object sender, EventArgs e)
{

OleDbDataAdapter daClient;
DataTable dtClient;
string strClientSQL;



string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + Utilities.stdDir;
strClientSQL = "SELECT * FROM Client ORDER BY casename";
daClient = new OleDbDataAdapter(strClientSQL,Utilities.connectionString);
dtClient = new DataTable("ClientInfo");
daClient.Fill(dtClient);


This works, however since I have over 500,000 records it takes 10 minutes to load. I am not sure if this is correct way to do this. Is there a better way?

Thanks,

Mark

 

[lingsn]

I think you are not using VB.NET, you use the C# coding. In general view, from what you have describe, I think you should load 1 record at a time because you only display 1 record in the form. I think you have to sit down and rethink on the design.

2nd, make sure your database table is indexed so that record retrieval will be faster.

 

[cjard]

I am rewriting a VB6 application that is using ADO to VB.NET using ADO.NET. What I would like to do seems simple enough, I have a form with several textboxes, comboboxes, and checkboxes. I would like to open a Access database, read its contents, then populate the form.

This works, however since I have over 500,000 records it takes 10 minutes to load. I am not sure if this is correct way to do this. Is there a better way?

You load half a million rows out of a database, into memory (which probably exceeds the size of the page file) and note that its slow - I think 10 minutes to do that op is pretty darn good actually, especially since it's sorted too!
Few questions: What would you do with all that data? Put it in a grid so the user can scroll all half million rows? What if they want to get to the middle record? Would you have them click the "Next" button 249,999 times?

I'd uninstall a program that worked like that just as soon as it finished the first 10 minute loading option. Fortunately, there is a great set of articles by microsoft that will be very interesting for you. Have a read of the DW2 link in my signature..

 

[cjard]

2nd, make sure your database table is indexed so that record retrieval will be faster.

A select without a where clause will never use an index..