[RESOLVED] RecordSet vs Collection

[SearchingDataOnly]

In recent years, I have often pondered a question:

Which one has the higher performance, RecordSet or Collection?

If RecordSet of DB (RC6.SqliteDB or AccessDB) performs better, then we might be able to replace the Collections (VB6.Collection or RC6.Collection) with DB-RecordSet frequently.

I haven't come up with a suitable test case, could someone analyze the performance difference between RecordSet and Collection from the internal mechanisms of the two? Thanks!


Note:
RC6.SqliteDB has a MemDB mode, which means that the entire database is stored in memory, so the performance will be much higher.



Edit:
We know that in VB6, the use of Class, UDT, and Collection has some strange limitations or flaws. If we use RecordSet instead of Class, UDT and Collection, those weird limitations or flaws will never be there again.

[VanGoghGaming]

Pretty sure the RecordSet should be slower due to the additional overhead involved but the difference in speed may be negligible depending on the size of your data.

[SearchingDataOnly]

Pretty sure the RecordSet should be slower due to the additional overhead involved but the difference in speed may be negligible depending on the size of your data.

I also guess RecordSet is slower than Collection, but I think there should be some way to greatly improve the performance of RecordSet. For example, MemeDB and RecordSet.ValueMatrix greatly improve the performance of RecordSet. I suspect there should be more ways to improve the performance of the RecordSet so that the RecordSet can achieve or approach the performance of the Collection.

[Elroy]

You can also do a memory-based recordset with the ADO. If you want to see that done extensively, see LaVolpe's Project Scanner.

But yeah, without testing (which I'll let you do, SDO), I'd have to agree with VGG that a Collection would be faster, because of the management overhead having to do with RecordSets. Also, the Dictionary is even faster. The Collection vs Dictionary (CreateObject("Scripting.Dictionary")) are quite similar, with the primary difference being that the Collection uses a binary-tree approach to indexing the keys and the Dictionary uses a hash-table approach.

[VanGoghGaming]

With a RecordSet first you need to move the cursor to the desired row before you can read its fields but yeah it can solve the problem of adding UDTs to a collection for example. A dictionary is also good, especially for its ability to check if a key already exists or not and it's also more robust than a collection but it has the same limitation regarding UDTs.

[SearchingDataOnly]

But yeah, without testing (which I'll let you do, SDO), I'd have to agree with VGG that a Collection would be faster, because of the management overhead having to do with RecordSets. Also, the Dictionary is even faster. The Collection vs Dictionary (CreateObject("Scripting.Dictionary")) are quite similar, with the primary difference being that the Collection uses a binary-tree approach to indexing the keys and the Dictionary uses a hash-table approach.[/QUOTE]

Yes, I was going to do that. If we can improve the performance of LaVolpe's Project Scanner by 10x, then it'll be very valuable to me.

But yeah, without testing (which I'll let you do, SDO), I'd have to agree with VGG that a Collection would be faster, because of the management overhead having to do with RecordSets. Also, the Dictionary is even faster. The Collection vs Dictionary (CreateObject("Scripting.Dictionary")) are quite similar, with the primary difference being that the Collection uses a binary-tree approach to indexing the keys and the Dictionary uses a hash-table approach.

I also guess RecordSet is slower than Collection, but I think there should be some way to greatly improve the performance of RecordSet. For example, MemeDB and RecordSet.ValueMatrix greatly improve the performance of RecordSet. I suspect there should be more ways to improve the performance of the RecordSet so that the RecordSet can achieve or approach the performance of the Collection.

[Elroy]

SDO, how often do you need to scan your projects?

My primary project (with 100s of modules) hasn't been scanned in years, as it just doesn't seem necessary. And small projects seem to scan fast enough. I'm not sure I see a sound reason for forking LaVolpe's wonderful project.

[SearchingDataOnly]

With a RecordSet first you need to move the cursor to the desired row before you can read its fields but yeah it can solve the problem of adding UDTs to a collection for example. A dictionary is also good, especially for its ability to check if a key already exists or not and it's also more robust than a collection but it has the same limitation regarding UDTs.

RecordSet can not only solve the problem of adding UDTs to a Collection, it can also solve the problem of extremely slow releasing of class instances, and can also solve the problem of limiting the number of classes in VB6-IDE (it seems that VB6-IDE can only load up to 720 or 900 classes)

[SearchingDataOnly]

SDO, how often do you need to scan your projects?

My primary project (with 100s of modules) hasn't been scanned in years, as it just doesn't seem necessary. And small projects seem to scan fast enough. I'm not sure I see a sound reason for forking LaVolpe's wonderful project.

Currently, I only need to scan my project occasionally, but in the future, I hope to apply some of the techniques of project scanning to my code-editor, which means that I need to get the type information in my project instantly.

[ISAWHIM]

Things to consider in evaluating things like this, are the access "functions", which each has, or doesn't have.

EG, in a record-set, you can usually provide some kind of scripting to get contents out, or insert them, etc. While with something raw like arrays, UDTs and classes, YOU have to code the various desired types of fetch and fill functions.

Behind the scenes, that is virtually all that is happening. How it happens and what depths the functions have, will really be the major factor. (If it is using file-reads, or if it has a "fast index", or if it is using RegEx in code, or if it has to convert things for comparisons...)

I would go so far as to say that ANY custom setup will be faster than ANY "DB Access", which is more of a "function over speed" and a "do all, at any cost", type of setup. While a custom setup will do, "just enough", and be notably "speedy with limited function".

I love doing time-demos, but this is a bit more complex than just comparing (X^2) vs (X * X)... It's more like comparing a TextBox to a RichTextBox. (TextBox would win for speed, and the other would win for function/ability.)

Honestly, I am surprised that there isn't already a time-demo comparison. (Where it is apples to apples, or even apples to oranges, and apples to soil.)

[Elroy]

SDO, Isawhim's post is a bit of a rough read, but he does make a good point, primarily that a RecordSet (even if memory based) has many more methods than a Collection (or Dictionary).

Just a cursory look at LaVolpe's Project Scanner reveals he's using at least these methods:

• MoveFirst
• MoveNext
• EOF
• Fields
• Close
• Clone
• Filter
• RecordCount
• Sort
• Append
• CursorLocation
• Open
• AddNew

In addition, he's running SQL queries on those Recordsets, not possible with Collections nor Dictionaries.

SDO, you may surprise me, but adding all this functionality to a Collection would be very far from a trivial task. And yet again, really? ... a fork of his very nice project, just to get rid of the Recordsets?

[xiaoyao]

sqlite memdb，There's no speedup in speed, it's just a little bit faster than reading the hard drive.Now the nve hard drive. The reading speed has reached about 1000 megabits per second.It's not the same thing as a real in-memory database.

[ISAWHIM]

SDO, Isawhim's post is a bit of a rough read

Yeah, I have that problem sometimes. My apologies. That is something I am working on.

I found a LOT about A faster than B, with ball-park guestimates, but that is ADO vs DAO. A lot of information on how to do specific calls or format data better too. No direct time-demo tests.

I hoped that I would find something to compare.

General conclusion is to use everything. :P
Read DB-> Write to Array -> Read/Write the Array -> Write/Save to DB-> Set next things into Array
Array or Collection as needed.

As opposed to ONLY using one or the other, for everything. Whatever is faster, for your needs.

[SearchingDataOnly]

Thank you ISAWHIM, Elroy and xiaoyao for your replies.

I just found a simple test case done in 2019, it seems that VB6.Dictionary is 2 times faster than RC6.RecordSet, RC6.Collection is 8 times faster than RC6.RecordSet, and RC6.SortDictionary is 7 times faster than RC6.RecordSet.

[jpbro]

Thank you ISAWHIM, Elroy and xiaoyao for your replies.

I just found a simple test case done in 2019, it seems that VB6.Dictionary is 2 times faster than RC6.RecordSet, RC6.Collection is 8 times faster than RC6.RecordSet, and RC6.SortDictionary is 7 times faster than RC6.RecordSet.

Do you have a link? Sounds interesting.

[Thierry69]

I did some test of performance in the past.
See in my signature

[jpbro]

I did some test of performance in the past.
See in my signature

Thanks Thierry! I'll take a look. I'm always curious about claims that X is "faster" than Y without any description of the job. What does it mean that "something is 4x faster than some other thing", if we're not defining/constraining the "thing" being measured?

@SDO - to your original question: "Which one has the higher performance, RecordSet or Collection?" - higher performance at what?

[Schmidt]

About half a year ago (during a discussion with Eduardo), I've introduced the new cCursor-Class,
which (when used against SQLite-InMemory-Tables) performs basically "as good as the RC6.Dictionary"
(but supports "multicolumn-records" and is not really "size-restricted").

So, RC6.cCursor behaves more like a "lightweight, cSelectCommand" -
able to perform "fast exists-checks on multiple criterions in the where-clause"...

HTH

Olaf

[SearchingDataOnly]

Do you have a link? Sounds interesting.

Thanks Thierry! I'll take a look. I'm always curious about claims that X is "faster" than Y without any description of the job. What does it mean that "something is 4x faster than some other thing", if we're not defining/constraining the "thing" being measured?

@SDO - to your original question: "Which one has the higher performance, RecordSet or Collection?" - higher performance at what?

I've written a new test case and I'll upload it in a few minutes.

[SearchingDataOnly]

Here is the new test code: (Note: In exe-mode, the performance will be much improved)

[SearchingDataOnly]

About half a year ago (during a discussion with Eduardo), I've introduced the new cCursor-Class,
which (when used against SQLite-InMemory-Tables) performs basically "as good as the RC6.Dictionary"
(but supports "multicolumn-records" and is not really "size-restricted").

So, RC6.cCursor behaves more like a "lightweight, cSelectCommand" -
able to perform "fast exists-checks on multiple criterions in the where-clause"...

HTH

Olaf

That's great, I'll test cCursor in a moment. Thank you very much, Olaf.

[SearchingDataOnly]

The test code in #Post20 needs to be changed as follows:

Private Sub New_RC6RecordSet()
    Dim Rs As cRecordSet

    'Set Rs = MemDB.GetTable("KeyValue")
    Set Rs = MemDB.GetRs("Select Top 0 * From KeyValue")

    Set Rs = Nothing

End Sub

In addition, using RC6.cCommand instead of RC6.cRecordSet to add records will improve performance by 5 times.

In the Class-Instance test, replacing GetRs with CreateCommand or CreateCursor will reduce the time required by dozens of times (i.e. performance will increase dozens of times). For example:

Private Sub New_RC6RecordSet()           '--- New_RC6Cursor ---
    Dim Cusor As cCusor
    Set Cursor = MemDB.CreateCursor("Select * From KeyValue Where Key = ?")
    Set Cursor = Nothing

End Sub

[SearchingDataOnly]

About half a year ago (during a discussion with Eduardo), I've introduced the new cCursor-Class,
which (when used against SQLite-InMemory-Tables) performs basically "as good as the RC6.Dictionary"
(but supports "multicolumn-records" and is not really "size-restricted").

So, RC6.cCursor behaves more like a "lightweight, cSelectCommand" -
able to perform "fast exists-checks on multiple criterions in the where-clause"...

HTH

Olaf

For sequential access, cCursor seems to be slower than cRecordSet, for example:

Private Sub GetItems_RecordSet()
    New_c.Timing True
        
    If Rs.BOF = False Then Rs.MoveFirst
    
    For i = 1 To TEST_COUNT
        Key = Rs.Fields("Key")
        Value = Rs.Fields("Value")
        Rs.MoveNext
    Next i
    
    LstResult.AddItem ResultString("RC6RecordSet", New_c.Timing)
    
End Sub

Private Sub GetItems_Cursor()
    Dim oCursor As cCursor
    
    New_c.Timing True
        
    Set oCursor = MemDB.CreateCursor("Select * From KeyValue Where Key = ?")
    
    For i = 1 To TEST_COUNT
        oCursor.SetText 1, "Key" & i
        If oCursor.Step Then
            Value = oCursor.ColVal(1)
        End If
    Next i
    
    LstResult.AddItem ResultString("RC6Cursor", New_c.Timing)
    
End Sub

[Schmidt]

For sequential access, cCursor seems to be slower than cRecordSet, for example:

Private Sub GetItems_RecordSet()
    New_c.Timing True
        
    If Rs.BOF = False Then Rs.MoveFirst
    
    For i = 1 To TEST_COUNT
        Key = Rs.Fields("Key")
        Value = Rs.Fields("Value")
        Rs.MoveNext
    Next i
    
    LstResult.AddItem ResultString("RC6RecordSet", New_c.Timing)
    
End Sub

Private Sub GetItems_Cursor()
    Dim oCursor As cCursor
    
    New_c.Timing True
        
    Set oCursor = MemDB.CreateCursor("Select * From KeyValue Where Key = ?")
    
    For i = 1 To TEST_COUNT
        oCursor.SetText 1, "Key" & i
        If oCursor.Step Then
            Value = oCursor.ColVal(1)
        End If
    Next i
    
    LstResult.AddItem ResultString("RC6Cursor", New_c.Timing)
    
End Sub

To compare "apples with apples", you will have to use the Bool-returning:
- Rs.FindFirst-Method on the same FieldName of the cRecordset-instance
in each loop-iteration...

Or, (alternatively) change the cCursor-based looping to "plain enumeration-mode" (without a Where-clause):

Private Sub GetItems_Cursor()
    Dim oCursor As cCursor
    
    New_c.Timing True
        
    Set oCursor = MemDB.CreateCursor("Select * From KeyValue")
    
    Do While oCursor.Step  
       Key = oCursor.ColVal(0)
       Value = oCursor.ColVal(1)
    Loop
    
    LstResult.AddItem ResultString("RC6Cursor", New_c.Timing)
    
End Sub

Below is an example-comparison between cCollection and cMemDB/cCursor -
and both are very comparable regarding Add- and Qry-performance,
in a multi-column-scenario with 200000-record-entries.

Option Explicit

Const TestCount As Long = 200000
Private C As cCollection, M As cMemDB

Private Sub Form_Load()
  Caption = "Click Me"
  
  Set C = New_c.Collection(False, TextCompare, False)
  Set M = New_c.MemDB
      M.Exec "Create Table Methods(Name Text Collate NoCase, PrvFrnPub Int, CodeModule Text Collate NoCase)"
  
  New_c.Timing True
      AddTestEntriesToCollection
  Debug.Print "AddTestEntriesToCollection:" & New_c.Timing
  
  New_c.Timing True
      AddTestEntriesToMemDB
  Debug.Print "AddTestEntriesToMemDB:" & New_c.Timing
End Sub

Private Sub Form_Click()
  Cls
  
  Dim FoundInC As Long, FoundInM As Long
  New_c.Timing True
      FoundInC = QueryEntriesInCollection
  Print "QueryEntriesInCollection:", New_c.Timing & " (" & FoundInC & ")"
  
  New_c.Timing True
      FoundInM = QueryEntriesInMemDB
  Print "QueryEntriesInMemDB:", New_c.Timing & " (" & FoundInM & ")"
End Sub

Sub AddTestEntriesToCollection()
  Dim i As Long
  For i = 0 To TestCount - 1
      C.Add Array(i Mod 3, _
                 "ModuleFile_" & i & IIf(i Mod 2, ".cls", ".bas") _
            ), "Method_" & i
  Next
End Sub

Sub AddTestEntriesToMemDB()
  Dim Cmd As cCommand, i As Long
  Set Cmd = M.CreateCommand("Insert Into Methods Values(?,?,?)")
  M.BeginTrans
  For i = 0 To TestCount - 1
      Cmd.SetText 1, "Method_" & i 'Name of the Method
      Cmd.SetInt32 2, i Mod 3  'Private, Friend, Public (as EnumValues 0, 1, 2)
      Cmd.SetText 3, "ModuleFile_" & i & IIf(i Mod 2, ".cls", ".bas") 'CodeModule of the Method
      Cmd.Execute
  Next
  M.Exec "Create Index idx_Methods_Name On Methods(Name)" 'don't forget to index the Table-Column
End Sub

Function QueryEntriesInCollection() As Long
  Rnd -123 'to ensure the same random-sequence
  
  Dim i As Long, SymbolName As String, Arr, PrvFrnPub As Long, CodeModule As String
  For i = 1 To TestCount
    SymbolName = "Method_" & Int(Rnd * 1.5 * TestCount) 'we use 1.5 * TestCount, to produce a few Keys, which do not exist
    If C.Exists(SymbolName) Then
       Arr = C(SymbolName)
       PrvFrnPub = Arr(0)
       CodeModule = Arr(1)
       QueryEntriesInCollection = QueryEntriesInCollection + 1
    End If
  Next
End Function

Function QueryEntriesInMemDB() As Long
  Rnd -123 'to ensure the same random-sequence
  Dim Cur As cCursor
  Set Cur = M.CreateCursor("Select PrvFrnPub, CodeModule From Methods Where Name=?")
  
  Dim i As Long, SymbolName As String, Arr, PrvFrnPub As Long, CodeModule As String
  For i = 1 To TestCount
    SymbolName = "Method_" & Int(Rnd * 1.5 * TestCount) 'we use 1.5 * TestCount, to produce a few Keys, which do not exist
    Cur.SetText 1, SymbolName
    If Cur.Step Then
       PrvFrnPub = Cur.ColVal(0)
       CodeModule = Cur.ColVal(1)
       QueryEntriesInMemDB = QueryEntriesInMemDB + 1
    End If
    Cur.Reset
  Next
End Function

HTH

Olaf

[SearchingDataOnly]

Below is an example-comparison between cCollection and cMemDB/cCursor -
and both are very comparable regarding Add- and Qry-performance,
in a multi-column-scenario with 200000-record-entries.

Option Explicit

Const TestCount As Long = 200000
Private C As cCollection, M As cMemDB

Private Sub Form_Load()
  Caption = "Click Me"
  
  Set C = New_c.Collection(False, TextCompare, False)
  Set M = New_c.MemDB
      M.Exec "Create Table Methods(Name Text Collate NoCase, PrvFrnPub Int, CodeModule Text Collate NoCase)"
  
  New_c.Timing True
      AddTestEntriesToCollection
  Debug.Print "AddTestEntriesToCollection:" & New_c.Timing
  
  New_c.Timing True
      AddTestEntriesToMemDB
  Debug.Print "AddTestEntriesToMemDB:" & New_c.Timing
End Sub

Private Sub Form_Click()
  Cls
  
  Dim FoundInC As Long, FoundInM As Long
  New_c.Timing True
      FoundInC = QueryEntriesInCollection
  Print "QueryEntriesInCollection:", New_c.Timing & " (" & FoundInC & ")"
  
  New_c.Timing True
      FoundInM = QueryEntriesInMemDB
  Print "QueryEntriesInMemDB:", New_c.Timing & " (" & FoundInM & ")"
End Sub

Sub AddTestEntriesToCollection()
  Dim i As Long
  For i = 0 To TestCount - 1
      C.Add Array(i Mod 3, _
                 "ModuleFile_" & i & IIf(i Mod 2, ".cls", ".bas") _
            ), "Method_" & i
  Next
End Sub

Sub AddTestEntriesToMemDB()
  Dim Cmd As cCommand, i As Long
  Set Cmd = M.CreateCommand("Insert Into Methods Values(?,?,?)")
  M.BeginTrans
  For i = 0 To TestCount - 1
      Cmd.SetText 1, "Method_" & i 'Name of the Method
      Cmd.SetInt32 2, i Mod 3  'Private, Friend, Public (as EnumValues 0, 1, 2)
      Cmd.SetText 3, "ModuleFile_" & i & IIf(i Mod 2, ".cls", ".bas") 'CodeModule of the Method
      Cmd.Execute
  Next
  M.Exec "Create Index idx_Methods_Name On Methods(Name)" 'don't forget to index the Table-Column
End Sub

Function QueryEntriesInCollection() As Long
  Rnd -123 'to ensure the same random-sequence
  
  Dim i As Long, SymbolName As String, Arr, PrvFrnPub As Long, CodeModule As String
  For i = 1 To TestCount
    SymbolName = "Method_" & Int(Rnd * 1.5 * TestCount) 'we use 1.5 * TestCount, to produce a few Keys, which do not exist
    If C.Exists(SymbolName) Then
       Arr = C(SymbolName)
       PrvFrnPub = Arr(0)
       CodeModule = Arr(1)
       QueryEntriesInCollection = QueryEntriesInCollection + 1
    End If
  Next
End Function

Function QueryEntriesInMemDB() As Long
  Rnd -123 'to ensure the same random-sequence
  Dim Cur As cCursor
  Set Cur = M.CreateCursor("Select PrvFrnPub, CodeModule From Methods Where Name=?")
  
  Dim i As Long, SymbolName As String, Arr, PrvFrnPub As Long, CodeModule As String
  For i = 1 To TestCount
    SymbolName = "Method_" & Int(Rnd * 1.5 * TestCount) 'we use 1.5 * TestCount, to produce a few Keys, which do not exist
    Cur.SetText 1, SymbolName
    If Cur.Step Then
       PrvFrnPub = Cur.ColVal(0)
       CodeModule = Cur.ColVal(1)
       QueryEntriesInMemDB = QueryEntriesInMemDB + 1
    End If
    Cur.Reset
  Next
End Function

HTH

Olaf

Very useful example, much appreciated.

[SearchingDataOnly]

To compare "apples with apples", you will have to use the Bool-returning:
- Rs.FindFirst-Method on the same FieldName of the cRecordset-instance
in each loop-iteration...

Or, (alternatively) change the cCursor-based looping to "plain enumeration-mode" (without a Where-clause):

Private Sub GetItems_Cursor()
    Dim oCursor As cCursor
    
    New_c.Timing True
        
    Set oCursor = MemDB.CreateCursor("Select * From KeyValue")
    
    Do While oCursor.Step  
       Key = oCursor.ColVal(0)
       Value = oCursor.ColVal(1)
    Loop
    
    LstResult.AddItem ResultString("RC6Cursor", New_c.Timing)
    
End Sub

Yes, with "plain enumeration-mode", Cursor's performance has improved a lot. However, I found a strange phenomenon during testing:
[*] In IDE Mode, oCursor performs faster than RecordSet.ValueMatrix
[*] In EXE Mode, Cursor performs slower than RecordSet.ValueMatrix

Below is the test code:

Private Sub GetItems_ValueMatrix()
    New_c.Timing True
            
    For i = 0 To TEST_COUNT - 1
        Key = Rs.ValueMatrix(i, 0)
        Value = Rs.ValueMatrix(i, 1)
    Next i
    
    LstResult.AddItem ResultString("RC6ValueMatrix", New_c.Timing)
    
End Sub

If Cursor is indeed slower than RecordSet.ValueMatrix, then Cursor's primary (or only) use case is "query and find". I don't know if my understanding is correct.

[Schmidt]

If Cursor is indeed slower than RecordSet.ValueMatrix, then Cursor's primary (or only) use case is "query and find". I don't know if my understanding is correct.

It's not (yet)...

A Cursor-based Step-loop is, what's used under the covers, to *fill* a cRecordset in the first place.
(under the covers of: Cnn.OpenRecordset, or MemDB.GetRs) ...
...and you left this "Rs-Fill" call out of your performance-test for the Rs entirely.

After an "instantiate Rs and fill it according to the SQL-String" method above went through,
the Cnn could be closed - and you would still be able to retrieve Values via Rs.Fields(...).Value or Rs.ValueMatrix
directly from the Rs-internal "VB-array-buffer-structures" ...
(an Rs "holds the data-set InMemory" - and can be used in "true disconnected Mode").

So, with an Rs - the "querying and filling" is the most time-consuming part (not the "enumerated Value-retrieval").

With a cCursor, the "initial construction" (instantiation, SQL-passing and -string-parsing), returns very fast
(compared to an Rs), ... the remaining "Step-iteration - and Value-retrieval directly from the DB" -
is then left to "outside UserCode".

To conclude, cRecordsets make sense, when you select "midsize or larger sets" from the DB,
with the intent to "keep them around for a longer time" (e.g. for databinding or navigating back and forward on them) -
whereas a cCursor makes sense, when you plan to do "high-frequent calls" against the DB,
for "existence-checks" or to retrieve smaller sets (of one to "a dozen or so" records).

Olaf

[SearchingDataOnly]

Very detailed and clear explanation, much appreciated.