x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 512

# Quicker Array Sorting

Here  is some code I have for populating an array and then sorting it.  Can someone show me what it would look like with a quicker sorting method?

Thanks!

``````	For k = 0 to UBound(Races, 2) - 1
sql="SELECT p.ParticipantID, p.FirstName, p.LastName, rc.Bib, p.Gender, rc.Age, p.City, p.St, p.Phone, p.DOB, p.Email, rg.RaceID FROM "
sql = sql & "Participant p INNER JOIN PartReg rg ON p.ParticipantID = rg.ParticipantID JOIN PartRace rc "
sql = sql & "ON rc.ParticipantID = p.ParticipantID WHERE rc.RaceID IN (" & sEventRaces & ") " & sOrderBy
rs.Open sql, conn, 1, 2
PartArray=rs.GetRows()
rs.Close
Set rs=Nothing
Next

'sort the array
If sSortBy = "bib" Then
For i = 0 to UBound(PartArray, 2) - 2
For j = i + 1 to UBound(PartArray, 2) - 1
If CInt(PartArray(2, i)) > CInt(PartArray(2, j)) Then
For k = 0 to 11
TempArray(k) = PartArray(k, i)
PartArray(k, i) = PartArray(k, j)
PartArray(k, j) = TempArray(k)
Next
End If
Next
Next
End If
``````
0
Bob Schneider
5 Solutions

IT ConsultantCommented:
Here's a comparison between sorting methods: http://www.cprogramming.com/tutorial/computersciencetheory/sortcomp.html

HTH,
Dan
0

Developer & EE ModeratorCommented:
The bubble sort is the easier to implement but not the fastest.  However, before spending time on which is the fastest, how much data needs to be sorted?  For small amounts of data, what you have may be fine.    I know I have seen articles on 4guys that actually compared times for this.  I found this one http://www.4guysfromrolla.com/webtech/012799-2.shtml although I know there is another somewhere with benchmarks.

I tend to do my sorting in my query where possible.  If you are dealing with a lot of data that needs to be manipulated after getrows(), I like to just use jquery/javascript on the client side as it is the fastest.
0

Developer & EE ModeratorCommented:
This is the comparison
http://www.4guysfromrolla.com/webtech/011001-1.shtml
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=83

If you keep it in vbscript, you can see the quicksort is faster for larger amounts of data.

Either way, if you log on to your server and watch the the amount of cpu/memory you may see it max out for a short time.  If the sorting is done client side via js/jquery, it is done on the users machine.

http://www.w3schools.com/jsref/jsref_sort.asp
http://learn.jquery.com/javascript-101/arrays/
0

Co-OwnerAuthor Commented:
In the past it has been small amounts of data (a couple hundred rows) but we are now finding  we have to sort larger and larger amounts of data (up to 4000 rows).  I use a vb6 app on my machine (when I am out timing a fitness event) and classic asp on my web site (which clients use).
0

Older than dirtCommented:
I apologize if this duplicates code shown in any of the referred to articles, but here is a quicksort example which is very fast.

``````Sub SortAnArrayWithQuickSort()

Dim strArray(5) As String
Dim lngIndex As Long
strArray(0) = "x"
strArray(1) = "r"
strArray(2) = "a"
strArray(3) = "z"
strArray(4) = "c"
strArray(5) = "d"

QuickSort strArray, LBound(strArray), UBound(strArray)

' Show the array after sorting
Debug.Print "AFTER"
For lngIndex = 0 To UBound(strArray)
Debug.Print strArray(lngIndex)
Next

End Sub

Private Sub QuickSort(C() As String, ByVal First As Long, ByVal Last As Long)

Dim Low As Long, High As Long
Dim MidValue As String

Low = First
High = Last
MidValue = C((First + Last) \ 2)

Do
While C(Low) < MidValue
Low = Low + 1
Wend

While C(High) > MidValue
High = High - 1
Wend

If Low <= High Then
Swap C(Low), C(High)
Low = Low + 1
High = High - 1
End If
Loop While Low <= High

If First < High Then QuickSort C, First, High
If Low < Last Then QuickSort C, Low, Last
End Sub

Private Sub QuickSort2DOnFirst(C() As String, ByVal First As Long, ByVal Last As Long)

Dim Low As Long, High As Long
Dim MidValue As String

Low = First
High = Last
MidValue = C((First + Last) \ 2, 0)

Do
While C(Low, 0) < MidValue
Low = Low + 1
Wend

While C(High, 0) > MidValue
High = High - 1
Wend

If Low <= High Then
Swap C(Low, 0), C(High, 0)
Low = Low + 1
High = High - 1
End If
Loop While Low <= High

If First < High Then QuickSort C, First, High
If Low < Last Then QuickSort C, Low, Last
End Sub
Private Sub Swap(ByRef A As String, ByRef B As String)
Dim T As String

T = A
A = B
B = T
End Sub
``````
0

Developer & EE ModeratorCommented:
That does sound like a lot of data and especially for the web, too much to throw out at once.   I would use an ajax call where your getrows is on page2.asp and your data is displayed on page1.asp.  On page1.asp you grab 100 rows that goes to a js/jquery array, present 20 or 30 rows at a time.

As the user pages and gets close to 100, send another ajax call for another 100 rows of data to build on the existing js/jquery array.

If all 4000 rows do need to be presented at once, you can still use ajax.  On page1.asp, use jquery to call x amount of records then display. On complete, call another x amount of records and build the display.

Another option to consider is to simply dynamically write a static html page.  Since the race results probably don't change after the official time, you can use asp to write out the actual page.  http://msdn.microsoft.com/en-us/library/t5399c99(v=vs.84).aspx
``````Function WriteLineToFile
Const ForReading = 1, ForWriting = 2
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("c:\testfile.txt", ForWriting, True)
f.WriteLine "Hello world!"
f.WriteLine "VBScript is fun!"
End Function
``````
``````Your actual sample may look more like:
html=""
html=html&"<h1>Results</h1>"
for result=1 to ubound(PartArray)
html=html&result&"<br>"
next

' write the html
f.WriteLine html
``````

Since the html page is written and becomes static, it is just loading time.   You can still use ajax, instead of writing the html page statically, write the data from getrows to a static json file. Then load page1.asp calling the static json file to build the table of data.  This at least cuts out calling the database multiple times.   Even statically, 4,000 rows will take some time the first time it loads.  If you allow sorting on the page, do it via jquery/js and the sorting will go quickly.  I do use datatables for my own and like the built in features.  When you implement, it is important to have well formed html that can validate and no js errors as many of these errors will prevent datatables from loading.
0

Co-OwnerAuthor Commented:
Top shelf as always!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.