Page 5 of 6 FirstFirst ... 23456 LastLast
Results 161 to 200 of 220

Thread: VB SQLite Library (COM-Wrapper)

  1. #161

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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 View Post
    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
    Last edited by Krool; Aug 2nd, 2022 at 01:27 PM.

  2. #162
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    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.

  3. #163

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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..

  4. #164
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    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.

  5. #165

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by wqweto View Post
    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- View Post
    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.
    Last edited by Krool; Aug 7th, 2022 at 03:05 PM.

  6. #166

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by wqweto View Post
    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.

  7. #167
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    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.

  8. #168
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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>

  9. #169

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by wqweto View Post
    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..

  10. #170
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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>

  11. #171

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

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

    EDIT: no effect ...
    Last edited by Krool; Aug 3rd, 2022 at 02:15 PM.

  12. #172

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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..

  13. #173

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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.

  14. #174
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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?

  15. #175

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by wqweto View Post
    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..

  16. #176
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    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>

  17. #177
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    Maybe Eduardo can give results of the latest benchmarks.
    Tested with compiled DLL:

    Name:  SQLite_test3.png
Views: 717
Size:  9.0 KB

    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

  18. #178

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    Tested with compiled DLL:

    Name:  SQLite_test3.png
Views: 717
Size:  9.0 KB

    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

  19. #179
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    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).

  20. #180

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    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)
    Last edited by Krool; Aug 11th, 2022 at 05:19 AM. Reason: removed VBSQLite12_vs14.DL

  21. #181
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

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

  22. #182

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    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.
    Last edited by Krool; Aug 6th, 2022 at 01:30 PM.

  23. #183

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by wqweto View Post
    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.

  24. #184

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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..
    Last edited by Krool; Aug 11th, 2022 at 05:19 AM. Reason: removed VBSQLite12_MT14.DLL

  25. #185
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    It registered and worked, but it is slower.

    Name:  SQLite_test4.png
Views: 810
Size:  9.0 KB

  26. #186
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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>

  27. #187
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by wqweto View Post
    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>
    Name:  SQLite_test5.png
Views: 814
Size:  8.9 KB

    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.

  28. #188

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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.

  29. #189
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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>

  30. #190
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by wqweto View Post
    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)

  31. #191
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    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:

    Code:
    DAO: 		1,22

  32. #192

  33. #193

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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.

  34. #194
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    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.

  35. #195

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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..

  36. #196
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    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.

  37. #197

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    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.
    Last edited by Krool; Aug 10th, 2022 at 03:09 PM.

  38. #198
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    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

  39. #199
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    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?

  40. #200
    Hyperactive Member
    Join Date
    May 2018
    Location
    Russia
    Posts
    316

    Re: VB SQLite Library (COM-Wrapper)

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

Page 5 of 6 FirstFirst ... 23456 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width