Results 1 to 25 of 25

Thread: [RESOLVED] Sorting-Array and Sorting-Collection, which one is faster?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Resolved [RESOLVED] Sorting-Array and Sorting-Collection, which one is faster?

    I need to deal with a set of data that needs sorting. Now there are three options, I would like to know which one is faster.

    I know that array is faster than collection in general. But if I need to insert, delete, and sort the array, will the array be faster than the collection?

    ---Solution 1: Array ---
    Class cArrayTest
    Code:
    Option Explicit
    
    Private Type typeUDT
        SequenceNum  As Long    '1, 2, 3, 4....
        DataSize As Long            'Sort by
        Text1 As String
        Text2 As String
        Data1 As Long
        Data2 As Long
    End Type
    
    Private m_arrSortedUDT() As typeUDT    'Sort typeUDT.DataSize
    
    Public Sub AddItem(DataSize As Long, Text1 As String, Text2 As String, Data1 As Long, Data2 As Long)
    
        'According to DataSize(sorted by DataSize),
        'typeUDT is inserted into the appropriate position in the array m_arrSortedUDT,
        'and updating all SequenceNum of m_arrSortedUDT
        
    End Sub
    
    Public Sub RemoveItem(SequenceNum As Long)
    
        'According to SequenceNum,
        'delete one of the items in the array m_arrSortedUDT
        'and update all SequenceNums in m_arrSortedUDT
        
    End Sub
    
    Public Function GetItem(ByVal SequenceNum As Long, ByRef DataSize As Long, ByRef Text1 As String, _
                                     ByRef Text2 As String, ByRef Data1 As Long, ByRef Data2 As Long) As Boolean
    
    End Function
    ---Solution 2: Collection ---
    Class cItem
    Code:
    Option Explicit
    
    Public SequenceNum  As Long    '1, 2, 3, 4....
    Public DataSize As Long            'Sort by
    Public Text1 As String
    Public Text2 As String
    Public Data1 As Long
    Public Data2 As Long
    Class cCollectionTest
    Code:
    Private m_SortedItemList As New Collection
    
    Public Sub AddItem(objItem As cItem)
        'According to cItem.DataSize(sorted by cItem.DataSize),
        'objItem is inserted into the appropriate position in the collection m_SortedItemList,
        'and updating all SequenceNum of m_arrSortedUDT
    End Sub
    
    Public Sub RemoveItem(SequenceNum As Long)
        'According to SequenceNum,
        'delete one of the items in the collection m_SortedItemList
        'and update all SequenceNums in m_SortedItemList
    End Sub
    
    Public Function GetItem(ByVal SequenceNum As Long) As cItem
    
    End Function
    ---Solution 3: vbRichClient5.cArrayList and cSortedDictionary ---

    Edit:
    The test source code is in #6
    Attached Files Attached Files
    Last edited by dreammanor; Dec 20th, 2017 at 01:39 PM.

  2. #2
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    What kind of sort method are thinking of implementing? bubble sort? quick sort? or one of many

    https://en.wikipedia.org/wiki/Sorting_algorithm
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by Goggy View Post
    What kind of sort method are thinking of implementing? bubble sort? quick sort? or one of many

    https://en.wikipedia.org/wiki/Sorting_algorithm
    Thank you Goggy. Yes, which one is faster depends on what sorting algorithm I use and what helper class of collection I use. I found three useful information on the vbForums, but I need time to study them. If someone tells me which solution is better, I just need to study the better solution.

    http://www.vbforums.com/showthread.p...ght=Collection

    http://www.vbforums.com/showthread.p...ing-algorithm)

    http://www.vbforums.com/showthread.p...=1#post5169881
    Last edited by dreammanor; Dec 19th, 2017 at 04:05 AM.

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,207

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    The Container which is the most universal one for your case (faster than the VB-Collection), is the RC5-cCollection,
    since it is automatically sorted internally (by Key, whilst adding), and supports two access- or enumeration-modes:
    - by AddOrder(Index)
    - by SortOrder(Index)

    The reason I've introduced these two modes is, that when you work with a "virtual Grid or List" (for rendering) -
    and the cCollection is serving as the "bound Container", you might want to switch between three states of the GUI-Control:
    - show Items sorted ascending (the cCollection then using its sorted Index: Col.ItemBySortKeyIndex(Grid_RowIndex)
    - show Items sorted descending (the cCollection then using its sorted Index "in reverse": Col.ItemBySortKeyIndex(Col.Count-1-Grid_RowIndex))
    - show Items unsorted (as they were originally added): Col.ItemByIndex(Grid_RowIndex)

    This way, you won't really need a "SequenceNumber" (not sure, for what you will need that in your scenario),
    so the following example is leaving that out...

    Into a Class cItem:
    Code:
    Option Explicit
     
    Public DataSize As Long '<- Field to Sort by
    Public Text1 As String, Text2 As String, Data1 As Long, Data2 As Long
    Into a Class cListColRC5:
    Code:
    Option Explicit
    
    Private mCol As cCollection
    
    Private Sub Class_Initialize()
      Set mCol = New_c.Collection(CompatibleToVBCollection:=False, UniqueKeys:=False)
    End Sub
    
    Public Property Get Count() As Long
      Count = mCol.Count
    End Property
    
    Public Sub AddItem(ByVal DataSize As Long, Text1 As String, Text2 As String, ByVal Data1 As Long, ByVal Data2 As Long)
      Dim NewItem As New cItem
          NewItem.DataSize = DataSize
          NewItem.Text1 = Text1
          NewItem.Text2 = Text2
          NewItem.Data1 = Data1
          NewItem.Data2 = Data2
      mCol.Add NewItem, DataSize
    End Sub
    
    Public Sub RemoveItemByAddIndex(ByVal SequenceNumber As Long)
      mCol.RemoveByIndex SequenceNumber - 1
    End Sub
    
    Public Function ItemByAddIndex(ByVal SequenceNumber As Long) As cItem
      Set ItemByAddIndex = mCol.ItemByIndex(SequenceNumber - 1)
    End Function
    
    Public Sub RemoveItemBySortIndex(ByVal SequenceNumber As Long)
      mCol.RemoveByIndex mCol.IndexBySortKeyIndex(SequenceNumber - 1)
    End Sub
    
    Public Function ItemBySortIndex(ByVal SequenceNumber As Long) As cItem
      Set ItemBySortIndex = mCol.ItemBySortKeyIndex(SequenceNumber - 1)
    End Function
    
    Public Sub RemoveAll()
      mCol.RemoveAll
    End Sub
    Into the TestForm:
    Code:
    Option Explicit
    
    Private List As New cListColRC5
    
    Private Sub Form_Load()
      List.AddItem 55555, "Text5555", "Text555", 55, 5
      List.AddItem 33333, "Text3333", "Text333", 33, 3
      List.AddItem 11111, "Text1111", "Text111", 11, 1
      List.AddItem 77777, "Text7777", "Text777", 77, 7
      List.AddItem 22222, "Text2222", "Text222", 22, 2
        EnumerateList True  'enumerate in Add-Order
        EnumerateList False 'enumerate in Sort-Order
      
      List.RemoveItemByAddIndex 3 'this removes the third item (as added above, the one with the "...1111")
        EnumerateList True  'enumerate in Add-Order
        EnumerateList False 'enumerate in Sort-Order
    
      List.RemoveItemBySortIndex 1 'this removes the item, which is currently occupying the sorted Top-Position ...2222)
        EnumerateList True  'enumerate in Add-Order
        EnumerateList False 'enumerate in Sort-Order
    End Sub
    
    Private Sub EnumerateList(Optional ByVal InAddOrder As Boolean)
      Debug.Print vbLf; "Listing in " & IIf(InAddOrder, "Add-Order", "Sort-Order"); vbLf; String(60, "-")
      Dim i
      For i = 1 To List.Count
        If InAddOrder Then
          With List.ItemByAddIndex(i)
            Debug.Print .DataSize, .Text1, .Text2, .Data1, .Data2
          End With
        Else
          With List.ItemBySortIndex(i)
            Debug.Print .DataSize, .Text1, .Text2, .Data1, .Data2
          End With
        End If
      Next
    End Sub
    The above prints out:
    Code:
    Listing in Add-Order
    ------------------------------------------------------------
     55555        Text5555      Text555        55            5 
     33333        Text3333      Text333        33            3 
     11111        Text1111      Text111        11            1 
     77777        Text7777      Text777        77            7 
     22222        Text2222      Text222        22            2 
    
    Listing in Sort-Order
    ------------------------------------------------------------
     11111        Text1111      Text111        11            1 
     22222        Text2222      Text222        22            2 
     33333        Text3333      Text333        33            3 
     55555        Text5555      Text555        55            5 
     77777        Text7777      Text777        77            7 
    
    Listing in Add-Order
    ------------------------------------------------------------
     55555        Text5555      Text555        55            5 
     33333        Text3333      Text333        33            3 
     77777        Text7777      Text777        77            7 
     22222        Text2222      Text222        22            2 
    
    Listing in Sort-Order
    ------------------------------------------------------------
     22222        Text2222      Text222        22            2 
     33333        Text3333      Text333        33            3 
     55555        Text5555      Text555        55            5 
     77777        Text7777      Text777        77            7 
    
    Listing in Add-Order
    ------------------------------------------------------------
     55555        Text5555      Text555        55            5 
     33333        Text3333      Text333        33            3 
     77777        Text7777      Text777        77            7 
    
    Listing in Sort-Order
    ------------------------------------------------------------
     33333        Text3333      Text333        33            3 
     55555        Text5555      Text555        55            5 
     77777        Text7777      Text777        77            7
    HTH

    Olaf

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Great, it's the best solution. Much appreciate.

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    The test results are very surprising.

    (1) When the number of tested-items is 10000, RC5 is 16611 times faster than VB-Collection and RC5 is 350 times faster than VB-Array

    (2) When the number of tested-items is 5000, RC5 is 3257 times faster than VB-Collection and RC5 is 154 times faster than VB-Array

    (3) When the number of tested-items is 3000, RC5 is 1415 times faster than VB-Collection and RC5 is 95 times faster than VB-Array

    (4) When the number of tested-items is 1000, RC5 is 241 times faster than VB-Collection and RC5 is 31 times faster than VB-Array

    Is there something wrong with my test code?



    Note: I only tested the Add function because its speed is the most important to me.

    In addition, the max number I need is 30,000, but the VB-Collection takes too long.
    Attached Files Attached Files
    Last edited by dreammanor; Dec 19th, 2017 at 12:31 PM.

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    The test results are as follows:

    Code:
    -------------------------------
    Count of Test-Entries: 10000 
    
    Array: 15187.7 msec
    
    Collection: 720941.3 msec
    
    CollectionEx: 702066 msec
    
    RC5: 43.4 msec
    
    -------------------------------
    Count of Test-Entries: 5000 
    
    Array: 3451.5 msec
    
    Collection: 72957.1 msec
    
    CollectionEx: 71477.5 msec
    
    RC5: 22.4 msec
    
    -------------------------------
    Count of Test-Entries: 3000 
    
    Array: 1185.1 msec
    
    Collection: 17688.7 msec
    
    CollectionEx: 15923.9 msec
    
    RC5: 12.5 msec
    
    --------------------------------
    Count of Test-Entries: 1000 
    
    Array: 147.2 msec
    
    Collection: 1134.5 msec
    
    CollectionEx: 1107.5 msec
    
    RC5: 4.7 msec

  8. #8
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,401

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    cTest_Array.cls is missing from your ZIP.

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by jpbro View Post
    cTest_Array.cls is missing from your ZIP.
    jpbro, I've re-uploaded the attachment, thanks for reminding.

  10. #10
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,401

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Couple of things I see - your Array test is calling ReDim preserve on each Add call, so that's a big bottleneck.

    Your Array and VB Collection tests are doing the sorting on Add, and I suspect that algorithm is not efficient compared to Olaf's sort on Add mechanism for the RC5 cCollection class.

    I tried outputting the keys to the debug window for the Array and RC5 Collection classes after running each test, and they appear to be in order so I suspect your tests are correct, the non-RC5 ones just aren't as optimized as they could be.

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by jpbro View Post
    Couple of things I see - your Array test is calling ReDim preserve on each Add call, so that's a big bottleneck.
    Yes, I seem to have seen an alternative to Redim, but I don't remember.

    Quote Originally Posted by jpbro View Post
    Your Array and VB Collection tests are doing the sorting on Add, and I suspect that algorithm is not efficient compared to Olaf's sort on Add mechanism for the RC5 cCollection class.
    It is to ensure I get a sorted list that the Array and VB Collection tests are doing the sorting on Add . RC5 is already sorted internally when the add operation is performed, so don't need to do sorting on Add of RC5.

    Quote Originally Posted by jpbro View Post
    I tried outputting the keys to the debug window for the Array and RC5 Collection classes after running each test, and they appear to be in order so I suspect your tests are correct, the non-RC5 ones just aren't as optimized as they could be.
    Thanks for reminding. I'll check the auto-generated Keys and try to find a better Random-Key algorithm.

    None-optimization is to test the efficiency of programs in "simple" programming. That means no additional algorithms are required.

    If the sorting algorithm of Array is optimized, the speed should be much higher. However, I suspect that even with the optimization of the VB Collection sorting algorithm, there is only a very limited increase in speed when there is a lot of data. Because VB-Collection search efficiency is too low, especially for VB_Collection.Item(i).

    If other people are interested, I'm looking forward to seeing different optimization algorithms.
    Last edited by dreammanor; Dec 20th, 2017 at 12:40 PM.

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

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by dreammanor View Post
    Yes, I seem to have seen an alternative to Redim, but I don't remember.
    One alternative would be to ReDim once at the beginning with a large-ish UBound, then track the actual count in a module level variable. As you add items, bump up the count variable. If you go over the UBound, then ReDim another "chunk". When you are done you can always shrink the array to recover memory.

  13. #13

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by jpbro View Post
    One alternative would be to ReDim once at the beginning with a large-ish UBound, then track the actual count in a module level variable. As you add items, bump up the count variable. If you go over the UBound, then ReDim another "chunk". When you are done you can always shrink the array to recover memory.
    This is a very clever way, I'll try it now, thanks.

  14. #14

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    jpbro, I've modified the code as you said, but the speed is not much improved.

    Code:
        If m_nUDTCount = 0 Then
            ReDim m_arrUDT(10000) As typeUDT
        ElseIf m_nUDTCount > UBound(m_arrUDT) Then
            ReDim Preserve m_arrUDT(m_nUDTCount + 10000) As typeUDT
        End If
        
        m_nUDTCount = m_nUDTCount + 1
        
        'ReDim Preserve m_arrUDT(m_nUDTCount - 1) As typeUDT

  15. #15
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Why not just throw away the UDTs entirely and use a fabricated ADO Recordset?

    You can index any needed "key" fields by assigning the "Optimize" property's Value on these fields = True. Then you get sorting fast enough for most purposes, filtering, find, etc.

    It isn't 1985 any more. Why fiddle with the creaky ancient UDTs for this stuff?

  16. #16

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by dilettante View Post
    Why not just throw away the UDTs entirely and use a fabricated ADO Recordset?

    You can index any needed "key" fields by assigning the "Optimize" property's Value on these fields = True. Then you get sorting fast enough for most purposes, filtering, find, etc.

    It isn't 1985 any more. Why fiddle with the creaky ancient UDTs for this stuff?
    Hi, dilettante, glad to see your reply. I'm working on audio data now, so I didn't think of using the ADO RecordSet. But ADO RecordSet is indeed a very interesting way. I'll test this method tomorrow.

    Although I've adopted Olaf's solution, I'm very surprised why other solutions are so much lower than Olaf's.

  17. #17
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    894

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    sorting pointers-to is the faster option... not the actual objects. It is prefferable for temporary sortage for the output of results.

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Hi,

    I would also like dilettante said use a fab.Recordset.

    but here a Blast from the Past
    put this in a Modul

    Code:
    Option Explicit
    
    Public Type myUDTType
       Name As String
       Vorname As String
       Alter As Long
    End Type
    
    Public myUDT() As myUDTType
    
    'Sortieren des UDT
    Public Function myUDTSort(UDT() As myUDTType, FieldNumber As Long) As myUDTType()
    
       Dim wUDT() As myUDTType
       Dim WA() As String, s As String, sFormat As String
       Dim i As Long, j As Long, k As Long
       
          i = UBound(UDT)
          ReDim wUDT(i)
          ReDim WA(i)
          
          'Formatlänge bestimmen
          sFormat = UBound(UDT)
          i = Len(sFormat)
          sFormat = String(i, "0")
          
          For i = 0 To UBound(UDT)
             Select Case FieldNumber
                Case 1
                   WA(i) = UDT(i).Name & " " & UDT(i).Vorname & _
                           Format(UDT(i).Alter, "000")
                Case 2
                   WA(i) = UDT(i).Vorname & " " & UDT(i).Name & _
                           Format(UDT(i).Alter, "000")
                Case Else
                   WA(i) = Format(UDT(i).Alter, "000") & _
                           UDT(i).Name & " " & UDT(i).Vorname
             End Select
             WA(i) = WA(i) & Format(i, sFormat)
          Next
       
          'sortieren WorkArray
          QuickSort WA, 0, UBound(WA)
    
          'UDT sortiert aufbauen
          j = Len(sFormat)
          For i = 0 To UBound(WA)
             k = Val(Right(WA(i), j))
             wUDT(i) = UDT(k)
          Next
          
          'sortiertes UDT übergeben
          myUDTSort = wUDT
    End Function
    
    Private Sub QuickSort(toSortArray() As String, _
                 ByVal LB As Single, ByVal UB As Single)
    
       Dim P1 As Single
       Dim P2 As Single
       Dim Ref As String
       Dim TEMP As String
    
          P1 = LB
          P2 = UB
          Ref = toSortArray((P1 + P2) / 2)
    
          Do
             Do While (toSortArray(P1) < Ref)
                P1 = P1 + 1
             Loop
    
             Do While (toSortArray(P2) > Ref)
                P2 = P2 - 1
             Loop
    
             If P1 <= P2 Then
                TEMP = toSortArray(P1)
                toSortArray(P1) = toSortArray(P2)
                toSortArray(P2) = TEMP
                  
                P1 = P1 + 1
                P2 = P2 - 1
             End If
          Loop Until (P1 > P2)
    
          If LB < P2 Then Call QuickSort(toSortArray, LB, P2)
          If P1 < UB Then Call QuickSort(toSortArray, P1, UB)
    End Sub
    and this in a Form, with 4 Commandbuttons and 2 Listboxes
    Code:
    '---------------------------------------------------
    '4 Commandbuttons
    '2 Listboxes
    '1 Module
    '----------------------------------------------------
    
    
    Option Explicit
    
    Private Declare Function timeGetTime Lib "winmm.dll" () As Long
    Private StartTime As Long
    Private EndTime As Long
    
    Private Sub Command1_Click()
    
       Dim i As Long
       
          StartTime = timeGetTime
          myUDT = myUDTSort(myUDT, 1)
          EndTime = timeGetTime
          Debug.Print GetLaufzeit(StartTime, EndTime)
          
          List2.Clear
          For i = 0 To UBound(myUDT)
             List2.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    Private Sub Command2_Click()
    
       Dim i As Long
       
          StartTime = timeGetTime
          myUDT = myUDTSort(myUDT, 2)
          EndTime = timeGetTime
          Debug.Print GetLaufzeit(StartTime, EndTime)
          
          List2.Clear
          For i = 0 To UBound(myUDT)
             List2.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    Private Sub Command3_Click()
    
       Dim i As Long
          
          StartTime = timeGetTime
          myUDT = myUDTSort(myUDT, 3)
          EndTime = timeGetTime
          Debug.Print GetLaufzeit(StartTime, EndTime)
          
          List2.Clear
          For i = 0 To UBound(myUDT)
             List2.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    Private Sub Command4_Click()
    
       Dim Ctl As Control
       
          On Error Resume Next
          For Each Ctl In Me.Controls
             Debug.Print Ctl.Name & ".Move " & _
                         Ctl.Left & ", " & _
                         Ctl.Top & ", " & _
                         Ctl.Width & "," & _
                         Ctl.Height
          Next
          On Error GoTo 0
    End Sub
    
    Private Sub Form_Load()
    
       Dim i As Long, j As Long
    
          Me.Height = 4950
          Me.Width = 9720
          Me.Top = (Screen.Height - Me.Height) / 2
          Me.Left = (Screen.Width - Me.Width) / 2
    
          'Controls anordnen
          Command4.Move 3720, 3840, 1095, 435
          Command3.Move 2520, 3840, 1095, 435
          Command2.Move 1320, 3840, 1095, 435
          Command1.Move 120, 3840, 1095, 435
          List2.Move 4860, 240, 4515, 2985
          List1.Move 120, 240, 4515, 2985
          
          Command1.Caption = "Sort 1"
          Command2.Caption = "Sort 2"
          Command3.Caption = "Sort 3"
          Command4.Visible = False
          
          Randomize
          ReDim myUDT(10000)
          For i = 0 To UBound(myUDT)
             myUDT(i).Name = String(10, Int(Rnd * 26 + 65))
             myUDT(i).Vorname = String(10, Int(Rnd * 26 + 65))
             myUDT(i).Alter = Int(Rnd * 30 + 10)
          Next
          
          For i = 0 To UBound(myUDT)
             List1.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    'Laufzeit in hhh:nn:ss,mmm, gemessen mit API timeGetTime
    Public Function GetLaufzeit(StartTime As Long, EndTime As Long) As String
    
       Dim LZ As Long
       Dim Stunden As Long
       Dim Minuten As Long
       Dim Sekunden As Long
       Dim MilliSekunden As Long
       
          LZ = EndTime - StartTime
          MilliSekunden = LZ Mod 1000
          Sekunden = LZ \ 1000
          Minuten = Sekunden \ 60
          Stunden = Minuten \ 60
          Minuten = Minuten Mod 60
          Sekunden = Sekunden Mod 60
          GetLaufzeit = Stunden & ":" & Format(Minuten, "00") & ":" & _
                        Format(Sekunden, "00") & "," & _
                        Format(MilliSekunden, "000")
    End Function



    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  19. #19

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by flyguille View Post
    sorting pointers-to is the faster option... not the actual objects. It is prefferable for temporary sortage for the output of results.
    Thank you for your reply. Sorting pointers-to is a good idea, but it requires additional sorting algorithms. Temporary sortage for the output of results is also an option, though it can complicate things.

  20. #20

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by ChrisE View Post
    Hi,

    I would also like dilettante said use a fab.Recordset.

    but here a Blast from the Past
    put this in a Modul

    Code:
    Option Explicit
    
    Public Type myUDTType
       Name As String
       Vorname As String
       Alter As Long
    End Type
    
    Public myUDT() As myUDTType
    
    'Sortieren des UDT
    Public Function myUDTSort(UDT() As myUDTType, FieldNumber As Long) As myUDTType()
    
       Dim wUDT() As myUDTType
       Dim WA() As String, s As String, sFormat As String
       Dim i As Long, j As Long, k As Long
       
          i = UBound(UDT)
          ReDim wUDT(i)
          ReDim WA(i)
          
          'Formatlänge bestimmen
          sFormat = UBound(UDT)
          i = Len(sFormat)
          sFormat = String(i, "0")
          
          For i = 0 To UBound(UDT)
             Select Case FieldNumber
                Case 1
                   WA(i) = UDT(i).Name & " " & UDT(i).Vorname & _
                           Format(UDT(i).Alter, "000")
                Case 2
                   WA(i) = UDT(i).Vorname & " " & UDT(i).Name & _
                           Format(UDT(i).Alter, "000")
                Case Else
                   WA(i) = Format(UDT(i).Alter, "000") & _
                           UDT(i).Name & " " & UDT(i).Vorname
             End Select
             WA(i) = WA(i) & Format(i, sFormat)
          Next
       
          'sortieren WorkArray
          QuickSort WA, 0, UBound(WA)
    
          'UDT sortiert aufbauen
          j = Len(sFormat)
          For i = 0 To UBound(WA)
             k = Val(Right(WA(i), j))
             wUDT(i) = UDT(k)
          Next
          
          'sortiertes UDT übergeben
          myUDTSort = wUDT
    End Function
    
    Private Sub QuickSort(toSortArray() As String, _
                 ByVal LB As Single, ByVal UB As Single)
    
       Dim P1 As Single
       Dim P2 As Single
       Dim Ref As String
       Dim TEMP As String
    
          P1 = LB
          P2 = UB
          Ref = toSortArray((P1 + P2) / 2)
    
          Do
             Do While (toSortArray(P1) < Ref)
                P1 = P1 + 1
             Loop
    
             Do While (toSortArray(P2) > Ref)
                P2 = P2 - 1
             Loop
    
             If P1 <= P2 Then
                TEMP = toSortArray(P1)
                toSortArray(P1) = toSortArray(P2)
                toSortArray(P2) = TEMP
                  
                P1 = P1 + 1
                P2 = P2 - 1
             End If
          Loop Until (P1 > P2)
    
          If LB < P2 Then Call QuickSort(toSortArray, LB, P2)
          If P1 < UB Then Call QuickSort(toSortArray, P1, UB)
    End Sub
    and this in a Form, with 4 Commandbuttons and 2 Listboxes
    Code:
    '---------------------------------------------------
    '4 Commandbuttons
    '2 Listboxes
    '1 Module
    '----------------------------------------------------
    
    
    Option Explicit
    
    Private Declare Function timeGetTime Lib "winmm.dll" () As Long
    Private StartTime As Long
    Private EndTime As Long
    
    Private Sub Command1_Click()
    
       Dim i As Long
       
          StartTime = timeGetTime
          myUDT = myUDTSort(myUDT, 1)
          EndTime = timeGetTime
          Debug.Print GetLaufzeit(StartTime, EndTime)
          
          List2.Clear
          For i = 0 To UBound(myUDT)
             List2.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    Private Sub Command2_Click()
    
       Dim i As Long
       
          StartTime = timeGetTime
          myUDT = myUDTSort(myUDT, 2)
          EndTime = timeGetTime
          Debug.Print GetLaufzeit(StartTime, EndTime)
          
          List2.Clear
          For i = 0 To UBound(myUDT)
             List2.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    Private Sub Command3_Click()
    
       Dim i As Long
          
          StartTime = timeGetTime
          myUDT = myUDTSort(myUDT, 3)
          EndTime = timeGetTime
          Debug.Print GetLaufzeit(StartTime, EndTime)
          
          List2.Clear
          For i = 0 To UBound(myUDT)
             List2.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    Private Sub Command4_Click()
    
       Dim Ctl As Control
       
          On Error Resume Next
          For Each Ctl In Me.Controls
             Debug.Print Ctl.Name & ".Move " & _
                         Ctl.Left & ", " & _
                         Ctl.Top & ", " & _
                         Ctl.Width & "," & _
                         Ctl.Height
          Next
          On Error GoTo 0
    End Sub
    
    Private Sub Form_Load()
    
       Dim i As Long, j As Long
    
          Me.Height = 4950
          Me.Width = 9720
          Me.Top = (Screen.Height - Me.Height) / 2
          Me.Left = (Screen.Width - Me.Width) / 2
    
          'Controls anordnen
          Command4.Move 3720, 3840, 1095, 435
          Command3.Move 2520, 3840, 1095, 435
          Command2.Move 1320, 3840, 1095, 435
          Command1.Move 120, 3840, 1095, 435
          List2.Move 4860, 240, 4515, 2985
          List1.Move 120, 240, 4515, 2985
          
          Command1.Caption = "Sort 1"
          Command2.Caption = "Sort 2"
          Command3.Caption = "Sort 3"
          Command4.Visible = False
          
          Randomize
          ReDim myUDT(10000)
          For i = 0 To UBound(myUDT)
             myUDT(i).Name = String(10, Int(Rnd * 26 + 65))
             myUDT(i).Vorname = String(10, Int(Rnd * 26 + 65))
             myUDT(i).Alter = Int(Rnd * 30 + 10)
          Next
          
          For i = 0 To UBound(myUDT)
             List1.AddItem myUDT(i).Name & ", " & myUDT(i).Vorname & _
                           ", " & myUDT(i).Alter
          Next
    End Sub
    
    'Laufzeit in hhh:nn:ss,mmm, gemessen mit API timeGetTime
    Public Function GetLaufzeit(StartTime As Long, EndTime As Long) As String
    
       Dim LZ As Long
       Dim Stunden As Long
       Dim Minuten As Long
       Dim Sekunden As Long
       Dim MilliSekunden As Long
       
          LZ = EndTime - StartTime
          MilliSekunden = LZ Mod 1000
          Sekunden = LZ \ 1000
          Minuten = Sekunden \ 60
          Stunden = Minuten \ 60
          Minuten = Minuten Mod 60
          Sekunden = Sekunden Mod 60
          GetLaufzeit = Stunden & ":" & Format(Minuten, "00") & ":" & _
                        Format(Sekunden, "00") & "," & _
                        Format(MilliSekunden, "000")
    End Function



    regards
    Chris
    Hi ChrisE, the code you provided is very useful, thank you very much.

    Fabricated ADO Recordset is a good idea, vbRichClient5.cMemDB and vbRichClient5.cRecordSet can also process datasets very efficiently in memory. But I don't want to use the database solution for the time being. I'll test these methods again if I have a chance in the future.
    Last edited by dreammanor; Dec 20th, 2017 at 01:34 PM.

  21. #21

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    @jpbro,
    after I added Randomize to the test code, the system now generates different random numbers each time, but the test results are the same as before, so I think the test results should be valid.

    @Olaf,
    about RC5.cCollection, I have two questions:
    (1) How to determine whether a key exists in the vbRichClient5.cCollection?
    (2) How to get a item object from vbRichClient5.cCollection based on a item key?

    mCol.ItemExists (DataSize)
    is normal for the first call, and the second time it's called, the error message is popped up: Run-time error '438': Object doesn't support this property or method.

    So I use mCol.IndexByKey (DataSize, True) to determine whether a key exists in the RC5.cCollection, but when DataSize (key) doesn't exist, IndexByKey returns 0, I think it should return -1 because 0 is valid index.


    (Edit:Sorry, I should use the method Exists instead of the method ItemExists. No problem now. Thank you jpbro)
    Last edited by dreammanor; Dec 20th, 2017 at 03:51 PM.

  22. #22
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,401

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Use the Exists method to test against the Keys - ItemExists is for search Items not Keys.

    Since DataSize is a Long you are may be running into an issue where the CCollection class assumes you want to get the item by Index. I think mCol.Exists(Cstr(DataSize)) and mCol.Item(Cstr(DataSize)) will work. Or perhaps use a string prefix for your keys (e.g. "Key_" & DataSize)?

  23. #23

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by jpbro View Post
    Use the Exists method to test against the Keys - ItemExists is for search Items not Keys.

    Since DataSize is a Long you are may be running into an issue where the CCollection class assumes you want to get the item by Index. I think mCol.Exists(Cstr(DataSize)) and mCol.Item(Cstr(DataSize)) will work. Or perhaps use a string prefix for your keys (e.g. "Key_" & DataSize)?
    I'm so blind that I didn't see the Exists method. The Exists method is valid, thank you so much, jpbro.

    In addition, I use mCol.IndexByKey (DataSize, True) to determine whether a key exists in the RC5.cCollection, but when DataSize (key) doesn't exist, IndexByKey returns 0, I think it should return -1 because 0 is valid index.

    The definition of IndexByKey is as follows:

    Property IndexByKey(Key, [Force_EvenIfNonExistent As Boolean]) As Long
    Last edited by dreammanor; Dec 20th, 2017 at 02:18 PM.

  24. #24

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    It doesn't matter, I use the following methods to meet my needs:

    Code:
    Public Function IndexByKey(Key) As Long
        On Error GoTo ErrIndex
        
        IndexByKey = mCol.IndexByKey(Key, False)
        
        Exit Function
        
    ErrIndex:
        IndexByKey = -1
    End Function
    Again, the properties ItemBySortKeyIndex, SortKeyIndexByIndex and SortKeyIndexByKey are really useful.

    Thank you, Olaf, jpbro and all the people.
    Last edited by dreammanor; Dec 21st, 2017 at 02:51 AM.

  25. #25
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,207

    Re: Sorting-Array and Sorting-Collection, which one is faster?

    Quote Originally Posted by jpbro View Post
    Use the Exists method to test against the Keys - ItemExists is for search Items not Keys.
    Yep.

    Quote Originally Posted by jpbro View Post
    Since DataSize is a Long you are may be running into an issue where the CCollection class assumes you want to get the item by Index. I think mCol.Exists(Cstr(DataSize)) and mCol.Item(Cstr(DataSize)) will work. Or perhaps use a string prefix for your keys (e.g. "Key_" & DataSize)?
    The cCollection (as well as the cSortedDictionary) both allow (besides String-Keys) also:
    - "Integer-Keys" ( of Type Currency, Long, Integer, Byte) -> all represented in the "longest Integer-Type" internally (a 64Bit VB-Currency)
    - and "FloatingPoint-Keys" (of Type Double, Single, Date) -> same rule as above - the longest Float-Type here being the (64Bit) VB-Double
    (mainly for speed-reasons in the internal Binary-Search-Algo, but those "non-String-Keys" also save a bit of memory internally).

    The only rule you have to keep in mind is, that - as soon as you have choosen a certain Type of Key (by adding the first Key/Value pair),
    you should "stick to it" (to not run into surprising effects with "mixed Keys").
    If you want to change the Type of Key, either do a RemoveAll - or create a new Instance.

    Olaf

Posting Permissions

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



Click Here to Expand Forum to Full Width