Solved

Optimizing DB access.

Posted on 2004-10-13
5
200 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

How our DevOps Team Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

825 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