-
Re: VB SQLite Library (COM-Wrapper)
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. :)
Quote:
Originally Posted by
wqweto
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
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
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. :)
It was needed with UTF8.
I've tried without the null char and it didn't work when it was working with UTF8.
-
Re: VB SQLite Library (COM-Wrapper)
Usage of sqlite3_prepare16_v2/v3 instead of sqlite3_prepare_v2/v3, if applicable.
I think that's it for now related to optimizations..
-
Re: VB SQLite Library (COM-Wrapper)
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.
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
wqweto
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)
Code:
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..
Quote:
Originally Posted by
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.
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.
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
wqweto
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.
-
Re: VB SQLite Library (COM-Wrapper)
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.
-
Re: VB SQLite Library (COM-Wrapper)
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>
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
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>
I can't register your (or mine) Ax-Dll on a vanilla XP vm.
So something is there..
-
Re: VB SQLite Library (COM-Wrapper)
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
Code:
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>
-
Re: VB SQLite Library (COM-Wrapper)
Didn't test yet (no time) but will a /NODEFAULTLIB:"libucrt.lib" solve it?
EDIT: no effect ...
-
Re: VB SQLite Library (COM-Wrapper)
I could compile now VBSQLite12.DLL (vs 2015 sqlite3) and register/use it in XP vm.
Though this was possible only due to
Code:
/FORCE:MULTIPLE /FORCE:UNRESOLVED
and that's a no go..
-
Re: VB SQLite Library (COM-Wrapper)
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.
-
Re: VB SQLite Library (COM-Wrapper)
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?
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
wqweto
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..
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
Krool
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>
-
1 Attachment(s)
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
Krool
Maybe Eduardo can give results of the latest benchmarks.
Tested with compiled DLL:
Attachment 185466
Code:
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
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
Eduardo-
Tested with compiled DLL:
Attachment 185466
Code:
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.
Code:
PRAGMA cache_size = -16000
-
Re: VB SQLite Library (COM-Wrapper)
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).
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
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).
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)
-
Re: VB SQLite Library (COM-Wrapper)
I could not register it. The message says like there is a missing dependency.
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
Eduardo-
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.
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
wqweto
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
Code:
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.
-
Re: VB SQLite Library (COM-Wrapper)
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.
Code:
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..
-
1 Attachment(s)
Re: VB SQLite Library (COM-Wrapper)
It registered and worked, but it is slower.
Attachment 185475
-
Re: VB SQLite Library (COM-Wrapper)
Code:
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>
-
1 Attachment(s)
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
wqweto
Code:
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>
Attachment 185476
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.
-
Re: VB SQLite Library (COM-Wrapper)
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.
-
Re: VB SQLite Library (COM-Wrapper)
Try replacing
Code:
' FL = iVBSQLiteCursor(3).Value
' DT = iVBSQLiteCursor(4).Value
' BD = iVBSQLiteCursor(5).Value
. . . with
Code:
FL = iVBSQLiteCursor.Value(2)
DT = iVBSQLiteCursor.Value(3)
BD = iVBSQLiteCursor.Value(4)
. . . for optimal performance:
Code:
RC6-Chunks: 0.71
DAO: 0.73
VBSQLite Cursor: 0.41
RC6 Cursor: 0.41
cheers,
</wqw>
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
wqweto
Try replacing...
So, there is a huge performance penalty for accessing through the normal interface.
Times now:
Code:
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)
-
Re: VB SQLite Library (COM-Wrapper)
Code:
' 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:
-
Re: VB SQLite Library (COM-Wrapper)
Tested latest source from github repo with Value fixes on an Intel i9-12900k desktop machine.
-
Re: VB SQLite Library (COM-Wrapper)
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.
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
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.
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):
All ran now, one after another.
Project updated at GitHub.
Main part copied here:
Code:
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.
-
Re: VB SQLite Library (COM-Wrapper)
I recompiled VBSQLite12_MT14.DLL using /O2.
Did you replace it?
Otherwise I am clueless why the build by wqweto runs faster..
-
Re: VB SQLite Library (COM-Wrapper)
Quote:
Originally Posted by
Krool
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.
-
Re: VB SQLite Library (COM-Wrapper)
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.
-
Re: VB SQLite Library (COM-Wrapper)
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
-
Re: VB SQLite Library (COM-Wrapper)
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?
-
Re: VB SQLite Library (COM-Wrapper)
Krool, can you implement the Fields property so that Columns(X + 1) = Fields(X)? For compatibility purposes.
