VB SQLite Library (COM-Wrapper)

[Schmidt]

Did you post the code for that somewhere?

The new RC6-version (6.0.10) is now "online"...

Here's the final Test-Result (also incorporating Krools latest version-update, from one posting above)...


Here the Test-Code I've used:

Option Explicit
 
Private Sub Form_Load()
    Dim iDbDAO As Database, iRecDAO As Recordset
    Dim iCon As cConnection, iCommand As cCommand
    Dim iT1
    
    If New_c.FSO.FileExists(App.Path & "\SQLite.db3") Then
       New_c.FSO.DeleteFile App.Path & "\SQLite.db3"
    End If
    
    CreateSQLiteDB
    
    Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
    Set iRecDAO = iDbDAO.OpenRecordset("Files")
    
    Set iCon = New_c.Connection(App.Path & "\SQLite.db3")
    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
            iCommand.SetBlob 4, iRecDAO(4).Value

            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.db3", DBCreateNewFileDB)
    
'    iCon.Execute "CREATE TABLE Files ( Path_Hash Blob PRIMARY KEY NOT NULL, FileLen INTEGER DEFAULT 0 NOT NULL, Date REAL DEFAULT 0 NOT NULL, Image_Data BLOB NOT NULL) Without RowId"
    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, bKeys(100000) As String, iIndexes(100000) As Long
    Dim oCon As cConnection, oCur As cCursor
    Dim iDbDAO As Database, iRecDAO As Recordset
    Dim iSQLite As New SQLiteConnection, iCur As SQLiteCursor
    Dim C As Long, ID As Long, PH As String, FL As Long, DT As Date, BD() As Byte
    Dim iT1
 
    Set oCon = New_c.Connection(App.Path & "\SQLite.db3")
    Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
        iSQLite.OpenDB App.Path & "\SQLite.db3", SQLiteReadWrite

    
    ' load keys
    C = 0
    Set oCur = oCon.CreateCursor("SELECT Path_Hash FROM Files")
    Do While oCur.Step
       iKeys(C) = oCur.ColVal(0): C = C + 1
    Loop
 
    ' set random indexes
    For C = 0 To UBound(iIndexes)
        iIndexes(C) = Int(Rnd * 100000)
    Next
 
  
    iT1 = Timer 
    oCon.BeginTrans
        Set oCur = oCon.CreateCursor("Select * From Files Where Path_Hash=?")
        
        For C = 0 To UBound(iIndexes)
            oCur.SetText 1, iKeys(iIndexes(C))
 
            If oCur.Step Then
'                  ID = oCur.ColVal(0)
'                  PH = oCur.ColVal(1)
                FL = oCur.ColVal(2)
                DT = oCur.ColVal(3)
                BD = oCur.ColVal(4)
            Else
                Stop
            End If
        Next
    oCon.CommitTrans
    LogText "RC6-Cursor: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf

 
    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(0).Value
'            PH = iRecDAO(1).Value
            FL = iRecDAO(2).Value
            DT = iRecDAO(3).Value
            BD = iRecDAO(4).Value
        Else
            Stop
        End If
    Next
    LogText "DAO: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
  

    
    iT1 = Timer  
    iSQLite.Execute "Begin"
        Set iCur = iSQLite.CreateCursor("Select * From Files Where Path_Hash=?")
        
        Dim cFL As SQLiteColumn: Set cFL = iCur(3)
        Dim cDT As SQLiteColumn: Set cDT = iCur(4)
        Dim cBD As SQLiteColumn: Set cBD = iCur(5)
        
        For C = 0 To UBound(iIndexes)
            iCur.SetParameterValue 1, iKeys(iIndexes(C))
     
            If iCur.RecordCount Then
    '            ID = iCur(1).Value
'                PH = iCur(2).Value
                FL = cFL.Value
                DT = cDT.Value
                BD = cBD.Value
            Else
                Stop
            End If
        Next
    iSQLite.Execute "Commit"
    LogText "SQLiteCursor: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
   
End Sub

Olaf