?
Solved

Optimizing DB access.

Posted on 2004-10-13
5
Medium Priority
?
223 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 200 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

743 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