sorry, I had originally posted this in VB.NET but meant to put it in Database Dev.

Ok, so say I have this database that has lots of records, anywhere from 10,000 to 100,000,000. I need to take all of the records.. compare each one to something else, and UPDATE a field value based off of my calculation. I need to know the best way to do this performance wise when dealing with this many records.

I'm using MySQL 4 and ADO.NET

The way I would normally do this is using the DataSet since it has the abillity to automatically update. but what the dataset does is it stores all of the data in memory which makes it impossible to use.

I have switched over to use the OdbcDataReader. While using the OdbcDataReader I have to have two connections, one doing the foward reading, and another connection to call the commands. Because you can not use the same Connection to make run a command if it's in Reader Mode.

I'm running this with 640,000 records (for time checking)

Code:
//cn and cn2 already referenced.

OdbcDataReader MyReader = null;
OdbcCommand comm = null;
OdbcCommand comm2 = null;
int r=1;
comm = new OdbcCommand("SELECT ID, NDC, Generic_Brand, Date_Filled, Transaction_ID, Real_GPI, Real_AWP_UnitPrice, Real_GenericCode, Real_GenericName, IsMac, Real_Date_Filled, Real_MacPrice, Real_Mail_Retail, Pharmacy_Number FROM Body ORDER BY NDC",cn);
comm2 = new OdbcCommand("",cn2);
//run SQL.
MyReader = comm.ExecuteReader();
Console.WriteLine("#" + myID + " :: " + StartTime.Subtract(DateTime.Now) + "  Executed!");   
//THIS TAKES 2-4min to ExecuteReader()

while (MyReader.Read())
{
             //DO THE PROCESSING IN HERE
             //This is just a temp UPDATE for speed tests.
	comm2.CommandText = "UPDATE Body SET IsMac = \"Z\" WHERE ID = " + r;
	comm2.ExecuteNonQuery();
	r++;
}
comm2 = null;
cn2.Close();
MyReader.Close();
comm = null;
MyReader = null;
cn.Close();

Console.WriteLine("#" + myID + " :: " +  StartTime.Subtract(DateTime.Now) + "  Finished Reading"); 
// Finally finishes at 7-8min.
Thread.Sleep(500);
I'm not sure if this is the best way to read the data, (use it) and then make updates to the database.

when I say I use it, what I must do is: Take the data from each record in the MySQL and compare it to data in a MS Access Database, based off a GroupID. Compare some fields in there and then pull a record from the MS Access Database based off of those compare fields (different dates), and then put that new data from the Access database into the MySQL DB, also other various calculations.

-SpeedyDog