Results 1 to 28 of 28

Thread: [RESOLVED] RecordSet vs Collection

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Resolved [RESOLVED] RecordSet vs Collection

    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.
    Last edited by SearchingDataOnly; Mar 21st, 2023 at 05:41 AM.

  2. #2
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,324

    Re: RecordSet vs Collection

    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.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

    Quote Originally Posted by VanGoghGaming View Post
    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.
    Last edited by SearchingDataOnly; Mar 21st, 2023 at 08:44 AM.

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: RecordSet vs Collection

    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.
    Last edited by Elroy; Mar 21st, 2023 at 08:47 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  5. #5
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,324

    Lightbulb Re: RecordSet vs Collection

    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.

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

    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.

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

  7. #7
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: RecordSet vs Collection

    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.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

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

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

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

  10. #10
    Addicted Member ISAWHIM's Avatar
    Join Date
    Jan 2023
    Posts
    181

    Re: RecordSet vs Collection

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

  11. #11
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: RecordSet vs Collection

    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?
    Last edited by Elroy; Mar 21st, 2023 at 12:11 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  12. #12
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: RecordSet vs Collection

    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.

  13. #13
    Addicted Member ISAWHIM's Avatar
    Join Date
    Jan 2023
    Posts
    181

    Re: RecordSet vs Collection

    Quote Originally Posted by Elroy View Post
    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.
    Last edited by ISAWHIM; Mar 21st, 2023 at 04:18 PM.

  14. #14

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

    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.

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

    Re: RecordSet vs Collection

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

  16. #16
    Fanatic Member
    Join Date
    Jan 2015
    Posts
    596

    Re: RecordSet vs Collection

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

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

    Re: RecordSet vs Collection

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

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: RecordSet vs Collection

    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

  19. #19

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

    Quote Originally Posted by jpbro View Post
    Do you have a link? Sounds interesting.
    Quote Originally Posted by jpbro View Post
    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.

  20. #20

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

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

  21. #21

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

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

  22. #22

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

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

    Code:
    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:

    Code:
    Private Sub New_RC6RecordSet()           '--- New_RC6Cursor ---
        Dim Cusor As cCusor
        Set Cursor = MemDB.CreateCursor("Select * From KeyValue Where Key = ?")
        Set Cursor = Nothing
    
    End Sub
    Last edited by SearchingDataOnly; Mar 24th, 2023 at 08:08 AM.

  23. #23

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

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

    Code:
    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

  24. #24
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: RecordSet vs Collection

    Quote Originally Posted by SearchingDataOnly View Post
    For sequential access, cCursor seems to be slower than cRecordSet, for example:

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

    Code:
    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

  25. #25

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

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

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

  26. #26

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

    Quote Originally Posted by Schmidt View Post
    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):
    Code:
    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:
    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.
    Last edited by SearchingDataOnly; Mar 25th, 2023 at 03:48 AM.

  27. #27
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: RecordSet vs Collection

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

  28. #28

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: RecordSet vs Collection

    Very detailed and clear explanation, much appreciated.

Posting Permissions

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



Click Here to Expand Forum to Full Width