Image file table update, code takes over 24 hours to finish....suggestions

M

MM

I have a routine that runs through a folder of image files (25,000
images) and updates a table (45,000 records) with the image file name
when it finds a match. It works fine but takes over 24 hours, any
suggestions on how to speed it up would be appreciated.

MM

Option Compare Database

Private Sub Command0_Click()
''
'' GET FILE NAMES FROM GRAPHICS FOLDER
''
Dim sFileDir As String
Dim rsFileInfo As ADODB.Recordset
Set rsFileInfo = New ADODB.Recordset

' create the filename field -- this is a string data type, length
255
rsFileInfo.Fields.Append "FileName", adBSTR, 255

' open the recordset
rsFileInfo.Open

' get the files in the correct directory
sFileDir = Dir("i:\thumbs\*.jpg") ' change the path as
necessary

Do While sFileDir <> ""
If sFileDir <> "." And sFileDir <> ".." Then
rsFileInfo.AddNew
' rsFileInfo!FileName = sFileDir
' alternatively, use the next line instead to get the
file name
' minus the last four characters ".jpg"
rsFileInfo!FileName = Left(sFileDir, Len(sFileDir) -
4)
rsFileInfo.Update
Debug.Print rsFileInfo!FileName
' get the next file in the directory
sFileDir = Dir
End If
Loop
rsFileInfo.MoveFirst

''
'' COMPARE FILES TO RECORDS IN INVENTORY TABLE USING ITEMMAP
''
Dim rsInv As ADODB.Recordset
Set rsInv = New ADODB.Recordset
rsInv.ActiveConnection = CurrentProject.Connection
rsInv.Open "SELECT ItemMap.InItem, Inventory.ProdCode,
ItemMap.InItem AS ChkCode, Inventory.PathToImagesFolder FROM ItemMap
INNER JOIN Inventory ON ItemMap.OutItem = Inventory.ProdCode;", ,
adOpenKeyset, adLockOptimistic

' loop thru recordset to find matches in rsFileInfo
Do Until rsInv.EOF
Do Until rsFileInfo.EOF
If rsInv!ChkCode = rsFileInfo!FileName Then
rsInv!PathToImagesFolder = rsFileInfo!FileName &
".jpg"
End If
rsFileInfo.MoveNext
Loop
rsFileInfo.MoveFirst
rsInv.MoveNext
Loop
rsFileInfo.Close
rsInv.Close
Set rsFileInfo = Nothing
Set rsInv = Nothing

End

End Sub
 
D

Damon Heron

An Exit Do might speed it up - don't know if that will affect your purpose,
though.


Do Until rsFileInfo.EOF
If rsInv!ChkCode = rsFileInfo!FileName Then
rsInv!PathToImagesFolder = rsFileInfo!FileName & ".jpg"
Exit Do
End If
rsFileInfo.MoveNext
Loop

Damon
 
B

Bob Quintal

m:
I have a routine that runs through a folder of image files (25,000
images) and updates a table (45,000 records) with the image file
name when it finds a match. It works fine but takes over 24 hours,
any suggestions on how to speed it up would be appreciated.

MM
Click to expand...

Your file comparison routine currently loops the two tables through
the 45,000 records 25,000 times.
Replace your
Do Until rsInv.EOF
Do Until rsFileInfo.EOF
If rsInv!ChkCode = rsFileInfo!FileName Then
rsInv!PathToImagesFolder = rsFileInfo!FileName & ".jpg"
End If
rsFileInfo.MoveNext
Loop
rsFileInfo.MoveFirst
rsInv.MoveNext
Loop

with

With an update query based on the inner join of the three tables.on
..ChkCode = .FileName


Q
 
T

Tony Toews [MVP]

And make sure there's an index on rsInv!ChkCode and
rsFileInfo!FileName fields.

Tony
 
M

MM

An Exit Do might speed it up - don't know if that will affect your purpose,
though.

Do Until rsFileInfo.EOF
            If rsInv!ChkCode = rsFileInfo!FileName Then
                rsInv!PathToImagesFolder = rsFileInfo!FileName & ".jpg"
                Exit Do
            End If
            rsFileInfo.MoveNext
        Loop

Damon













- Show quoted text -
Click to expand...

Thanks for your post, I'm not very good with code, what does "EXIT DO"
do?
 
M

MM

Your file comparison routine currently loops the two tables through
the 45,000 records 25,000 times.
Replace your
Do Until rsInv.EOF
   Do Until rsFileInfo.EOF
      If rsInv!ChkCode = rsFileInfo!FileName Then
         rsInv!PathToImagesFolder = rsFileInfo!FileName & ".jpg"
      End If
      rsFileInfo.MoveNext
   Loop
   rsFileInfo.MoveFirst
   rsInv.MoveNext
Loop

with

With an update query based on the inner join of the three tables.on  
.ChkCode = .FileName

Q















--
Bob Quintal

PA is y I've altered my email address.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for your post. I'm not too stong with code but I think I
understand the idea find a file and update it then those are
eliminated narrowing the search. The code I have took me some trial
and error (and NG help) to figue out and make work. Would you be
willing to give me an idea of what the replacement might look like?
I'll give it a shot tomorrow when I get into the office.

MM
 
T

Tony Toews [MVP]

MM said:
Is there something different in the code needed to index rsInv!ChkCode
and rsFileInfo!FileName?
Click to expand...

No, you need to go into the table definition and click on the field.
There's a property below. Set that to Indexed.

Tony
 
M

Mike Painter

MM said:
I have a routine that runs through a folder of image files (25,000
images) and updates a table (45,000 records) with the image file name
when it finds a match. It works fine but takes over 24 hours, any
suggestions on how to speed it up would be appreciated.
Click to expand...
If the other suggestions fail you might try directing the contents of the
directory to a txt file and attaching it, then running an update query.
Dir i:\thumbs\*.jpg > SomeFile.txt.

If "thumbs" implies a thumb drive over USB 2.0 moving the file to a drive
might be worth doing.
 
D

Damon Heron

If the condition in the If statement is true (a match is found) then the
loop is exited to the outer loop.
Without the exit do, the loop will continue thru all the file names, even
though a match has already been found.

Damon

Thanks for your post, I'm not very good with code, what does "EXIT DO"
do?
 
M

MM

 If the other suggestions fail you might try directing the contents of the
directory to a txt file and attaching it, then running an update query.
Dir i:\thumbs\*.jpg > SomeFile.txt.

If "thumbs" implies a thumb drive over USB 2.0 moving the file to a drive
might be worth doing.
Click to expand...

i: is a local drive on the sql server... I'll try the text export of
file names to an update.
 
M

MM

 If the other suggestions fail you might try directing the contents of the
directory to a txt file and attaching it, then running an update query.
Dir i:\thumbs\*.jpg > SomeFile.txt.

If "thumbs" implies a thumb drive over USB 2.0 moving the file to a drive
might be worth doing.
Click to expand...

How simple is that, 5 minutes!!! I still need to work on (and learn)
to code properly so I'll still try the other notes. Thanks to all.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mange image files with code 1
delete images from disk where filename is not in table 1
copy and rename image files based on query result 1

Top