Optimizing DB access.
Posted on 2004-10-13
I created an application that has to read and update records in a database (.mdb)
On this moment it take just over 1 minute to process 7100 records. I don't know if this is normal, profesionaly I work with an AS/400 and be able to write 1.000.000 records in less that 5 seconds, so I think this is extreem slow.
Now I hope I can blame my program (I'm not very experienced yet in VB).
Can sombody look to the code to look if it can be done more efficient?
I have an input file of +/- 7100 records (I'm sure they are all available in the DB)
Open "MyFile" For Input As #1
Do Until EOF(1)
Line Input #1, L$
x = GetDbData("Where MP3Name = " & L$)
If oRs.RecordCount > 0 Then
oRs("MP3Select") = True
---- The GetDbData -----
Public Function GetData(MyKey As String)
If oRs.State Then oRs.Close 'close privious request
oRs.Open "select * from MP3TrackInfo " & MyKey, oConn, _
adOpenKeyset, adLockOptimistic, adCmdText
GetData = Not oRs.EOF 'Give back false if record is not found
Maybe I can replace the open and close for an other search option, but how?