Solved

Optimizing DB access.

Posted on 2004-10-13
5
179 Views
Last Modified: 2013-12-25
Hi Experts,

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
            oRs.Update
        End If
       
    Loop

Close

---- 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
   
End Function

Maybe I can replace the open and close for an other search option, but how?

Thanks

Murph
0
Comment
Question by:theo kouwenhoven
  • 3
5 Comments
 
LVL 7

Accepted Solution

by:
HoweverComma earned 50 total points
ID: 12304183
Line input in VB is painfully slow. I converted a program to Delphi that I had 300 seconds decreased to 19 seconds.

Glad to see you have gotten the database working now.

use adOpenDyanmic instead of adOpenKeySet

Call This only one time on FormActivate (or where you want just dont call it repeatedly)
 oRs.Open "select * from MP3TrackInfo " & MyKey, oConn, _
        adOpenDynamic, adLockOptimistic, adCmdText


'These are not necessary in all cases but will populate the recordset which may speed up the LU's.
'If you do not use them sometimes you will get a Flase positive for BOF

oRs.MoveLast
oRs.MoveFirst

 
and use this to test for a match

oRs.Seek "=", Val(strSeek)
If oRs.Nomatch (will be true if no match was found)
Also make sure to index the mp3trackinfo field

Will check back periodically tonight,  I am working on a project and come back and forth when I hit dry spells.
0
 
LVL 5

Expert Comment

by:waelothman
ID: 12304195
you opend many connection if you gathering this information it will be more professional for example
count =0
Temp=""
Open "MyFile" For Input As #1
    Do Until EOF(1)
     count=count+1
    Line Input #1, L$
       if count < 100 then
            Temp=Temp+ "'" & L$ &",'"  
      else
              Temp= Temp+ "'" & L$ &"'"  
              oConn.execute  "Update MP3TrackInfo set MP3Select where MP3Name in (" & Temp & ")
             Temp = ""
            count =0
     end if
loop
if count <100 then
oConn.execute  "Update MP3TrackInfo set MP3Select where MP3Name in (" & Temp & "'')
end if

        End If
       
    Loop

Close
0
 
LVL 5

Expert Comment

by:waelothman
ID: 12304208
and also you can make some littel aprovment if you made
Open "MyFile" For Input As #1
    Do Until EOF(1)
    Line Input #1, L$
    oConn.execute  "Update MP3TrackInfo set MP3Select where MP3Name = '" & L$
               
    Loop

Close
0
 
LVL 16

Author Comment

by:theo kouwenhoven
ID: 12322709
Hi HoweverComma,

Thanks for your remark,

I have no idea what I can do with VB and Db's, was just messing around and saw that the combination adOpenKeySet, adLockOptimistic, adCmdText was working, stil no idea what it means, but removing one of them doesn't work.

The adOpenKeySet replaced by adOpenDynamic saves 20 second so down from 1:05 minutes to 0:48 seconds.
But still to slow.

You also remark "Line input in VB is painfully slow". I writing 7.5 prorg.Languages some beter then other, I don't like to learn a new one, so if you have some ideas within VB......



0
 
LVL 5

Expert Comment

by:waelothman
ID: 12323481
my code is not working?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now