Looking for Faster way to update Table - boolean field

[Milhouse4118]

I'm looking for the fastest way to update a table boolean field in an access table by way of VBA. A snipet of my code is below

For Each vFile In dictClipsDB.Keys
    If Not dictFilesFound.Exists(vFile) Then
        strSql = "UPDATE tblClips SET blnAlive = FALSE WHERE fldLocation = " & cstQuote & vFile & cstQuote
        dictClipsDead(vFile) = "DELETE READY" 'Dir(vFile) ' this should should be eliminated
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSql
        DoCmd.SetWarnings True
    End If
Next vFile

The above code "worked", but the table tblClips has more than 40,000 records. The dictionary "DictClipsDB" could have as many as 40,000 keys. The code executes VERY slowly and I strongly suspect that it is due to the thousands of executions of the SQL statement. Is there a better way to attempt this table update?

I then tried to modify the code as shown below:

For Each vFile In dictClipsDB.Keys
    If Not dictFilesFound.Exists(vFile) Then
       
        'Writing code to write a REALLY long SQL statement that will update the table for all Non-Found Files.
        x = x + 1
        'FIrst pass build the necessary SQL statement
        If x = 1 Then
            'strSql = "UPDATE [tblClips] SET [blnAlive] = FALSE WHERE fldLocation = " & cstQuote & vFile & cstQuote
            strSql = cstQuote & vFile & cstQuote
        Else
        'Continue adding the missing files to the strSQL Statement
            'strSql = strSql & " OR fldLocation = " & cstQuote & vFile & cstQuote
            strSql = strSql & ", " & cstQuote & vFile & cstQuote
        End If
       
 
    End If
Next vFile

If x > 0 Then
    strSql = "UPDATE [tblClips] SET [blnAlive] = FALSE WHERE [fldLocation] IN (" & strSql & ")"
End If

strMsg = MsgBox("The For Loop has identified " & x & " records as dead.  Proceed with execiuting SQL update of records?", vbYesNo, "TESTING")
If strMsg = vbNo Then Exit Sub

'Time to set all not found values in the database to "DEAD"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

I attempted to execute the above code and my pc simply locked up. I allowed it to run for more than hour and decided that I may have mis-coded something or it wasn't much of an improvement.

I finally decided to try to get expert advice and I registered on this forum.

I am not at all committed to running a SQL statement or structuring it as I show above. This is just some of my very inexperienced approach came up with. Please guide me to a more efficient way to set a boolean field based on a value in a dictionary object. The dictionary object will have a key value that is identical to the fldLocation value of the record....

Thanks for your help.

- Milhouse4118

 

[CJ_London]

Would help if you documented your code and explained why you are using a dictionary

Suspect the problem is looping your dictionary.

Plus usually faster to use currentdb.execute rather than docmd.runsql. Using this also means you don’t need set warnings and if you did, set and unset outside the loop

Other methods-

use an ado recordset rather than a dictionary. You can then apply a filter to reduce your 40000 to something less

Or create a function that references your dictionary and returns true or false

This can then be called in a simple update query either as part of the SET clause or as a criteria

 

[theDBguy]

Hi. Welcome to AWF!

I'm not clear on the problem either. Would it be possible to use a JOIN in your query?

 

[Milhouse4118]

Thanks for the reply.

Taking a step back, the core purpose of my database is to catalog different files on my computer. I start out by scanning specific directories and building a dictionary of all found files (I used to use a Collection for these found files, but the dictionary runs much faster).

There are probably much better ways to do what I'm doing....

I'll look into changing from docmd.runsql to currentdb.execute.

Thanks for the tip.

 

[CJ_London]

There are probably much better ways to do what I'm doing...

Probably -I’ve suggested two

Edit - and is your fldlocation field indexed ?

 

[jdraw]

Milhouse4118,

You may want to review this article and code by Crystal(strive4peace). The purpose of the utility is
Loop through all your Access databases in or under a path and copy what's in the system object table (MSysObjects) for each to the documentation database; and get file information like size. There is a video describing this utility at
https://www.youtube.com/watch?v=ePMIb3LuL6s&t=3s

 

[Milhouse4118]

Probably -I’ve suggested two

Edit - and is your fldlocation field indexed ?

Yes, fldLocation is indexed, No Duplicates

 

[Milhouse4118]

@jdraw Thanks for the video link. I'll be sure to check it out.

 

[The_Doc_Man]

Thanks for the reply.

Taking a step back, the core purpose of my database is to catalog different files on my computer. I start out by scanning specific directories and building a dictionary of all found files (I used to use a Collection for these found files, but the dictionary runs much faster).

There are probably much better ways to do what I'm doing....

I'll look into changing from docmd.runsql to currentdb.execute.

Thanks for the tip.

Specifically to try to speed this up...

Instead of loading files to a dictionary, load them to a table. Then do your UPDATE to a JOIN linking tblClips to this table of discovered files. Be sure that you have indexes on both tables before you do the JOIN. The reason I suggest this is the order of SQL step execution. The processing of the SELECT clause comes early, followed by FROM and JOIN clauses. WHERE clauses come much later. If you use the JOIN of that list as though it were a filter, your set of eligible records gets reduced more rapidly than it would be if waiting for the WHERE clause to kick in.

 

[MarkK]

You can nest your updates inside a transaction so they are cached locally until you commit, and then they are all transmitted to the table as a batch. Consider code like...

Sub SampleCode()
On Error GoTo handler
    Const SQ_UPDATE As String = _
        "UPDATE tblClips " & _
        "SET blnAlive = FALSE " & _
        "WHERE fldLocation = p0"
    
    DBEngine.BeginTrans
    
    With CurrentDb.CreateQueryDef("", SQL_UPDATE)
        For Each vFile In dictClipsDB.Keys
            If Not dictFilesFound.Exists(vFile) Then
                .Parameters(0) = vFile
                .Execute dbFailOnError
            End If
        Next
        .Close
    End With
    
    DBEngine.CommitTrans
    Exit Sub

handler:
    DBEngine.Rollback
    MsgBox Err & " " & Err.Description
End Sub

I am not sure this is the problem, but it is something you can try.

 

[arnelgp]

you can also try to use Temp Table to hold vFiles not found in dictFilesFound dictionary and slightly modify the code.

the temp table"

tblNotInDict (table)
FileName (short text) (index with duplicate)

the new code:

Dim db As DAO.Database
dim rs As DAO.Recordset
set db = Currentdb

' clear contents of temp table
db.Execute "delete * from tblNotInDict;"
set rs = db.OpenRecordset("tblNotInDict")
' fill table with records not in dictFilesFound
For Each vFile In dictClipsDB.Keys
    If Not dictFilesFound.Exists(vFile) Then
        rs.AddNew
        rs!FileName = vFile & ""
        rs.Update
    End If
Next vFile
' close the recordset
rs.Close: set rs = Nothing

' the actual Update query
db.Execute "Update tblClips Set blnAlive = False Where fldLocation IN (SELECT [FileName] from tblNotInDict);"
' close the database object
set db=nothing

 

[ebs17]

The dictionary objects dictClipsDB and dictFilesFound must first be filled. How is this done? Analyzing the file system will take some time in itself, depending on the volume, and will contribute to the slowness.

A dictionary and a collection offer very fast direct individual access to information, but one rule for performance is: mass data processing beats serial individual data processing. So if the contents of the file system analysis are loaded directly into a table, this table can be compared with an existing table once using a proper SQL query.

A simple possibility: Read a single directory directly into a text file, which is then used as a table.

Sub call_ListFilesToFile()
    Dim db As DAO.Database
    Dim sSQL As String

    Set db = CurrentDb
    ListFilesToFile "X:\OneFolder", "D:\log.txt", "pdf"
    sSQL = "UPDATE tblClips AS C SET C.blnAlive = FALSE WHERE NOT EXISTS" & _
         " (SELECT NULL FROM [Text;;FMT=Delimited;HDR=no;IMEX=2;CharacterSet=850;DATABASE=D:\].log.txt AS X  WHERE X.F1 = C.fldLocation)"
    db.Execute sSQL, dbFailOnError
   
End Sub

Public Function ListFilesToFile(ByVal StartDirectory As String, _
                                ByVal PathOutputFile As String, _
                                Optional ByVal FileExtension As String = "*") As Boolean
' Output the list of full paths to a text file

    With CreateObject("WScript.Shell")
        .Run "cmd.exe /c dir " & StartDirectory & "\*." & FileExtension & " /b/s >" & PathOutputFile & Chr(34)
    End With

    ListFilesToFile = True
End Function

If you need more speed, you could import the generated text file into an Access table and index the field. Then maybe also with other suitable query formulations.

 

[cheekybuddha]

    With CreateObject("WScript.Shell")
        .Run "cmd.exe /c dir " & StartDirectory & "\*." & FileExtension & " /b/s >" & PathOutputFile & Chr(34)
    End With

Might you need an extra Chr(34) in this expression?

 

[ebs17]

No.
Have you tested it yet, or are you just thinking about it?

 

[cheekybuddha]

No.
Have you tested it yet, or are you just thinking about it?

Not tested, just thinking about it. Will test when I have a chance.

 

[cheekybuddha]

Quick test:

Immediate Window:

FileExtension = "*"
StartDirectory = "Documents"
PathOutputFile = "Documents\My Data Sources"
?"cmd.exe /c dir " & StartDirectory & "\*." & FileExtension & " /b/s >" & PathOutputFile & Chr(34)
cmd.exe /c dir Documents\*.* /b/s >Documents\My Data Sources"

Run in command window:

C:\Users\dm>cmd.exe /c dir Documents\*.* /b/s >Documents\My Data Sources\test.txt"
The system cannot find the file specified.

Run again adding an extra double quote before PathOutputFile:

C:\Users\dm>cmd.exe /c dir Documents\*.* /b/s >"Documents\My Data Sources\test.txt"

C:\Users\dm>dir "Documents\My Data Sources\test.txt"
 Volume in drive C has no label.
 Volume Serial Number is XXXX-1234

 Directory of C:\Users\dm\Documents\My Data Sources

03/08/2024  10:49            21,638 test.txt
               1 File(s)         21,638 bytes
               0 Dir(s)  12,885,241,856 bytes free

 

[ebs17]

OK, it worked for me, but I didn't have spaces in the names anywhere.
Some cosmetic work for an extension:

Public Function ListFilesToFile(ByVal StartDirectory As String, _
                                ByVal PathOutputFile As String, _
                                Optional ByVal FileExtension As String = "*") As Boolean
' Output the list of full paths to a text file
    Dim sCommand As String
  
    sCommand = "cmd.exe /c dir " & _
        Chr(34) & StartDirectory & "\*." & FileExtension & Chr(34) & _
        " /b/s >" & _
        Chr(34) & PathOutputFile & Chr(34)
    Debug.Print sCommand
    With CreateObject("WScript.Shell")
        .Run sCommand
    End With

    ListFilesToFile = True
End Function

Note: With this method, the characters in the path and file names should be less than ASCII code 127.

 

[Milhouse4118]

Wow. Thanks for the replies. I now have quite a bit to work with and some new ideas to try. I'll try to report back as I develop the solution...

 

[Pat Hartman]

Taking a step back, the core purpose of my database is to catalog different files on my computer. I start out by scanning specific directories and building a dictionary of all found files (I used to use a Collection for these found files, but the dictionary runs much faster).

Therein lies the problem. This is not an appropriate use of a dictionary or a collection. Just use a table.

Welcome.

 

[The_Doc_Man]

Because a dictionary is a memory-resident object derived from the Scripting library, it occupies physical (and virtual) memory from WorkSpace or Database objects whereas using a table to store file names (potentially heading towards a JOIN) uses BE (file/disk) space. For smaller dictionary lists, this is not excessively bad, but as the dictionary grows, so does its demand on address space. Granted, it might take a long time to issue enough Dictionary.Add operations to blow out the Heap - which is part of the Access working memory area. But if it does, you have crashed the DB in a way that might be hard to recover.