VB SQLite Library (COM-Wrapper)

[Krool]

sqlite3_bind_text16 now fixed to use directly StrPtr(Value).
I don't know what triggered me to cache it in a variable and append a vbNullChar. Normally I don't do it when sending a string to an API.

Btw, in SQLiteCursor.SetParameterValue there is Case vbDouble but vbSingle is missing.

That was done by intention to force strict data types. So storing a Date variable needs to be passed with CDbl(), for example. (or as string or julian date .. , depends how somebody handles it)
Only for vbInteger/vbByte there is an exception, since a 0 is already an integer unless 0& is used.
If you think otherwise please suggest. Thx

[Eduardo-]

sqlite3_bind_text16 now fixed to use directly StrPtr(Value).
I don't know what triggered me to cache it in a variable and append a vbNullChar. Normally I don't do it when sending a string to an API.

It was needed with UTF8.
I've tried without the null char and it didn't work when it was working with UTF8.

[Krool]

Usage of sqlite3_prepare16_v2/v3 instead of sqlite3_prepare_v2/v3, if applicable.

I think that's it for now related to optimizations..

[Eduardo-]

JFYI the performance issue of sqlite3win32.dll was never totally fixed, only improved in the previous version but now in the current version it is slow again.

[Krool]

Try VS2015 which is the last with XP support (and use static build to not depend on the runtime DLLs).

Optimizations in release build are most welcome and expected of course.

cheers,
</wqw>

I installed in vs 2017 the v14 (2015) compiler and clicked the windows-xp support (v140_xp)
However, building the dll with v14 compiler works, but it won't work in XP vm. So manually compiling I do something wrong.. probably it needs to set to target XP.. hmm.

EDIT: forgot to add the section (which you mention)

if [%1]==[XP] (
    set "include=C:\Program Files (x86)\Microsoft SDKs\Windows\v7.1A\include;%include%"
    set "lib=C:\Program Files (x86)\Microsoft SDKs\Windows\v7.1A\lib;%lib%"
)

Now it works..

JFYI the performance issue of sqlite3win32.dll was never totally fixed, only improved in the previous version but now in the current version it is slow again.

Ok, the only difference is that I set to the SQLITE_DEFAULT_CACHE to -8000 and Olaf set it to +8196.
So I defined effectively a cache of 8000*1024 and Olaf then 8196*4096 (the default page size is 4096).
Maybe that's it ?
To note is that -8000 is already a 4x increase of the default -2000.

[Krool]

Here is the VS2015 compiled version of VBSQLite12. Not sure if it's kept binary compatible with the original though.

cheers,
</wqw>

Hi wqweto,

I was able to compile a sqlite3win32.dll which works fine on Windows XP for debug reasons.
However, the VBSQLite12.DLL (Ax-DLL) compiles fine (I add a environment variables for the lib folder btw to solve the VS2015 compile for VB6 IDE)

BUT, both mine and yours works good in a VB6 IDE on Windows 10 for example. However, trying to register (via regsvr32) the Ax-DLL on Windows XP throws out an error and can't be registered.

[Eduardo-]

FWIW I could not register wqweto's VBSQLite12.DLL (Ax-DLL).

It must be because Windows 11 (Windows 10 already) is problematic regarding colliding COM components versions (or something like that; I don't know exactly what the problem is).
Sometimes I needed to go through the registry and manually delete all entries related to an OCX to be able to register another version after I had registered a newer one (or different, non binary compatible but that in some way collide).
That didn't happen in previous Windows.

PS: even if you previously unregistered the other version.

[wqweto]

In Win10 and Win11 there are two registry "branches" for Admin users (every user in fact).

Technically you have to unregister both from elevated command prompt and unelevated command prompt to be sure everything is unregistered.

cheers,
</wqw>

[Krool]

In Win10 and Win11 there are two registry "branches" for Admin users (every user in fact).

Technically you have to unregister both from elevated command prompt and unelevated command prompt to be sure everything is unregistered.

cheers,
</wqw>

I can't register your (or mine) Ax-Dll on a vanilla XP vm.
So something is there..

[wqweto]

Yes, there is a problem with the ucrt.lib and vcruntime.lib linked files. Should have been libucrt.lib and libvcruntime.lib instead for a static build.

https://docs.microsoft.com/en-us/cpp...?view=msvc-170

Unfortunately these static .lib files error out with incompatibilities with VB6 produces VBSQLite12.OBJ which already has some symbols embedded from MSVCRT.LIB from VC6 compiler

libucrt.lib(atan.obj) : error LNK2005: __CIatan already defined in VBSQLite12.OBJ
libucrt.lib(cos.obj) : error LNK2005: __CIcos already defined in VBSQLite12.OBJ
libucrt.lib(87ctran.obj) : error LNK2005: __CIexp already defined in VBSQLite12.OBJ
libucrt.lib(sin.obj) : error LNK2005: __CIsin already defined in VBSQLite12.OBJ
libucrt.lib(sqrt.obj) : error LNK2005: __CIsqrt already defined in VBSQLite12.OBJ
libucrt.lib(tan.obj) : error LNK2005: __CItan already defined in VBSQLite12.OBJ
libucrt.lib(log.obj) : error LNK2005: __CIlog already defined in VBSQLite12.OBJ

I don't have a solution for this at the moment. Further research will be needed if it's even possible to mix .obj files from different versions of the compiler.

Bummer!

cheers,
</wqw>

[Krool]

Didn't test yet (no time) but will a /NODEFAULTLIB:"libucrt.lib" solve it?

EDIT: no effect ...

[Krool]

I could compile now VBSQLite12.DLL (vs 2015 sqlite3) and register/use it in XP vm.

Though this was possible only due to

/FORCE:MULTIPLE /FORCE:UNRESOLVED

and that's a no go..

[Krool]

With the VC6.0 we can link as it was compiled with /MD.
It would cause the same issues when would done with /MT.

the trick managed to compile a v140_xp statically in an AxDll. (see VbPng project)

But it's very cumbersome. It needs an asm file compiled as a .obj (https://github.com/thetrik/VbPng/blo.../gostartup.asm) and /ENTRY:VBDllMain in the LinkSwitches of the Ax Dll.
Of course the "Initialize VbPng" shall be removed for our needs.

But that's one point. In addition he uses /OPT:REF instead NOREF but replaced the vb6 linker by a modern one. And that made me halt.

So, I guess I will stick to VC6.0 and /MD as we can afford that msvcp60.dll is present.

[wqweto]

Yes, everything else would be too complicated or hacky. With VC6 using /MD it references MSVCRT.DLL which is a system dll (i.e. VC6 runtime does not have to be setup with separate installer).

I’ll see VbPng hack if we can use parts of it here. To build and ship VC6 binaries is the only choice for now. Does performance suffer too much?

[Krool]

Does performance suffer too much?

For me it's fine. Maybe Eduardo can give results of the latest benchmarks.
Perhaps SQLITE_DEFAULT_CACHE needs to be increased a little further and the differences will be negligible..

[wqweto]

For me it's fine. Maybe Eduardo can give results of the latest benchmarks.
Perhaps SQLITE_DEFAULT_CACHE needs to be increased a little further and the differences will be negligible..

That would be cool then, no need to "hammer" VS2015 .obj files although would be interesting to support more modern compilers.

cheers,
</wqw>

[Eduardo-]

Maybe Eduardo can give results of the latest benchmarks.

Tested with compiled DLL:

Private Sub Command2_Click()
    Dim iDbDAO As Database, iRecDAO As Recordset
    Dim iRC6Connection As cConnection, iDSet As cRecordset
    Dim iVBSQLiteConnection As New SQLiteConnection
    Dim iVBSQLiteCursor As SQLiteCursor
    Dim iRC6Cursor As cCursor
    Dim iKeys(100000) As String
    Dim iIndexes(100000) As Long
    Dim c As Long
    Dim ID As Long
    Dim PH As String
    Dim FL As Long
    Dim DT As Date
    Dim BD() As Byte
    Dim iT1
 
    Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
    Set iRC6Connection = New_c.Connection(App.Path & "\SQLite.db")
    iVBSQLiteConnection.OpenDB App.Path & "\SQLite.db", SQLiteReadWrite

    
    ' load keys
    c = 0
    Set iDSet = iRC6Connection.OpenRecordset("SELECT Path_Hash FROM Files")
    Do Until iDSet.EOF
        iKeys(c) = iDSet(0).Value: c = c + 1
        iDSet.MoveNext
    Loop
    Set iDSet = Nothing
    
    ' set random indexes
    For c = 0 To UBound(iIndexes)
        iIndexes(c) = Rnd * 100000
    Next
 
    ' RC6-Chunks
    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 = 512 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 = iRC6Connection.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
           
           Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
              ID = iDSet(0).Value
              PH = iDSet(1).Value
              FL = iDSet(2).Value
              DT = iDSet(3).Value
              BD = iDSet(4).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
    If HDic.Count 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 = iRC6Connection.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
       
       Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
          'ID = iDSet(0).Value
          PH = iDSet(1).Value
          FL = iDSet(2).Value
          DT = iDSet(3).Value
          BD = iDSet(4).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
    LogText "RC6-Chunks: " & vbTab & Round(Timer - iT1, 2)
    Set iDSet = Nothing
    
    ' DAO
    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: " & vbTab & vbTab & Round(Timer - iT1, 2)
    
    ' VBSQLite cursor
    iT1 = Timer
    iVBSQLiteConnection.Execute "Begin Transaction"
    Set iVBSQLiteCursor = iVBSQLiteConnection.CreateCursor("Select * From Files Where Path_Hash=?")
    
    For c = 0 To UBound(iIndexes)
        iVBSQLiteCursor.SetParameterValue 1, iKeys(iIndexes(c))
        If iVBSQLiteCursor.RecordCount Then
            'ID = iVBSQLiteCursor(1).Value
            'PH = iVBSQLiteCursor(2).Value
            FL = iVBSQLiteCursor(3).Value
            DT = iVBSQLiteCursor(4).Value
            BD = iVBSQLiteCursor(5).Value
        Else
            Stop
        End If
    Next
    iVBSQLiteConnection.Execute "End Transaction"
    LogText "VBSQLite Cursor: " & vbTab & Round(Timer - iT1, 2)
    Set iVBSQLiteCursor = Nothing
    Set iVBSQLiteConnection = Nothing
    

    ' RC6 cursor
    iT1 = Timer
    iRC6Connection.BeginTrans
    Set iRC6Cursor = iRC6Connection.CreateCursor("Select * From Files Where Path_Hash=?")
    
    For c = 0 To UBound(iIndexes)
        iRC6Cursor.SetText 1, iKeys(iIndexes(c))
        If iRC6Cursor.Step Then
            'ID = iVBSQLiteCursor(1).Value
            'PH = iVBSQLiteCursor(2).Value
            FL = iRC6Cursor.ColVal(2)
            DT = iRC6Cursor.ColVal(3)
            BD = iRC6Cursor.ColVal(4)
        Else
            Stop
        End If
        iRC6Cursor.Reset
    Next
    iRC6Connection.CommitTrans
    LogText "RC6 Cursor: " & vbTab & Round(Timer - iT1, 2)
    Set iRC6Cursor = Nothing

End Sub

[Krool]

Tested with compiled DLL:

Private Sub Command2_Click()
    Dim iDbDAO As Database, iRecDAO As Recordset
    Dim iRC6Connection As cConnection, iDSet As cRecordset
    Dim iVBSQLiteConnection As New SQLiteConnection
    Dim iVBSQLiteCursor As SQLiteCursor
    Dim iRC6Cursor As cCursor
    Dim iKeys(100000) As String
    Dim iIndexes(100000) As Long
    Dim c As Long
    Dim ID As Long
    Dim PH As String
    Dim FL As Long
    Dim DT As Date
    Dim BD() As Byte
    Dim iT1
 
    Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
    Set iRC6Connection = New_c.Connection(App.Path & "\SQLite.db")
    iVBSQLiteConnection.OpenDB App.Path & "\SQLite.db", SQLiteReadWrite

    
    ' load keys
    c = 0
    Set iDSet = iRC6Connection.OpenRecordset("SELECT Path_Hash FROM Files")
    Do Until iDSet.EOF
        iKeys(c) = iDSet(0).Value: c = c + 1
        iDSet.MoveNext
    Loop
    Set iDSet = Nothing
    
    ' set random indexes
    For c = 0 To UBound(iIndexes)
        iIndexes(c) = Rnd * 100000
    Next
 
    ' RC6-Chunks
    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 = 512 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 = iRC6Connection.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
           
           Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
              ID = iDSet(0).Value
              PH = iDSet(1).Value
              FL = iDSet(2).Value
              DT = iDSet(3).Value
              BD = iDSet(4).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
    If HDic.Count 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 = iRC6Connection.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
       
       Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
          'ID = iDSet(0).Value
          PH = iDSet(1).Value
          FL = iDSet(2).Value
          DT = iDSet(3).Value
          BD = iDSet(4).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
    LogText "RC6-Chunks: " & vbTab & Round(Timer - iT1, 2)
    Set iDSet = Nothing
    
    ' DAO
    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: " & vbTab & vbTab & Round(Timer - iT1, 2)
    
    ' VBSQLite cursor
    iT1 = Timer
    iVBSQLiteConnection.Execute "Begin Transaction"
    Set iVBSQLiteCursor = iVBSQLiteConnection.CreateCursor("Select * From Files Where Path_Hash=?")
    
    For c = 0 To UBound(iIndexes)
        iVBSQLiteCursor.SetParameterValue 1, iKeys(iIndexes(c))
        If iVBSQLiteCursor.RecordCount Then
            'ID = iVBSQLiteCursor(1).Value
            'PH = iVBSQLiteCursor(2).Value
            FL = iVBSQLiteCursor(3).Value
            DT = iVBSQLiteCursor(4).Value
            BD = iVBSQLiteCursor(5).Value
        Else
            Stop
        End If
    Next
    iVBSQLiteConnection.Execute "End Transaction"
    LogText "VBSQLite Cursor: " & vbTab & Round(Timer - iT1, 2)
    Set iVBSQLiteCursor = Nothing
    Set iVBSQLiteConnection = Nothing
    

    ' RC6 cursor
    iT1 = Timer
    iRC6Connection.BeginTrans
    Set iRC6Cursor = iRC6Connection.CreateCursor("Select * From Files Where Path_Hash=?")
    
    For c = 0 To UBound(iIndexes)
        iRC6Cursor.SetText 1, iKeys(iIndexes(c))
        If iRC6Cursor.Step Then
            'ID = iVBSQLiteCursor(1).Value
            'PH = iVBSQLiteCursor(2).Value
            FL = iRC6Cursor.ColVal(2)
            DT = iRC6Cursor.ColVal(3)
            BD = iRC6Cursor.ColVal(4)
        Else
            Stop
        End If
        iRC6Cursor.Reset
    Next
    iRC6Connection.CommitTrans
    LogText "RC6 Cursor: " & vbTab & Round(Timer - iT1, 2)
    Set iRC6Cursor = Nothing

End Sub

Now it would be interesting to see if there is any difference when increasing SQLITE_DEFAULT_CACHE from -8000 to let's say -16000.

You may test via pragma. So we don't need to recompile for the moment.

PRAGMA cache_size = -16000

[Eduardo-]

Values over 8000 seem to make no difference at all.

But I see that the number need to be positive, if I put negative numbers they seem to have some effect but not so good (it is slower, but better than zero).

[Krool]

Values over 8000 seem to make no difference at all.

But I see that the number need to be positive, if I put negative numbers they seem to have some effect but not so good (it is slower, but better than zero).

Ok, I see.
Now let's just double check if a VBSQLite12.DLL compiled with vs2015 (vs14_xp) and /MD makes any difference.
See here test VBSQLite12_vs14.DLL (it's not binary compatible, so you could register beneath ordinary VBSQLite12.DLL; just for test)

[Eduardo-]

I could not register it. The message says like there is a missing dependency.

[Krool]

I could not register it. The message says like there is a missing dependency.

Yes it's dynamic. (/MD)
As you noticed the static approach (/MT) is a bit challenging.

So you can't register it on windows xp, for example. (Unless it would be possible to install a visual c++ 2015 redistributable in win xp, which could be the case, don't know)

I just wanted to test if the effort makes sense at all.

[Krool]

With VC6 using /MD it references MSVCRT.DLL which is a system dll (i.e. VC6 runtime does not have to be setup with separate installer).

Yes, I tested the sqlite3win32 with /MT using VC6 and I could compile the VBSQLite12.DLL using

KERNEL32.LIB msvcrt.lib /OPT:NOREF /OPT:NOWIN98

But as you said, that makes no big deal. So for VC6 we shall stick with /MD I guess.

[Krool]

One step forward for the vs 2015 /MT compile.

I included the "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.1A\Lib" (v140_xp) in the environment variable list, so that kernel32.lib is linked from that.

So, no unresolved external symbols anymore. However, the "fatal error LNK1169: one or more multiply defined symbols found" remain. However, it can be overcome with /FORCE:MULTIPLE.

kernel32.lib libvcruntime.lib libucrt.lib /OPT:NOREF /OPT:NOWIN98 /FORCE:MULTIPLE

In my tests in a vanilla windows XP VM it worked without issues.

@ Eduardo,
can you test it ? VBSQLite12_MT14.DLL
And of course report if the VS2015 dll is faster than the VS6 dll..

[Eduardo-]

It registered and worked, but it is slower.

[wqweto]

Time to fill DB: 0.45

RC6-Cursor: time searching 100,000 randoms records that are found: 0.26

DAO: time searching 100,000 randoms records that are found: 0.72

SQLiteCursor: time searching 100,000 randoms records that are found: 0.26

. . . using this compilation of VBSQLite12.zip where .cobj from sqlite3win32 are compiled with set cl_opt=/c /MT /O2 /Gz only.

Final binary imports seem to be only from KERNEL32.dll and MSVBVM60.DLL

cheers,
</wqw>

[Eduardo-]

Time to fill DB: 0.45

RC6-Cursor: time searching 100,000 randoms records that are found: 0.26

DAO: time searching 100,000 randoms records that are found: 0.72

SQLiteCursor: time searching 100,000 randoms records that are found: 0.26

. . . using this compilation of VBSQLite12.zip where .cobj from sqlite3win32 are compiled with set cl_opt=/c /MT /O2 /Gz only.

Final binary imports seem to be only from KERNEL32.dll and MSVBVM60.DLL

cheers,
</wqw>

wqweto: you can see that I get very different results, twice the time actually for your VBSQLite12.DLL

Files updated in GitHub temp test folder.

[Krool]

Eduardo,

just re-compiled VBSQLite12_MT14.DLL to use now O2. (you need to unregister old first and re-register this one then)
It is just a test to see if at least my and wqweto's builds results now in the same speed results.

[wqweto]

Try replacing

'            FL = iVBSQLiteCursor(3).Value
'            DT = iVBSQLiteCursor(4).Value
'            BD = iVBSQLiteCursor(5).Value

. . . with

            FL = iVBSQLiteCursor.Value(2)
            DT = iVBSQLiteCursor.Value(3)
            BD = iVBSQLiteCursor.Value(4)

. . . for optimal performance:

RC6-Chunks: 	0.71
DAO: 		0.73
VBSQLite Cursor: 	0.41
RC6 Cursor: 	0.41

cheers,
</wqw>

[Eduardo-]

Try replacing...

So, there is a huge performance penalty for accessing through the normal interface.

Times now:

RC6-Chunks: 	0,85
DAO: 		1,3
VBSQLite Cursor: 	0,41
RC6 Cursor: 	0,38

I see that you get different numbers still, also for DAO, I guess that it may have something to do with the machine, is AMD your processor?

PS: BTW, what is your code for DAO? (I tested assigning to field variables in the past and didn't see so huge change)

[Eduardo-]

    ' DAO
    Dim f2 As Field
    Dim f3 As Field
    Dim f4 As Field

    iT1 = Timer
    Set iRecDAO = iDbDAO.OpenRecordset("Files")
    iRecDAO.Index = "Path_Hash"
    Set f2 = iRecDAO.Fields(2)
    Set f3 = iRecDAO.Fields(3)
    Set f4 = iRecDAO.Fields(4)
    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 = f2.Value
            DT = f3.Value
            BD = f4.Value
        Else
            Stop
        End If
    Next
    LogText "DAO: " & vbTab & vbTab & Round(Timer - iT1, 2)

Result:

DAO: 		1,22

[wqweto]

Tested latest source from github repo with Value fixes on an Intel i9-12900k desktop machine.

[Krool]

So, I still don't get it.
What's now faster Eduardo? VC6 or VS2015 dll?
Maybe you can repeat the tests with the replaced piece of sample suggested by wqweto.

[Eduardo-]

So, I still don't get it.
What's now faster Eduardo? VC6 or VS2015 dll?
Maybe you can repeat the tests with the replaced piece of sample suggested by wqweto.

My test where it worked on pair of RC6 was wqweto's compilation.
I also noticed that there is also too much overhead when using the "normal" fields collection, but it worked OK when accessing the data more directly through the cursor object's value property.

Results from tests comparing different compilations with DAO and RC6, all accessing the values directly from Cursor object's value property. All compiled, same code at client side (which is posted):

DAO:	1.25
RC6 Cursor:	0.38
VBSQLite Cursor, Krool's VBSQLite12_MT14:	1.16
VBSQLite Cursor, Krool's official current version:	1.28
VBSQLite Cursor, wqweto's version:	0.41

All ran now, one after another.

Project updated at GitHub.
Main part copied here:

Private Sub Command2_Click()
    Dim iDbDAO As Database, iRecDAO As Recordset
    Dim iRC6Connection As cConnection, iDSet As cRecordset
    Dim iVBSQLiteConnection As New SQLiteConnection
    Dim iVBSQLiteCursor As SQLiteCursor
    Dim iRC6Cursor As cCursor
    Dim iKeys(100000) As String
    Dim iIndexes(100000) As Long
    Dim c As Long
    Dim ID As Long
    Dim PH As String
    Dim FL As Long
    Dim DT As Date
    Dim BD() As Byte
    Dim iT1
 
    Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
    Set iRC6Connection = New_c.Connection(App.Path & "\SQLite.db")
    iVBSQLiteConnection.OpenDB App.Path & "\SQLite.db", SQLiteReadWrite

    
    ' load keys
    c = 0
    Set iDSet = iRC6Connection.OpenRecordset("SELECT Path_Hash FROM Files")
    Do Until iDSet.EOF
        iKeys(c) = iDSet(0).Value: c = c + 1
        iDSet.MoveNext
    Loop
    Set iDSet = Nothing
    
    ' set random indexes
    For c = 0 To UBound(iIndexes)
        iIndexes(c) = Rnd * 100000
    Next
 
    ' RC6-Chunks
    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 = 512 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 = iRC6Connection.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
           
           Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
              ID = iDSet(0).Value
              PH = iDSet(1).Value
              FL = iDSet(2).Value
              DT = iDSet(3).Value
              BD = iDSet(4).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
    If HDic.Count 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 = iRC6Connection.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
       
       Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
          'ID = iDSet(0).Value
          PH = iDSet(1).Value
          FL = iDSet(2).Value
          DT = iDSet(3).Value
          BD = iDSet(4).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
    LogText "RC6-Chunks: " & vbTab & Round(Timer - iT1, 2)
    Set iDSet = Nothing
    
    ' DAO
    Dim f2 As Field
    Dim f3 As Field
    Dim f4 As Field
    
    iT1 = Timer
    Set iRecDAO = iDbDAO.OpenRecordset("Files")
    iRecDAO.Index = "Path_Hash"
    Set f2 = iRecDAO.Fields(2)
    Set f3 = iRecDAO.Fields(3)
    Set f4 = iRecDAO.Fields(4)
    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 = f2.Value
            DT = f3.Value
            BD = f4.Value
        Else
            Stop
        End If
    Next
    LogText "DAO: " & vbTab & vbTab & Round(Timer - iT1, 2)
    
    ' VBSQLite cursor
    iT1 = Timer
    iVBSQLiteConnection.Execute "Begin Transaction"
    Set iVBSQLiteCursor = iVBSQLiteConnection.CreateCursor("Select * From Files Where Path_Hash=?")
    
    For c = 0 To UBound(iIndexes)
        iVBSQLiteCursor.SetParameterValue 1, iKeys(iIndexes(c))
        If iVBSQLiteCursor.RecordCount Then
            'ID = iVBSQLiteCursor(1).Value
            'PH = iVBSQLiteCursor(2).Value
            FL = iVBSQLiteCursor.Value(2)
            DT = iVBSQLiteCursor.Value(3)
            BD = iVBSQLiteCursor.Value(4)
        Else
            Stop
        End If
    Next
    iVBSQLiteConnection.Execute "End Transaction"
    LogText "VBSQLite Cursor: " & vbTab & Round(Timer - iT1, 2)
    Set iVBSQLiteCursor = Nothing
    Set iVBSQLiteConnection = Nothing

    ' RC6 cursor
    iT1 = Timer
    iRC6Connection.BeginTrans
    Set iRC6Cursor = iRC6Connection.CreateCursor("Select * From Files Where Path_Hash=?")
    
    For c = 0 To UBound(iIndexes)
        iRC6Cursor.SetText 1, iKeys(iIndexes(c))
        If iRC6Cursor.Step Then
            'ID = iVBSQLiteCursor(1).Value
            'PH = iVBSQLiteCursor(2).Value
            FL = iRC6Cursor.ColVal(2)
            DT = iRC6Cursor.ColVal(3)
            BD = iRC6Cursor.ColVal(4)
        Else
            Stop
        End If
        iRC6Cursor.Reset
    Next
    iRC6Connection.CommitTrans
    LogText "RC6 Cursor: " & vbTab & Round(Timer - iT1, 2)
    Set iRC6Cursor = Nothing
End Sub

And Krool, I don't know which one of these are VC6 and VS2015, if any.

[Krool]

I recompiled VBSQLite12_MT14.DLL using /O2.
Did you replace it?

Otherwise I am clueless why the build by wqweto runs faster..

[Eduardo-]

I recompiled VBSQLite12_MT14.DLL using /O2.
Did you replace it?

Otherwise I am clueless why the build by wqweto runs faster..

I tested with the version that is found at the link I posted (I just downloaded it for the test).
You sent me that file before, probably another compilation? But now I used that one that is currently on GitHub.

[Krool]

Update released.

The sqlite3 c source was upgraded from version 3.37.2 (2022-01-06) to 3.39.2 (2022-07-21).

The compiler is still VC6. However, it uses now in addition the /O2 and /Gz compiler switches.
This means that the __cdecl ASM wrapper for the overloading of the SQL functions or aggregates is not needed anymore. Thus reducing an overhead and overall cleaner.

But it still uses /MD as msvcrt.dll is always available. When changing to VS2015 (v140_xp) someday then that build batch uses /MT of course.

Also upgraded the regexp c extension to the latest version.

[xiaoyao]

Are there two versions? One supports xp,A support for Win7, Win11?If some versions have a relatively small volume after compilation, while others are very large, it is recommended to keep two or three.Some people require newer features, while others require smaller sizes. Everyone chooses what they like.I have another question, can its reading directly return the content of the entire table? It means returning multiple fields and rows of results to a two-dimensional or one-dimensional array at once. I heard that the latest version of sqlite has gradually removed this feature

[xiaoyao]

Can I directly obtain the entire column of data for a certain field into a vb array, such as long type or text type. Call the API once to directly obtain the result. If we only encapsulate the original sqlite.dll, it definitely does not have such a function and needs to modify the source code
If I want to read 100000 rows and 30 columns of data, can I use multiple threads to read it?

[Nouyana]

Krool, can you implement the Fields property so that Columns(X + 1) = Fields(X)? For compatibility purposes.