Page 3 of 6 FirstFirst 123456 LastLast
Results 81 to 120 of 220

Thread: VB SQLite Library (COM-Wrapper)

  1. #81
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    Ok, I'll make a test project.
    Here it is. I limited it to just 100,000 items so the origin database file is not too big.

    But if you want a file with more records I can upload a bigger database.

    Name:  DB_test.png
Views: 904
Size:  22.8 KB

  2. #82
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: VB SQLite Library (COM-Wrapper)

    I haven't tried Kr00l's wrapper, but I can confirm similar bad performance after converting your demo to RC6 code. I even tried swapping out all the .OpenRecordset calls for .FindFirst, and the performance was even worse. I think the issue that if it takes even 1ms to query and open a recordset, then that will add up to quite a long time over hundreds of thousands of calls. Instead, you will need to emulate the DAO "Seek" method by open the recordset only once, setting an Index column (that is, sort by a column), then perform a binary search against the values in that column until you find a match. This will dramatically lower the time it takes to find all your random hash values.

    I did a quick test of a binary search against the RC6 cRecordset.ValueMatrix method, and without any optimizations I got the time to be "close" to the DAO time:

    Name:  2022-07-25_12-57-26.png
Views: 914
Size:  14.4 KB

    Maybe Kr00l's library has something similar to the RC6 cRecordset.ValueMatrix property where you can query values in an already opened RS?

  3. #83
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by jpbro View Post
    Instead, you will need to emulate the DAO "Seek" method by open the recordset only once, setting an Index column (that is, sort by a column), then perform a binary search against the values in that column
    But it can't be done in memory, it would exhaust the available RAM for tables with millions records if you need to load the data.
    I wonder if the SQLite sqlite3win32.dll has something to allow to do that but directly in the database.

    Or maybe a mix, load in memory just the keys and somewhat get a pointer to where the row is in the database. But that would take too much RAM anyway I think.

    PS: worth to mention: FindFirst/Next is slow as hell.

  4. #84
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: VB SQLite Library (COM-Wrapper)

    If you select only the RowID and Path_Hash, then you should be able to SELECT quote a lot of records I think - I would guess in the high-tens to low-hundreds of millions perhaps? Do you expect to have that many?

    You might be able to speed things up if you can collect a bunch of hashes - you'd have to experiment with collecting tens, hundreds, or thousands of them to see what has the best performance. Once you've collected a bunch you could grab all matching records with "SELECT * FROM Files WHERE Path_Hash IN (<CommaSeparatedListofCollectedHashes>)". You could then loop the list and perform you further processing on the matches. Not sure if collecting hashes before hitting the DB is possible in your scenario or not.

  5. #85
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    But it can't be done in memory, it would exhaust the available RAM for tables with millions records...
    There's a relative simple (well-known) solution for this kind of thing:
    - process the Data in "chunks"
    - the "chunks" here being: "sets of records, limited to e.g. a size of 256 in the SQLite-case"
    - done via applying an "In Clause" in the where-condition (to find matches)

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

    In my Performance-Test, I get about 45% faster results with SQLite (compared to DAO-Seek):
    Name:  SQLiteSeek.png
Views: 935
Size:  66.0 KB

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

    Here is the Form-Code (which needs a DAO, and an RC6-ref in the Project):
    Code:
    Option Explicit
     
    Private Sub Form_Load()
        Dim iDbDAO As Database, iRecDAO As Recordset
        Dim iCon As cConnection, iCommand As cCommand
        Dim C As Long, B() As Byte, iT1
        
        If New_c.FSO.FileExists(App.Path & "\SQLite.db") Then
           New_c.FSO.DeleteFile App.Path & "\SQLite.db"
        End If
        
        CreateSQLiteDB
        
        Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
        Set iRecDAO = iDbDAO.OpenRecordset("Files")
        
        Set iCon = New_c.Connection(App.Path & "\SQLite.db")
        Set iCommand = iCon.CreateCommand("INSERT INTO Files (Path_Hash, FileLen, Date, Image_Data) VALUES (?,?,?,?)")
      
        iT1 = Timer
        iCon.BeginTrans
            Do Until iRecDAO.EOF
                iCommand.SetText 1, iRecDAO(1).Value
                iCommand.SetInt32 2, iRecDAO(2).Value
                iCommand.SetDouble 3, iRecDAO(3).Value
                B = iRecDAO(4).Value: iCommand.SetBlobPtr 4, VarPtr(B(0)), UBound(B) + 1
                
                iCommand.Execute
                iRecDAO.MoveNext
            Loop
        iCon.CommitTrans
        LogText "Time to fill DB: " & Round(Timer - iT1, 2) & vbCrLf
    End Sub
    
    Private Sub CreateSQLiteDB()
        Dim iCon As cConnection
        Set iCon = New_c.Connection(App.Path & "\SQLite.db", DBCreateNewFileDB)
        
        iCon.Execute "CREATE TABLE Files (ID_File INTEGER PRIMARY KEY, Path_Hash TEXT NOT NULL, FileLen INTEGER DEFAULT 0 NOT NULL, Date REAL DEFAULT 0 NOT NULL, Image_Data BLOB NOT NULL)"
        iCon.Execute "CREATE INDEX Path_Hash ON Files (Path_Hash)"
    End Sub
    
    Private Sub LogText(nText As String)
        txtLog.SelText = nText & vbCrLf
        txtLog.Refresh
    End Sub
        
    Private Sub Command2_Click()
        Dim iKeys(100000) As String, iIndexes(100000) As Long
        Dim iCon As cConnection, iDSet As cRecordset
        Dim iDbDAO As Database, iRecDAO As Recordset
        Dim C As Long, ID As Long, PH As String, FL As Long, DT As Date, BD() As Byte
        Dim iT1
     
        Set iCon = New_c.Connection(App.Path & "\SQLite.db")
        Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
     
        ' load keys
        C = 0
        Set iDSet = iCon.OpenRecordset("SELECT Path_Hash FROM Files")
        Do Until iDSet.EOF
            iKeys(C) = iDSet(0).Value
            If Len(iKeys(C)) <> 32 Then Stop
            C = C + 1
            iDSet.MoveNext
        Loop
        
        ' set random indexes
        For C = 0 To UBound(iIndexes)
            iIndexes(C) = Rnd * 100000
        Next
     
        ' seach for existent keys
        iT1 = Timer
        Dim HDic As cSortedDictionary, HLst() As String, i As Long
        Set HDic = New_c.SortedDictionary(TextCompare, False)
        For C = 0 To UBound(iIndexes)
            If Not HDic.Exists(iKeys(iIndexes(C))) Then HDic.Add iKeys(iIndexes(C))
            If HDic.Count = 256 Then '<- only when a certain "chunksize-limit" is reached, we talk with the DB
               ReDim HLst(0 To HDic.Count - 1) 'let's build a List for an SQL In (...) comparison
               For i = 0 To HDic.Count - 1: HLst(i) = HDic.KeyByIndex(i): Next
               Set iDSet = iCon.OpenRecordset("SELECT * FROM Files WHERE Path_Hash In ('" & Join(HLst, "','") & "')")
               
               Do Until iDSet.EOF 'process all cache-hits via Rs-Loop
                  ID = iDSet!ID_File.Value
                  PH = iDSet!Path_Hash.Value
                  FL = iDSet!FileLen.Value
                  DT = iDSet!Date.Value
                  BD = iDSet!Image_Data.Value
                  
                  HDic.Remove PH
                  iDSet.MoveNext
               Loop
               For i = 0 To HDic.Count - 1 'process all cache-misses (left over in HDic)
                   Stop
               Next
               If HDic.Count Then HDic.RemoveAll
            End If
        Next
        LogText "SQLite: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2)
        
        
        iT1 = Timer
        Set iRecDAO = iDbDAO.OpenRecordset("Files")
        iRecDAO.Index = "Path_Hash"
        For C = 0 To UBound(iIndexes)
            iRecDAO.Seek "=", iKeys(iIndexes(C))
            If Not iRecDAO.NoMatch Then
                ID = iRecDAO!ID_File.Value
                PH = iRecDAO!Path_Hash.Value
                FL = iRecDAO!FileLen.Value
                DT = iRecDAO!Date.Value
                BD = iRecDAO!Image_Data.Value
            Else
                Stop
            End If
        Next
        LogText "DAO: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
    End Sub
    The above code has about "10 lines overhead" (for the "chunking"), compared with the DAO-solution.

    HTH

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

    Olaf

  6. #86
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by jpbro View Post
    If you select only the RowID and Path_Hash, then you should be able to SELECT quote a lot of records I think - I would guess in the high-tens to low-hundreds of millions perhaps? Do you expect to have that many?
    Humm, 32 characters each hash = 64 bytes + 4 bytes RowID = 68 Bytes pero row.
    Let's limit it to 1 GB RAM (I don't like it to take so much because I already have other big things in memory, but anyway): 14 millions. But there will be some overhead surely, so let's say 10 millions.

    I didn't think of a limit, it seems to be a good number already, but I don't like the idea of limiting in this way.
    All I'm doing is to get rid of limits (other than the hardware that is installed).

    With DAO I already can handle 3 millions records (there I hit the 2 GB database file size limit)
    I would be only multiplying by 3 or 4 the capacity.

    IDK if users will need to handle so much data, but I would like to have this "technology" ready for maybe other projects or other uses.

  7. #87
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    There's...

    Name:  SQLiteSeek.png
Views: 935
Size:  66.0 KB
    I'll see to test it.
    But I wonder why DAO is so slow in your machine.
    Compare to my numbers:

    Name:  DB_test.png
Views: 910
Size:  22.8 KB

    And they are all around 1.45, when mines are 0.34/0.16/0.12

  8. #88
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    I'll see to test it.
    But I wonder why DAO is so slow in your machine.
    Compare to my numbers:

    And they are all around 1.45, when mines are 0.34/0.16/0.12
    That's because your test-setup was not really "fair"...

    DAO (in Seek-Mode) supports "lazy Field-Value retrieval".

    But the point of a cache-table is, that you will need to retrieve *all* the Field-Values
    (in case of a cache-hit).

    My little example does just that (in both cases of course).

    Olaf

  9. #89
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    That's because your test-setup was not really "fair"...

    DAO (in Seek-Mode) supports "lazy Field-Value retrieval".

    But the point of a cache-table is, that you will need to retrieve *all* the Field-Values
    (in case of a cache-hit).

    My little example does just that (in both cases of course).

    Olaf
    OK, here is the new code retrieving all the fields:

    Code:
    Private Sub Command2_Click()
        Dim iKeys(100000) As String
        Dim iDSet As SQLiteDataSet
        Dim iCon As SQLiteConnection
        Dim c As Long
        Dim iDbDAO As Database
        Dim iRecDAO As Recordset
        Dim iT1
        Dim iIndexes(100000) As Long
        Dim iPathHash As String
        Dim iFileLen As String
        Dim iFileDate As String
        Dim iData() As Byte
        Const cLenBData As Long = 508
        
        Set iCon = New SQLiteConnection
        iCon.OpenDB App.Path & "\SQLite.db", SQLiteReadWrite
        Set iDSet = iCon.OpenDataSet("SELECT * FROM Files")
        
        ' load keys
        c = 0
        iDSet.MoveFirst
        Do Until iDSet.EOF
            iKeys(c) = iDSet!Path_Hash
            c = c + 1
            iDSet.MoveNext
        Loop
        
        ' set random indexes
        For c = 0 To UBound(iIndexes)
            iIndexes(c) = Rnd * 100000
        Next
        
        
        ' seach for existent keys
        iT1 = Timer
        For c = 0 To UBound(iIndexes)
            Set iDSet = iCon.OpenDataSet("SELECT * FROM Files WHERE (Path_Hash = '" & iKeys(iIndexes(c)) & "')")
            If iDSet.RecordCount > 0 Then
                iDSet.MoveFirst
                iPathHash = iDSet!Path_Hash
                iFileLen = iDSet!FileLen
                iFileDate = iDSet!Date
                iData = iDSet!Image_Data
            Else
                Stop
            End If
        Next
        LogText "SQLite: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2)
        
        Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
        Set iRecDAO = iDbDAO.OpenRecordset("Files")
        iRecDAO.Index = "Path_Hash"
        
        iT1 = Timer
        For c = 0 To UBound(iIndexes)
            iRecDAO.Seek "=", iKeys(iIndexes(c))
            If Not iRecDAO.NoMatch Then
                iPathHash = iRecDAO!Path_Hash
                iFileLen = iRecDAO!FileLen
                iFileDate = iRecDAO!Date
                iData = iRecDAO.Fields("Image_Data").GetChunk(0, cLenBData)
            Else
                Stop
            End If
        Next
        LogText "DAO: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
        
        
        ' added an "X" at the end of the keys
        iT1 = Timer
        For c = 0 To UBound(iIndexes)
            Set iDSet = iCon.OpenDataSet("SELECT * FROM Files WHERE (Path_Hash = '" & iKeys(iIndexes(c)) & "X" & "')")
            If iDSet.RecordCount > 0 Then
                Stop
            End If
        Next
        LogText "SQLite: time searching 100,000 keys that differ with existing keys at the end: " & Round(Timer - iT1, 2)
        
        Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
        Set iRecDAO = iDbDAO.OpenRecordset("Files")
        iRecDAO.Index = "Path_Hash"
        
        iT1 = Timer
        For c = 0 To UBound(iIndexes)
            iRecDAO.Seek "=", iKeys(iIndexes(c)) & "X"
            If Not iRecDAO.NoMatch Then
                Stop
            End If
        Next
        LogText "DAO: time searching 100,000 keys that differ with existing keys at the end: " & Round(Timer - iT1, 2) & vbCrLf
        
        
        ' added an "X" at the beginning of the keys
        iT1 = Timer
        For c = 0 To UBound(iIndexes)
            Set iDSet = iCon.OpenDataSet("SELECT * FROM Files WHERE (Path_Hash = '" & "X" & iKeys(iIndexes(c)) & "')")
            If iDSet.RecordCount > 0 Then
                Stop
            End If
        Next
        LogText "SQLite: time searching 100,000 keys that do not exist from the beginning: " & Round(Timer - iT1, 2)
        
        Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
        Set iRecDAO = iDbDAO.OpenRecordset("Files")
        iRecDAO.Index = "Path_Hash"
        
        iT1 = Timer
        For c = 0 To UBound(iIndexes)
            iRecDAO.Seek "=", "X" & iKeys(iIndexes(c))
            If Not iRecDAO.NoMatch Then
                Stop
            End If
        Next
        LogText "DAO: time searching 100,000 keys that do not exist from the beginning: " & Round(Timer - iT1, 2) & vbCrLf
        
        LogText "Finished"
    End Sub
    The project is also updated on GitHub.

    But I don't get your numbers. And BTW it is impossible to assign anything in case that the keys are not found. This are the new numbers:

    Name:  DB_test1.png
Views: 842
Size:  21.2 KB

    I also guess that your machine is much faster than mine, because you get 0.76 seconds in creating the database while I get 1.72. So, where is the difference?

  10. #90
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Forget it, I see that you perform the first test three times, not the other two where the key is not found.

    PS: I'll set up RC6 to test your example.

  11. #91
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Ahh, I now get the idea.
    You retrieve several rows each time. So the main program needs to wait until the chunk limit is reached to be able to get the data for all at once.
    That will complicate things in my program, because I not always need to get a lot of records, but I could adapt it to the idea.

  12. #92
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    Humm, 32 characters each hash = 64 bytes + 4 bytes RowID = 68 Bytes pero row.
    Let's limit it to 1 GB RAM (I don't like it to take so much because I already have other big things in memory, but anyway): 14 millions. But there will be some overhead surely, so let's say 10 millions.
    Yeah looks like I was off by an order of a magnitude on my math. I just tried and could get somewhere between 5 and 10 million records. AFAIK SQLite stores strings as UTF-8, so the 32-characters should = 32 Bytes, not 64. Another option would be to store the File_Hash as a binary blob instead of text - that would mean 16-bytes per hash instead of 32. Still, you would be limited to what you could pull into memory.

    Looks like the "chunked" SELEC..IN approach would be ideal if it would work with your app.

  13. #93
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    OK, thank you, this must work. I'll test the idea with Krool's VBSQLite.

    👏👏👏

  14. #94
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by jpbro View Post
    AFAIK SQLite stores strings as UTF-8, so the 32-characters should = 32 Bytes, not 64.
    Yes, but that's on disk, not as you would have it in VB's memory.

    Edit: and BTW, that also explains why the SQLite database is a bit smaller than the Access one with the same data.

  15. #95
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    Yes, but that's on disk, not as you would have it in VB's memory.

    Edit: and BTW, that also explains why the SQLite database is a bit smaller than the Access one with the same data.
    Hmm, I'm not sure about that - perhaps that's implementation dependent? I admit that I haven't investigated Kr00l's or Olaf's code closely, but I would have thought that any implementation would point to the data in SQLite managed memory and then covert to VB6 data types as needed as opposed to making a full copy of everything to VB6 data types in memory. In any case, BLOBs would be binary data and would halve (or more) your memory use, but clearly the chunked approach is still the way to go.

  16. #96
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by jpbro View Post
    Hmm, I'm not sure about that - perhaps that's implementation dependent? I admit that I haven't investigated Kr00l's or Olaf's code closely, but I would have thought that any implementation would point to the data in SQLite managed memory and then covert to VB6 data types as needed as opposed to making a full copy of everything to VB6 data types in memory. In any case, BLOBs would be binary data and would halve (or more) your memory use, but clearly the chunked approach is still the way to go.
    Strings in VB6 are UTF-16, not UTF-8. Of course a conversion takes place at some point (in the components).

  17. #97
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Ben321 View Post
    It says "This Ax-DLL project is intended to get a VB-friendly COM-Wrapper for the SQLite library". Do I need to download both the StdCall SQLite library and the COM wrapper? Does the COM wrapper have StdCall SQLite library as a dependency?

    Or in the COM Wrapper does it have all the SQLite stuff built-in?
    From the first post of this thread:

    sqlite3win32 is only needed for compiling the Ax-DLL. The compiled Ax-DLL doesn't have any dependency, because sqlite3win32 was then compiled into it.

  18. #98
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    Strings in VB6 are UTF-16, not UTF-8. Of course a conversion takes place at some point (in the components).
    I know, but there's a difference between holding all text data in memory as UTF-16 vs. holding it all in memory as UTF-8, and converting to UTF-16 as needed (for example, when getting a text value via a .ValueMatrix property or similar). Again, I don't know how either Kr00l or Olaf have implemented it, but if it is possible to keep everything in SQLite's managed memory as UTF-8, then that would be a sensible choice to save memory (although possibly at the expense of performance from repeated UTF-8->UTF-16 conversions on multiple accesses of the same data).

    I guess what I'm saying is, there are potentially two ways to handle the data in memory pulled from an SQLite database - slurp it all into VB6 datatypes/structures/memory which would (of course) use UTF-16 for Text/Strings, or maintain pointers to SQLite managed memory where TEXT fields are UTF-8 and then perform conversions to UTF-16 as needed when data is actually needed/accessed.

    Anyway, I'm purely speculating at this point, Kr00l and/or Olaf can clarify if the care to. If I have the time, I will take a closer look and find out myself.

  19. #99
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by jpbro View Post
    ...there's a difference between holding all text data in memory as UTF-16 -
    vs. holding it all in memory as UTF-8...
    Yep, an RC5 or RC6 cRecordset (after the set was created as an Object-instance - via Cnn.OpenRecordset or Cnn.GetRs),
    will contain "a 2D copy of all (Table-)Values which represent this set" ... so there's no "lazy, delayed Field-retrieval" here.
    The Rs will contain and hand out all the data, even when its "Parent-DB-Connection" gets closed (is set to Nothing) after the "Rs-instancing SQL-Select".

    And in case of retrieved Text-Fields, these copies are "still non-converted UTF8-Blobs" (instead of BStrings) -
    in the Rs-instance's internal Structures.
    Only at the point of accessing Rs.Fields(...).Value or Rs.ValueMatrix(RowIdx, ColIdx) Properties,
    will these UTF8-Field-Blobs be converted to BSTRs "on the fly".

    This way (if one keeps the Rs alive as the "main-data-container"),
    less (String-)memory is wasted when large sets are retrieved (and held).

    Olaf
    Last edited by Schmidt; Jul 25th, 2022 at 07:03 PM.

  20. #100
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    I updated the GitHub repo with a new comparison test.

    Name:  DB_test2.png
Views: 685
Size:  9.4 KB

  21. #101
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    Yep, an RC5 or RC6 cRecordset (after the set was created as an Object-instance - via Cnn.OpenRecordset or Cnn.GetRs),
    will contain "a 2D copy of all (Table-)Values which represent this set" ... so there's no "lazy, delayed Field-retrieval" here.
    The Rs will contain and hand out all the data, even when its "Parent-DB-Connection" gets closed (is set to Nothing) after the "Rs-instancing SQL-Select".
    Ahh, yes, I didn't even consider disconnected recordsets, so it makes sense to have a full copy of the data in the cRecordset object instance.

    Quote Originally Posted by Schmidt View Post
    And in case of retrieved Text-Fields, these copies are "still non-converted UTF8-Blobs" (instead of BStrings) -
    in the Rs-instance's internal Structures.
    Only at the point of accessing Rs.Fields(...).Value or Rs.ValueMatrix(RowIdx, ColIdx) Properties,
    will these UTF8-Field-Blobs be converted to BSTRs "on the fly".
    Good to know, thanks for clearing that up!

  22. #102

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Eduardo,
    I think such a "SQLiteCursor" class suggested by Schmidt is a viable solution to memory problem. (Forward-only cursor, this is the only cursor type Sqlite allows)
    When time allows I will definitely add that soon.

    Btw, just a side question. You can enlarge the 2GB limit in VB6 to 4Gb (3.5 GB in practice) if you compile w/ LargeAddressAware. Maybe that would solve it for now?

  23. #103
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    Eduardo,
    I think such a "SQLiteCursor" class suggested by Schmidt is a viable solution to memory problem. (Forward-only cursor, this is the only cursor type Sqlite allows)
    When time allows I will definitely add that soon.
    It would be nice to test it.

    Quote Originally Posted by Krool View Post
    Btw, just a side question. You can enlarge the 2GB limit in VB6 to 4Gb (3.5 GB in practice) if you compile w/ LargeAddressAware. Maybe that would solve it for now?
    Yes, but that would be only "a bit".

    Thank you!

  24. #104

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    It would be nice to test it.
    I'm on it. The new SQLiteCursor will be createable from the SQLiteConnection or from an SQLiteCommand object.
    So, the cursor class is focused only on iterating the values. It will support only "MoveFirst" and "MoveNext". The MoveFirst will simply reset everything.
    The RecordCount of that cursor is on the first run equal to the position. But it will remain the same after a possible "second" round. (MoveFirst)

    It's not possible to determine RecordCount upfront. So that's my solution.
    I need to make more tests.. Will provide it soon.

  25. #105
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    OK Krool. Anyway I don't think I'm understanding very much if it is something intended to find keys faster or something else (I never used cursors before so I guess I should study about them).

    I have been reading a bit about the SQLite interface of the native API, and could not come to a conclusion because I would have to study and understand a lot more, but I have "a hunch" that perhaps it must be possible to implement a Seek emulation. I mean to get records based on indexes real quick.
    The idea is to make a replacement of DAO and ADO recordsets, keeping backward compatibility (to be used directly with existent code, "plug and play"). And also with (at least) the same speed, speed must be the king.
    Not just Seek, but also other normal DB operations. With Edit, Update, NoMatch, everything (those ones are already at hand).
    It seems to be so close... But perhaps that's not your goal.
    I was considering doing it myself at some point (not now), but understanding how to deal with the SQLite API does not seem so easy.

  26. #106

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    OK Krool. Anyway I don't think I'm understanding very much if it is something intended to find keys faster or something else (I never used cursors before so I guess I should study about them).

    I have been reading a bit about the SQLite interface of the native API, and could not come to a conclusion because I would have to study and understand a lot more, but I have "a hunch" that perhaps it must be possible to implement a Seek emulation. I mean to get records based on indexes real quick.
    The idea is to make a replacement of DAO and ADO recordsets, keeping backward compatibility (to be used directly with existent code, "plug and play"). And also with (at least) the same speed, speed must be the king.
    Not just Seek, but also other normal DB operations. With Edit, Update, NoMatch, everything (those ones are already at hand).
    It seems to be so close... But perhaps that's not your goal.
    I was considering doing it myself at some point (not now), but understanding how to deal with the SQLite API does not seem so easy.
    Well, the SQLiteCursor will just simply allow you to query a full 100GB (or watever) table and iterate trough.

  27. #107

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Update released.

    Ax-DLL version 1.2 in which the SQLiteCursor class is added.

    Example:

    Code:
    Dim Cursor As SQLiteCursor
    Set Cursor = DBConnection.CreateCursor("SELECT ID, szText FROM test_table")
    
    ' .RecordCount can now be only 1 or 0.
    
    Do Until Cursor.EOF
        
        Debug.Print Cursor!szText, Cursor.Value(1) ' Either via .Columns or directly
        
        Cursor.MoveNext
    Loop
    
    MsgBox Cursor.RecordCount & " record(s)" ' Now it's meaningful
    
    Cursor.MoveFirst ' loop again through if you wish

  28. #108
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    And what is the difference between a SQLiteCursor and a SQLiteDataSet?
    I ask because they roughly seem to do and work more or less the same to me, at least regarding getting data and enumerating records (I must be overlooking something).

  29. #109

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    And what is the difference between a SQLiteCursor and a SQLiteDataSet?
    I ask because they roughly seem to do and work more or less the same to me, at least regarding getting data and enumerating records (I must be overlooking something).
    The DataSet is fetching all data in memory whereas the Cursor is only fetching on-demand on the current record.
    So, the DataSet is memory limited whereas the Cursor is simply not.

  30. #110
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Ah, OK, then it must be faster for getting if specific records (keys) exist (in case they support the WHERE clause).

  31. #111
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    The new Cursor-Class is not (yet) optimal, because it does not have "Command-Parameter-integration" (as I suggested before).

    Though it brings down the timing (with "DAO-like code-efficiency") -
    but so does the DataSet (when derived from a CommandObject).

    As it is currently, some Overhead is caused, due to the necessary (additional) Object-Instantiation in each iiteration
    (which, as said - could be avoided via a Cursor-Class that "integrates" the Command-functionality as well).

    Here is my current (native-compiled) results (all Field-access now done via Indexes to speed things up a little):
    Name:  SQLiteSeek2.png
Views: 615
Size:  49.7 KB

    Here the complete Test-Code again (needs references to RC6 and to VBSQLite 1.2):
    Code:
    Option Explicit
     
    Private Sub Form_Load()
        Dim iDbDAO As Database, iRecDAO As Recordset
        Dim iCon As cConnection, iCommand As cCommand
        Dim iT1
        
        If New_c.FSO.FileExists(App.Path & "\SQLite.db") Then
           New_c.FSO.DeleteFile App.Path & "\SQLite.db"
        End If
        
        CreateSQLiteDB
        
        Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
        Set iRecDAO = iDbDAO.OpenRecordset("Files")
        
        Set iCon = New_c.Connection(App.Path & "\SQLite.db")
        Set iCommand = iCon.CreateCommand("INSERT INTO Files (Path_Hash, FileLen, Date, Image_Data) VALUES (?,?,?,?)")
      
        iT1 = Timer
        iCon.BeginTrans
            Do Until iRecDAO.EOF
                iCommand.SetText 1, iRecDAO(1).Value
                iCommand.SetInt32 2, iRecDAO(2).Value
                iCommand.SetDouble 3, iRecDAO(3).Value
                iCommand.SetBlob 4, iRecDAO(4).Value
     
                iCommand.Execute
                iRecDAO.MoveNext
            Loop
        iCon.CommitTrans
        LogText "Time to fill DB: " & Round(Timer - iT1, 2) & vbCrLf
    End Sub
    
    Private Sub CreateSQLiteDB()
        Dim iCon As cConnection
        Set iCon = New_c.Connection(App.Path & "\SQLite.db", DBCreateNewFileDB)
        
        iCon.Execute "CREATE TABLE Files (ID_File INTEGER PRIMARY KEY, Path_Hash TEXT NOT NULL, FileLen INTEGER DEFAULT 0 NOT NULL, Date REAL DEFAULT 0 NOT NULL, Image_Data BLOB NOT NULL)"
        iCon.Execute "CREATE INDEX Path_Hash ON Files (Path_Hash)"
    End Sub
    
    Private Sub LogText(nText As String)
        txtLog.SelText = nText & vbCrLf
        txtLog.Refresh
    End Sub
        
    Private Sub Command2_Click()
        Dim iKeys(100000) As String, iIndexes(100000) As Long
        Dim iCon As cConnection, iDSet As cRecordset
        Dim iDbDAO As Database, iRecDAO As Recordset
        Dim iSQLite As New SQLiteConnection, iCmd As SQLiteCommand, iCur As SQLiteCursor, iDS As SQLiteDataSet
        Dim C As Long, ID As Long, PH As String, FL As Long, DT As Date, BD() As Byte
        Dim iT1
     
        Set iCon = New_c.Connection(App.Path & "\SQLite.db")
        Set iDbDAO = DBEngine.OpenDatabase(App.Path & "\OrigDB.mdb")
            iSQLite.OpenDB App.Path & "\SQLite.db", SQLiteReadWrite
    
        
        ' load keys
        C = 0
        Set iDSet = iCon.OpenRecordset("SELECT Path_Hash FROM Files")
        Do Until iDSet.EOF
            iKeys(C) = iDSet(0).Value: C = C + 1
            iDSet.MoveNext
        Loop
        
        ' set random indexes
        For C = 0 To UBound(iIndexes)
            iIndexes(C) = Rnd * 100000
        Next
     
        ' seach for existent keys
        iT1 = Timer
        Dim HDic As cSortedDictionary, HLst() As String, i As Long
        Set HDic = New_c.SortedDictionary(TextCompare, False)
        For C = 0 To UBound(iIndexes)
            If Not HDic.Exists(iKeys(iIndexes(C))) Then HDic.Add iKeys(iIndexes(C))
            If HDic.Count = 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 = iCon.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 = iCon.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: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
        
        
        iT1 = Timer
        Set iRecDAO = iDbDAO.OpenRecordset("Files")
        iRecDAO.Index = "Path_Hash"
        For C = 0 To UBound(iIndexes)
            iRecDAO.Seek "=", iKeys(iIndexes(C))
            If Not iRecDAO.NoMatch Then
                ID = iRecDAO(0).Value
                PH = iRecDAO(1).Value
                FL = iRecDAO(2).Value
                DT = iRecDAO(3).Value
                BD = iRecDAO(4).Value
            Else
                Stop
            End If
        Next
        LogText "DAO: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
        
            
        iT1 = Timer 'an "integrated CursorObject" (that allows Param-Settings) would be faster
        Set iCmd = iSQLite.CreateCommand("Select * From Files Where Path_Hash=?")
        For C = 0 To UBound(iIndexes)
            iCmd.SetParameterValue 1, iKeys(iIndexes(C))
            Set iCur = iCmd.CreateCursor
            
            If iCur.RecordCount Then
                ID = iCur(1).Value
                PH = iCur(2).Value
                FL = iCur(3).Value
                DT = iCur(4).Value
                BD = iCur(5).Value
            Else
                Stop
            End If
        Next
        LogText "Cursor: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
    
    
        iT1 = Timer 'as it is, the same principle (with a DataSet) has comparable performance
        Set iCmd = iSQLite.CreateCommand("Select * From Files Where Path_Hash=?")
        For C = 0 To UBound(iIndexes)
            iCmd.SetParameterValue 1, iKeys(iIndexes(C))
            Set iDS = iCmd.OpenDataSet
            
            If iDS.RecordCount Then
                ID = iDS(1).Value
                PH = iDS(2).Value
                FL = iDS(3).Value
                DT = iDS(4).Value
                BD = iDS(5).Value
            Else
                Stop
            End If
        Next
        LogText "DataSet: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
    
    End Sub
    Olaf

  32. #112

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    I tried to combine the Cursor and keep all binding stuff from Command inside. But that's not working..
    Once a stmt is busy binding can't be applied. And the cursor must be busy (at least step'ed once) all the time.
    So nup. Thanks anyhow Olaf.

  33. #113

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    The only way I can imagine is to reset the stmt and move to first on each call to SetParameterValue. (if implemented in SQLiteCursor..)
    Something the line of:
    Code:
    Public Sub SetParameterValue(ByVal Index As Long, ByRef Value As Variant)
    
    If PropLastResult <> 0 Then
        If stub_sqlite3_reset(PropHandle) = SQLITE_OK Then
            PropRecordCount(0) = 0
            PropPosition = 0
            PropLastResult = 0
        Else
            Err.Raise Number:=vbObjectError + stub_sqlite3_errcode(stub_sqlite3_db_handle(PropHandle)), Description:=SQLiteUTF8PtrToStr(stub_sqlite3_errmsg(stub_sqlite3_db_handle(PropHandle)))
        End If
    End If
    
    [...]
    
    If PropLastResult = 0 Then
        PropLastResult = stub_sqlite3_step(PropHandle)
        If PropLastResult = SQLITE_ROW Then
            PropRecordCount(0) = 1
            PropPosition = 1
        End If
    End If
    
    [...]
    
    End Sub
    But if that is a good solution. I don't know..

  34. #114
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    The only way I can imagine is to reset the stmt and move to first on each call to SetParameterValue.
    ...
    But if that is a good solution. I don't know..
    It's intuitive and "as one would expect", I'd say...
    ...when someone changes the "underlying conditions of a Set-representation" (by changing parameters),
    the Cursor will (unsurprisingly) be reset to "start from the beginning".

    Have just implemented it this way in the new RC6.cCursor-Class
    (which is admittedly "nice to have", not only for stuff like in Eduardos scenario).

    The (unchunked) performance, using this new Class (for "single-Record-Exists-Queries"),
    is about factor 3 slower though, compared with DAO-Seek ...
    (4.35sec for cCursor, compared to about 1.45sec for DAO-Seek, using the known test as above)

    And only 0.35sec of the 4.35sec total was related to Field-retrieval.
    A plain (repeated) execution of only the sequence:
    - sqlite-parameter-setting (involving an implicite sqlite-reset-call)
    - followed by an sqlite-step-call
    needed about 4sec (for 100000 "exist-checks" without Field-retrieval)

    It's not *that* bad, but the overhead inherent in the lib itself (in either the sqlite-reset or sqlite-step-calls)
    is apparently higher compared with DAO (at least in such "parametrized single-record-searches".

    Edit: Just found out, that one can speed up the SQLite-behavour in such cursor-searches by about factor 12,
    when enclosing the whole loop in a transaction (even though the whole thing is a read-operation).

    The new timing (including Field-Value-retrieval) is now 0.35sec (about factor 4 faster than DAO-Seek)
    ... so, well - guess my trust in the speed of the SQLite-engine is restored again...

    Olaf
    Last edited by Schmidt; Jul 28th, 2022 at 03:33 PM.

  35. #115

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Thanks Olaf.

    Sometimes I just need a confirmation and yes it sounds logical to move to the first row when using SetParameterValue or ClearParameters.

    I now included the binding methods/properties in the SQLiteCursor class.

    To note is that his is meaningful only when created from a SQLiteConnection class.
    A SQLiteCursor created from an SQLiteCommand class will loose the parameter indexes as the query got expanded.

    If you like you may test the new capability in your test.

    EDIT: the typelib version for SQLite12 jumped from 1.0 to 1.1. But that does no harm..

  36. #116
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    The new timing (including Field-Value-retrieval) is now 0.35sec (about factor 4 faster than DAO-Seek)
    ... so, well - guess my trust in the speed of the SQLite-engine is restored again...

    Olaf
    Does that mean that I'll have to roll back my code to the unchunked-mode?

    BTW: it is already working, stored 10 million records in a 6.5 GB db.

  37. #117
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    If you like you may test the new capability in your test.
    Sure, ... and the result was 1.75sec (DAO-Seek was 1.39, RC6-cCursor was 0.39)...

    So, it's now playing in the same "ballpark" as DAO-Seek...
    The reason why it's still about factor 4.5 slower than RC6.cCursor is,
    that your Value-retrieval has to go through a lot of extra-COM-instances and
    Method-Calls (in each iteration)... whereas the RC6-cCursor does not have a
    "Fields-Collection" (as in the cRecordset) - instead I directly go to the sqlite-apis for that,
    using only a zerobased index without Field-access-by-name functionality (it's a "low-level-class").

    Here the relevant test-snippet for your upgraded SQLiteCursor-Class:
    Code:
        iT1 = Timer  
        iSQLite.Execute "Begin Transaction"
            Set iCur = iSQLite.CreateCursor("Select * From Files Where Path_Hash=?")
            
            For C = 0 To UBound(iIndexes)
                iCur.SetParameterValue 1, iKeys(iIndexes(C))
         
                If iCur.RecordCount Then
        '            ID = iCur(1).Value
    '                PH = iCur(2).Value
                    FL = iCur(3).Value
                    DT = iCur(4).Value
                    BD = iCur(5).Value
                Else
                    Stop
                End If
            Next
        iSQLite.Execute "End Transaction"
        LogText "SQLiteCursor: time searching 100,000 randoms records that are found: " & Round(Timer - iT1, 2) & vbCrLf
    Olaf

  38. #118
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Can this transaction for queries be done at the same time the transaction for new record additions is taking place?

  39. #119
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    Can this transaction for queries be done at the same time the transaction for new record additions is taking place?
    I mean, I need to search for records and if the records do not exist, then to add them. So queries and additions are happening at the same time. Can I have transactions for both operations (queries / new records) at the same time?

  40. #120
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    I mean, I need to search for records and if the records do not exist, then to add them. So queries and additions are happening at the same time. Can I have transactions for both operations (queries / new records) at the same time?
    Just a single enclosing Transaction for both operations is enough:

    Code:
    Private Sub Form_Load()
      Dim Cnn As New SQLiteConnection, Cmd As SQLiteCommand, Cur As SQLiteCursor, V
          Cnn.OpenDB ":memory:"
          'create a table with an index on "Hash", and pre-insert two records
          Cnn.Execute "Create Table T(ID Integer Primary Key, Hash Text Unique Not Null)"
          Cnn.Execute "Insert Into T(Hash) Values('A')"
          Cnn.Execute "Insert Into T(Hash) Values('B')"
     
      Set Cmd = Cnn.CreateCommand("Insert Into T(Hash) Values(?)")
      Set Cur = Cnn.CreateCursor("Select * From T Where Hash=?")
      
      Cnn.Execute "Begin"
          For Each V In Array("A", "B", "C", "D", "C") '<- we try to add "C" twice here
              Cur.SetParameterValue 1, V
              If Cur.RecordCount Then
                 Debug.Print "Record is existing: ", V, Cur(1)
              Else
                 Cmd.SetParameterValue 1, V: Cmd.Execute
                 Debug.Print "Record was missing: ", V, "but is now inserted"
              End If
          Next
      Cnn.Execute "Commit"
    
      'final check (outside the transaction, to see what really made it into Table "T")
      Debug.Print Cnn.OpenDataSet("Select * From T").RecordCount, "<- should be 4"
    End Sub
    HTH

    Olaf

Page 3 of 6 FirstFirst 123456 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