ADO Recordset Question

[briana02]

Hello all,

I have an app that is updating a VERY large table, > 700,000 records. The app reads updated records from a file, then writes them to the table. Well, the file itself containes 44,000 records and when I run the application, it is incredibly slow (I calculated that it would take 39.3 days for the program to run...lol). Here's my program logic: I read a record from the file, check to see if that record exists in the DB by opening a ado recordset with a SQL statement like the following:
"SELECT [Field1], [Field2] FROM [TABLE] WHERE [Field1] = '" & strPrimaryKey & "'"
where strPrimaryKey is a string containing the primary key read from the file. As you can probably imagine, returning the result set 44,000 times (for each record read from the file) from a table with over 700,000 records is VERY Slow. Anyone have any suggestions?

Thanks in advance
Brian

[katlaw]

You need to look at the index structure on your table. There should be an index that your query uses, so that it does not iterate through all the records in the table.

HTH!
katlaw

[phunkydude]

You may wanna check out the recent Postings on BULK INSERT. At least read about it ... could be of help.

[Z LoveLife]

Have you considered simply opening your entire 700,000 recordset and then do a transaction file/master file walk. Open the recordset and the file. Read the first record of each and compare them. If they're equal it exists do your update and read both. If transaction file key is lower then read transaction file. If recordset key is lower movenext. that way you save all the opens on your monster recordset. Just a thought.

[briana02]

Hey guys, I though I would share my final solution with you. I finally used an the Execute method of the ado connection object to execute a simple SQL update statement. I found, also, that the table WAS NOT indexed, making traversing a recordset a very hefty task indeed -- what a mess.
Well, here is the code. Thank you all for your suggestions as well. :)

strItem = inStream.ReadLine
While Not inStream.atEndOfStream
Debug.Print inStream.Line
If strItem <> "" Then
strBTN = Mid$(strItem, 2, 10)
strBillCycle = Mid$(strItem, 31, 1)
strSQL = "UPDATE dbo_Customer" _
& " SET Billing_Cycle = '" _
& strBillCycle & "'" _
& " WHERE BTN = '" & strBTN & "'"
adoConnection.Execute (strSQL)
If inStream.Line Mod 100 = 0 Then
DoEvents
End If
End If
strItem = inStream.ReadLine
Wend