-
Jul 24th, 2022, 10:51 PM
#81
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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.
Last edited by Eduardo-; Jul 25th, 2022 at 08:56 AM.
-
Jul 25th, 2022, 12:06 PM
#82
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:
Maybe Kr00l's library has something similar to the RC6 cRecordset.ValueMatrix property where you can query values in an already opened RS?
-
Jul 25th, 2022, 12:25 PM
#83
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by jpbro
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.
Last edited by Eduardo-; Jul 25th, 2022 at 12:55 PM.
-
Jul 25th, 2022, 12:39 PM
#84
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.
-
Jul 25th, 2022, 12:44 PM
#85
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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):
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
-
Jul 25th, 2022, 12:54 PM
#86
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by jpbro
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.
-
Jul 25th, 2022, 01:01 PM
#87
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Schmidt
There's...
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
-
Jul 25th, 2022, 01:05 PM
#88
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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
-
Jul 25th, 2022, 01:25 PM
#89
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Schmidt
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:
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?
-
Jul 25th, 2022, 01:35 PM
#90
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.
-
Jul 25th, 2022, 01:59 PM
#91
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.
-
Jul 25th, 2022, 02:03 PM
#92
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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.
-
Jul 25th, 2022, 02:08 PM
#93
Re: VB SQLite Library (COM-Wrapper)
OK, thank you, this must work. I'll test the idea with Krool's VBSQLite.
👏👏👏
-
Jul 25th, 2022, 02:09 PM
#94
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by jpbro
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.
Last edited by Eduardo-; Jul 25th, 2022 at 02:14 PM.
-
Jul 25th, 2022, 02:33 PM
#95
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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.
-
Jul 25th, 2022, 03:18 PM
#96
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by jpbro
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).
-
Jul 25th, 2022, 03:20 PM
#97
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Ben321
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.
-
Jul 25th, 2022, 04:20 PM
#98
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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.
-
Jul 25th, 2022, 06:28 PM
#99
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by jpbro
...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.
-
Jul 25th, 2022, 07:17 PM
#100
Re: VB SQLite Library (COM-Wrapper)
I updated the GitHub repo with a new comparison test.
-
Jul 25th, 2022, 08:59 PM
#101
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Schmidt
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.
Originally Posted by Schmidt
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!
-
Jul 26th, 2022, 02:23 PM
#102
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?
-
Jul 26th, 2022, 03:22 PM
#103
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Krool
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.
Originally Posted by Krool
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!
-
Jul 27th, 2022, 05:31 AM
#104
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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.
-
Jul 27th, 2022, 06:14 AM
#105
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.
-
Jul 27th, 2022, 07:28 AM
#106
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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.
-
Jul 27th, 2022, 12:05 PM
#107
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
-
Jul 27th, 2022, 12:30 PM
#108
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).
-
Jul 27th, 2022, 12:46 PM
#109
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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.
-
Jul 27th, 2022, 12:50 PM
#110
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).
-
Jul 27th, 2022, 02:31 PM
#111
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):
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
-
Jul 27th, 2022, 02:57 PM
#112
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.
-
Jul 28th, 2022, 12:41 PM
#113
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..
-
Jul 28th, 2022, 03:05 PM
#114
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Krool
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.
-
Jul 28th, 2022, 04:00 PM
#115
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..
-
Jul 28th, 2022, 04:13 PM
#116
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Schmidt
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.
-
Jul 28th, 2022, 05:22 PM
#117
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Krool
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
-
Jul 28th, 2022, 06:30 PM
#118
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?
-
Jul 28th, 2022, 08:57 PM
#119
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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?
-
Jul 29th, 2022, 01:00 AM
#120
Re: VB SQLite Library (COM-Wrapper)
Originally Posted by Eduardo-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|