Solved

Optimizing DB access.

Posted on 2004-10-13
5
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…

688 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