VB SQLite Library (COM-Wrapper)

Quote:

---

Originally Posted by Krool

xiaoyao, your texts are so awful that I am not able to understand these. So I can't respond to you, sorry!

---

I want to load the database without a hard disk file, such as downloading a byte array or resource file from the Internet.
Maybe this is too difficult. The most appropriate approach is estimated to be to HOOK the file read and write function of this process, so that you can simulate a virtual file pointer and jump to the memory address.

Use this method to open an abc.doc file with WORD. In fact, this file is 0 bytes, or use code to generate an empty file with the same size as a real DOC file.
The actual content is read from memory.

According to me this is not possible with the current SQLite engine.
You can load a disk file to memory, you can safe a memory db to a file, but there are no from and to memory

Update released.

The sqlite3 c source was upgraded from version 3.34.1 (2021-01-20) to 3.37.2 (2022-01-06).

The compilation fails, and there is a lot of code. It is difficult and takes a lot of time to successfully apply.

Could you give it a try to build a lightweight ADO recordset to encapsulate SQLite API? It only needs 12 interface processes.
like this:
RC5 Sqlite Like Adodb.Connection/Adodb.RecordSet(WithOut Reg Com Dll)-VBForums
https://www.vbforums.com/showthread....t-Reg-Com-Dll)


Sometimes, some lightweight versions are needed to implement the simplest query, reading and modification.
Such as reading data
A=rs ("field 1") value
B=rs ("field 2") value
Modify field value
RS ("field 1") = "A1"
RS ("field 2") = "TTT"

-------------
Self writing class connection and recordset, instead of adodb For objects such as recordset, only 12 interfaces (or methods) need to be implemented for normal mode calls.
Four interfaces of connection: createnewdb, opendb, execute, openrecordset
8 interfaces of recordset: openrecordset, get item (let item), addnew, updatebatch, RecordCount, EOF, MoveNext

Code:

---

Sub Test_Like_Adodb_RecordSet()
ShowErr = True
dBFile = App.Path & "\Sqlite_Test3.db"
 Dim Cnn As New Connection
 MsgBox Cnn.cConnectionA.Version
 If Dir(dBFile) = "" Then 'Create New Sqlite DB
    Cnn.CreateNewDB dBFile
    Else
    Cnn.OpenDB (dBFile)
 End If
 
Dim RS As Recordset
On Error Resume Next
 
Set RS = Cnn.OpenRecordset("select * FROM UserTable")
If ErrNumber <> 0 Then
    Err.Clear
    Set RS = Nothing
    'New Table
  Cnn.Execute "CREATE TABLE UserTable (UserName TEXT PRIMARY KEY, ID INTEGER, Info TEXT)"
  'Set RS = New Recordset
  'RS.OpenRecordset "select * FROM UserTable", Cnn
 
  Set RS = Cnn.OpenRecordset("select * FROM UserTable")
 
  RS.AddNew
  RS("UserName") = "name1"
  RS("ID") = 100
  RS("Info") = "A"
 
  RS.AddNew
  RS("UserName") = "name2"
  RS("ID") = 200
  RS("Info") = "B"
 
  RS.UpdateBatch
 
  RS.AddNew
  RS("UserName") = "name2"
  RS("ID") = 201
  RS("Info") = "Bb"
 
 
  If Not RS.UpdateBatch Then
    MsgBox "key value err：" & ErrDescription
  End If
 
  'Set RS = Nothing
    Set RS = New Recordset
    RS.OpenRecordset "select * FROM UserTable", Cnn
End If
'Read Rows ,Data
MsgBox "RecordCount=" & RS.RecordCount & ", Field:UserName=" & RS("UserName") & ", Field:ID=" & RS("ID")

'Edit RecordSet
Set RS = New Recordset
RS.OpenRecordset "select * FROM UserTable", Cnn
RS("ID") = RS("ID") + 1
RS.UpdateBatch

'Read Rows ,Data
Set RS = New Recordset
RS.OpenRecordset "select * FROM UserTable", Cnn
MsgBox "After Edit,RecordCount=" & RS.RecordCount & ", Field:UserName=" & RS("UserName") & ", Field:ID=" & RS("ID")

RS.Delete
RS.Delete

If RS.Delete = False Then
    'MsgBox "Delete err:" & ErrDescription
End If
Set RS = Nothing
Set Cnn = Nothing
End Sub

---

Re:The SQLiteDataSet object is read-only and meaningful for SELECT statements only.

You can fix and add code support for RS.UPDATE

Code:

---

DIM Rs as Recordset
Set RS = New Recordset
RS.OpenRecordset "select * FROM UserTable", Cnn
RS("ID") = RS("ID") + 1

RS.UpdateBatch

---

Code:

---

Public Property Get Item(Field As Variant) As Variant
    'Read Field Value/读取字段值
    Item = cRecordset1(Field)
End Property
Public Property Let Item(Field As Variant, ByVal vNewValue As Variant)
    'Edit Field Value/修改字段值
    cRecordset1(Field) = vNewValue
End Property

---

update fields value like ado

Rs("ID")=11
Rs.UPDATE

Code:

---

RS.EditField("ID") = 11
RS.EditField("Info") = "info edited"
 
If RS.UpdateEx("UserTable", "UserName='" & RS("UserName") & "'") Then
    MsgBox "Update One Recordset ok"
End If

---

Recordset.cls

Code:

---

Public FieldList As New Dictionary
Public Cnn As Connection

Public Property Let EditField(Field As String, ByVal vNewValue As Variant)
    'Edit Field Value/修改字段值
    On Error GoTo Err
    Dim Field1 As New FieldClass
    Field1.FieldName = Field
    Field1.FieldValue = vNewValue
    Field1.TypeName = TypeName(vNewValue)
    'InputBox "", "Field1.TypeName ", Field1.TypeName
    Set FieldList(Field) = Field1
   
    Exit Property
Err:
    DoErr "Edit Field Value"
End Property

Function UpdateEx(table_name As String, WhereStr As String) As Boolean
'Update table_name
'SET column1 = value1, column2 = value2...., columnN = valueN
'WHERE [condition];

Dim i As Long, SQL As String
Dim SqlArr() As String, FieldCount As Long
Dim Field1 As FieldClass
Dim Fg As String
FieldCount = FieldList.Count
ReDim SqlArr(FieldCount - 1)
For i = 0 To FieldCount - 1
    Set Field1 = FieldList.Items(i)
    Select Case Field1.TypeName
        Case "String"
            Fg = "'"
        Case "Date"
        Case Else
        Fg = ""
    End Select
   
    SqlArr(i) = Field1.FieldName & "=" & Fg & Field1.FieldValue & Fg
Next
SQL = "Update " & table_name & " SET " & Join(SqlArr, ",") & IIf(WhereStr <> "", " WHERE " & WhereStr, "")
MsgBox SQL
Set FieldList = New Dictionary
On Error GoTo Err
    UpdateEx = Cnn.Execute(SQL)
Exit Function
Err:
Set FieldList = New Dictionary
DoErr "UpdateEx"
End Function

---

fieldClass.cls

Code:

---

  Public TypeName As String
  Public FieldName As String
  Public FieldValue As Variant

---

Hello, I trying to convert code from DAO to VBSQLite and I'm wondering how to convert DAO's Seek method that works on indexed fields and is actually very fast (hundreds of times faster than DAO's FindFirst) but I don't know how to handle that.

If I have to make a new SQL query for every row that I need to fetch I guess it will be quite slower (it is a guess, I didn't test that still).
Any advice?

What would be the way to insert a new record into a table that has a BLOB type field (binary data in a byte array)?

Quote:

---

Originally Posted by Eduardo-

What would be the way to insert a new record into a table that has a BLOB type field (binary data in a byte array)?

---

An easy way is the Command class where you can pass a byte stream via SetParameterValue.
If that's not wanted you can "textize" your byte stream.
Example:

Quote:

---

X'0102030405060708090a0b0c0d0e0f'

---

About your other question:
When I do heavy tasks with lot of ping pong queries I usually create a memory db and attach it with the current connection. Then backup the table into memory and process query/update and backup back to file.
But that's some kind of effort and pays of in huge number only and when the db is on a network drive etc.

In your case you only query or update as well?
Maybe you can first start to optimize "begin transaction" and "end transaction" so the file db journal is not updated by every single row but rather in a batch.

Quote:

---

Originally Posted by Krool

An easy way is the Command class where you can pass a byte stream via SetParameterValue.
If that's not wanted you can "textize" your byte stream.

---

Hello Krool, thank you.

No, I don't want to textize. That would use more space in the db and also would take some time to convert (and convert back in the retrievals).

How would be the way to store using SetParameterValue?

Lets assume I have these fields: The ID that is the primary key, and autoincrement integer, a second field that is a string, and a third field that is the BLOB.

I have the BLOB data in a bite array.

In my attempts I have something like this:

Code:

---

    Dim SQLiteCon As SQLiteConnection
    Dim MyString As String
    Dim MyData() As Byte

    Set SQLiteCon = New SQLiteConnection
    SQLiteCon.OpenDB "D:\TestDB.db", SQLiteReadWrite
    ' ... fill the variables
    SQLiteCon.Execute "INSERT INTO MyTable (String_Field, Blob_Field) VALUES ('" & MyString & "', " & MyData & ")"

---

That of course doesn't work.

Quote:

---

Originally Posted by Krool

About your other question:
When I do heavy tasks with lot of ping pong queries I usually create a memory db and attach it with the current connection. Then backup the table into memory and process query/update and backup back to file.
But that's some kind of effort and pays of in huge number only and when the db is on a network drive etc.

---

In this case the database will be local.

Quote:

---

Originally Posted by Krool

In your case you only query or update as well?

Maybe you can first start to optimize "begin transaction" and "end transaction" so the file db journal is not updated by every single row but rather in a batch.

---

I have been looking for a way to do that but for the other question, to be able to put one field at a time like DAO does, but could not find how to use transactions.

Back to the second question, I see that your component store all the queried data in memory (o that's what I understood).
In my case I'm trying to move to SQLIte because I have hit the 2 GB limit of Access. I have all the information in just one table. If I load all the data in memory (at least in the local memory) I'lll run out of memory.
IDK how DAO works but I think it does not use local memory, or maybe it caches in memory just some records but I guess not all.
I would like to make this program ready to handle large data, for example 10 GB or more. If the programs attempts to load that data into memory, not only local process memory, but all the machine memory could be exhausted (in case I was using it).

I was trying to make a test program to see how it performs making a new SQL query for each record retrieval (I confess that I don't have high hopes that it will perform as DAO's Seek, but I also hope I'm wrong), but got stuck with adding the BLOB field.

PD: worth to mention that I created an index for the field String_Field, and the data need to be retrieved but that field.

Thanks.

You create a Command class with following sql:

Code:

---

"INSERT INTO MyTable (String_Field, Blob_Field) VALUES (?100, ?101)"

---

Then you use .SetParameterValue with 100 to pass a String and again with 101 to pass a byte stream
Then execute the command.
You can recycle the Command object and change only the parameters. That's more performant.

I hope you get it. I'm only on phone now so bear with me.

Thank you Krool.

I was able to add data using the Command, but I must be doing something wrong because it was extremely slow.
I added 1,000,000 records and it took like 2 hours 40 minutes.

I also see that Vb6.exe was not using all the processing power, but just some minor percent. I was expecting it to be running at full processor power.
I'm using the VBSQLite ActiveX DLL compiled, and testing in IDE.

The code was like this:

Code:

---

    Dim SQLiteCon As SQLiteConnection
    Dim MyString As String
    Dim MyData() As Byte
    Dim MyCommand As SQLiteCommand
   
    Set SQLiteCon = New SQLiteConnection
    SQLiteCon.OpenDB "D:\TestDB.db", SQLiteReadWrite
    Set MyCommand = SQLiteCon.CreateCommand("INSERT INTO MyTable (String_Field, Blob_Field) VALUES (?100 , ?101)")
   
    for ...
        MyCommand.SetParameterValue 100, MyString
        MyCommand.SetParameterValue 101, MyData
        MyCommand.Execute
    Next

---

The data actually came from an Access database where I already have it.

Have started a transaction first? This can make a huge difference

Quote:

---

Originally Posted by Arnoutdv

Have started a transaction first? This can make a huge difference

---

No, how to do that?

Quote:

---

Originally Posted by Eduardo-

No, how to do that?

---

Make a sql execute with "BEGIN TRANSACTION". Then do your 1,000,000 inserts and then execute with "END TRANSACTION".

OK, it improved "a bit", now it took 30 seconds!!

I'll test the speed of searches now (I have to set up the test program to perform many random searches).

Well, bad news. Making a new SQL query for each search is not close to DAO's Seek speed.

1,000,000 random searches, all keys were found:
SQLite: 148 seconds.
DAO: 14 seconds

1,000,000 random searches, all keys NOT found, but the first 32 characters matched existing keys:
SQLite: 126 seconds.
DAO: 7 seconds.

1,000,000 random searches, all keys NOT found, but first character already didn't match any existing key:
SQLite: 117 seconds.
DAO: 1.4 seconds.

Without the underlying test-code behind these results,
it's hard to give recommendations how to speed up SQLite properly.
(in all of my tests, it was faster than the JET-engine).

@Krool
Perhaps a little addition to your wrapper would be worthwhile:
- an SQLiteCursor-Class

99% of the implementation for that could be copied from your SQLiteCommand-Class...

You only have to change the:
- Execute Method
- to a MoveNext Method
where what's currently done in line 120 here: https://github.com/Kr00l/VBSQLite/bl...iteCommand.cls
will be "single-steps".

One such step will then only perform "the Seek-Condition" (as formulated in the SQL-Select the statement is based on).

After such a "seek-step", the User should be free,
- to retrieve ColumnValues at the current Record-Position...
- via Extra-MethodCalls on that Cursor-Class,
- and not implicitely "all of them in each step" as your DataTable-Class currently does for the sake of convenience

Such a Cursor-Class would simulate the DAO-Seek-behaviour quite nicely (even if it is "forward-only")...
(for backward-searches on a given index, the underlying select would have to specify an ...Order By IndexedField Desc in the underlying SQL).

Edit: ... back to Eduardo...
Perhaps all you need (but were not offered with JET) - is an UpSert-Command?
(Insert when a unique Field is not existent, update instead when existent)...
It is described for SQLite here: https://www.sqlite.org/lang_upsert.html (see the examples in 2.1...)

As said, code is needed, which describes in more detail, what you currently "seek".

Olaf

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?

Quote:

---

Originally Posted by Schmidt

Without the underlying test-code behind these results,
it's hard to give recommendations how to speed up SQLite properly.
(in all of my tests, it was faster than the JET-engine).

---

Ok, I'll make a test project.

Quote:

---

Originally Posted by Schmidt

Edit: ... back to Eduardo...
Perhaps all you need (but were not offered with JET) - is an UpSert-Command?
(Insert when a unique Field is not existent, update instead when existent)...
It is described for SQLite here: https://www.sqlite.org/lang_upsert.html (see the examples in 2.1...)

As said, code is needed, which describes in more detail, what you currently "seek".

Olaf

---

If the record is existent I need to read the record, if it does not exist I need to calculate everything and save it in a new record. It is a cache.

But never have update records in this program. When something changed, I delete the old record and schedule a delayed action to insert a new one (after I have the new data).

But one warning, IDK how that Cursor would work, but if it needs to have all the data loaded in memory, it won't be possible.
I need this to work with millions records (they won't fit in the RAM).
That's why I need to change the database system, to be able to handle more records.
In the case of DAO/mdb the problem was the 2 GB file limit, but I don't think it has all the records in memory.

Quote:

---

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.

Attachment 185346

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:

Attachment 185347

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

Quote:

---

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.

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.

Quote:

---

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):
Attachment 185348

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

Quote:

---

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.

Quote:

---

Originally Posted by Schmidt

There's...

Attachment 185348

---

I'll see to test it.
But I wonder why DAO is so slow in your machine.
Compare to my numbers:

Attachment 185349

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

Quote:

---

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

Quote:

---

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:

Attachment 185353

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?

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.

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.

Quote:

---

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.

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

:thumb: 👏👏👏

Quote:

---

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.

Quote:

---

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.

Quote:

---

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

Quote:

---

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:

Quote:

---

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.

---

Quote:

---

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.

Quote:

---

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

I updated the GitHub repo with a new comparison test.

Attachment 185356

Quote:

---

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.

Quote:

---

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!

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?

Quote:

---

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.

Quote:

---

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!

Quote:

---

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.

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.

Quote:

---

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.

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

---

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

Quote:

---

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.

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

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):
Attachment 185369

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

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.

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

Quote:

---

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

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

Quote:

---

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? :eek:

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

Quote:

---

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

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

Quote:

---

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?

Quote:

---

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