VB SQLite Library (COM-Wrapper)

[Schmidt]

But it can't be done in memory, it would exhaust the available RAM for tables with millions records...

There's a relative simple (well-known) solution for this kind of thing:
- process the Data in "chunks"
- the "chunks" here being: "sets of records, limited to e.g. a size of 256 in the SQLite-case"
- done via applying an "In Clause" in the where-condition (to find matches)

Doing it that way, will reduce the "overhead involved, when building a Recordset".

In my Performance-Test, I get about 45% faster results with SQLite (compared to DAO-Seek):


And that means, that a cCursor-wrapping for SQLite (with a MoveNext, and maybe a Seek-Method),
would show the same performance-advantage (saving a few lines, to avoid working in "chunks").

Here is the Form-Code (which needs a DAO, and an RC6-ref in the Project):

Option Explicit
 
Private Sub Form_Load()
    Dim iDbDAO As Database, iRecDAO As Recordset
    Dim iCon As cConnection, iCommand As cCommand
    Dim C As Long, B() As Byte, iT1
    
    If New_c.FSO.FileExists(App.Path & "\SQLite.db") Then
       New_c.FSO.DeleteFile App.Path & "\SQLite.db"
    End If
    
    CreateSQLiteDB
    
    Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
    Set iRecDAO = iDbDAO.OpenRecordset("Files")
    
    Set iCon = New_c.Connection(App.Path & "\SQLite.db")
    Set iCommand = iCon.CreateCommand("INSERT INTO Files (Path_Hash, FileLen, Date, Image_Data) VALUES (?,?,?,?)")
  
    iT1 = Timer
    iCon.BeginTrans
        Do Until iRecDAO.EOF
            iCommand.SetText 1, iRecDAO(1).Value
            iCommand.SetInt32 2, iRecDAO(2).Value
            iCommand.SetDouble 3, iRecDAO(3).Value
            B = iRecDAO(4).Value: iCommand.SetBlobPtr 4, VarPtr(B(0)), UBound(B) + 1
            
            iCommand.Execute
            iRecDAO.MoveNext
        Loop
    iCon.CommitTrans
    LogText "Time to fill DB: " & Round(Timer - iT1, 2) & vbCrLf
End Sub

Private Sub CreateSQLiteDB()
    Dim iCon As cConnection
    Set iCon = New_c.Connection(App.Path & "\SQLite.db", DBCreateNewFileDB)
    
    iCon.Execute "CREATE TABLE Files (ID_File INTEGER PRIMARY KEY, Path_Hash TEXT NOT NULL, FileLen INTEGER DEFAULT 0 NOT NULL, Date REAL DEFAULT 0 NOT NULL, Image_Data BLOB NOT NULL)"
    iCon.Execute "CREATE INDEX Path_Hash ON Files (Path_Hash)"
End Sub

Private Sub LogText(nText As String)
    txtLog.SelText = nText & vbCrLf
    txtLog.Refresh
End Sub
    
Private Sub Command2_Click()
    Dim iKeys(100000) As String, iIndexes(100000) As Long
    Dim iCon As cConnection, iDSet As cRecordset
    Dim iDbDAO As Database, iRecDAO As Recordset
    Dim C As Long, ID As Long, PH As String, FL As Long, DT As Date, BD() As Byte
    Dim iT1
 
    Set iCon = New_c.Connection(App.Path & "\SQLite.db")
    Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
 
    ' load keys
    C = 0
    Set iDSet = iCon.OpenRecordset("SELECT Path_Hash FROM Files")
    Do Until iDSet.EOF
        iKeys(C) = iDSet(0).Value
        If Len(iKeys(C)) <> 32 Then Stop
        C = C + 1
        iDSet.MoveNext
    Loop
    
    ' set random indexes
    For C = 0 To UBound(iIndexes)
        iIndexes(C) = Rnd * 100000
    Next
 
    ' seach for existent keys
    iT1 = Timer
    Dim HDic As cSortedDictionary, HLst() As String, i As Long
    Set HDic = New_c.SortedDictionary(TextCompare, False)
    For C = 0 To UBound(iIndexes)
        If Not HDic.Exists(iKeys(iIndexes(C))) Then HDic.Add iKeys(iIndexes(C))
        If HDic.Count = 256 Then '<- only when a certain "chunksize-limit" is reached, we talk with the DB
           ReDim HLst(0 To HDic.Count - 1) 'let's build a List for an SQL In (...) comparison
           For i = 0 To HDic.Count - 1: HLst(i) = HDic.KeyByIndex(i): Next
           Set iDSet = iCon.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
           
           Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
              ID = iDSet!ID_File.Value
              PH = iDSet!Path_Hash.Value
              FL = iDSet!FileLen.Value
              DT = iDSet!Date.Value
              BD = iDSet!Image_Data.Value
              
              HDic.Remove PH
              iDSet.MoveNext
           Loop
           For i = 0 To HDic.Count - 1 'process all cache-misses (left over in HDic)
               Stop
           Next
           If HDic.Count Then HDic.RemoveAll
        End If
    Next
    LogText "SQLite: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2)
    
    
    iT1 = Timer
    Set iRecDAO = iDbDAO.OpenRecordset("Files")
    iRecDAO.Index = "Path_Hash"
    For C = 0 To UBound(iIndexes)
        iRecDAO.Seek "=", iKeys(iIndexes(C))
        If Not iRecDAO.NoMatch Then
            ID = iRecDAO!ID_File.Value
            PH = iRecDAO!Path_Hash.Value
            FL = iRecDAO!FileLen.Value
            DT = iRecDAO!Date.Value
            BD = iRecDAO!Image_Data.Value
        Else
            Stop
        End If
    Next
    LogText "DAO: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
End Sub

The above code has about "10 lines overhead" (for the "chunking"), compared with the DAO-solution.

HTH

Hah... have just seen that JPBro suggested the same thing in the post before this one...

Olaf