The Sql table consists of about 20 fields from a VFP table, and an additional 10 fields, whcih are in effect lookup fiuelds to give descriptions from a series of VFP lookup tables.If I strip out the 'Lookup Code' bit, the update takes about 5 minutes, so I am fairly sure the bottleneck is in the way I am updating the descriptions, and womder if there is a quicker way to do this update.

I have the following code replicated 10 times for each of my lookup fields, within my main loop to update the main data

1. I set up an OleDbCommand with the connection string and a parameter for the lookup table

2. I then have a While Rdr.read loop to update a field with the description

Is there a quicker/better way to update these descripotions ?

(By the way, what is SSIS ?)

I have done a bit of experimenting and got the update,( inclusive of descriptions) down to about 10 mins for 200,000 records.

I had a look at Arrays, ArrayList but neither seemd to fit the bill. Then I came across  a thing called Dictionary, which allows you to store in a type of array,  Key and Value pairs, and then to do a lookup on these with one command. This seems to have worked extremely well in the limited testing I have done so far.

So what I have done is as follows:

1. Initailly, store all my Keys and Descriptions into  Dictionaries . This is an initial hit but only takes seconds as I am just storing 2 fields for each lookup

2. When populating my main Table, in order to get the descriptions, I just do a lookup on the in Memory dictionary. This means I do not have any overhead of going to disk  out and doing any selects etc...all thats needed is a lookup which seems to be very fast