Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Quicker Array Sorting

Posted on 2013-11-23
Medium Priority
508 Views
Last Modified: 2013-11-23
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
Set rs = Server.CreateObject("ADODB.Recordset")
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
Question by:Bob Schneider
7 Comments

LVL 35

Assisted Solution

Dan Craciun earned 400 total points
ID: 39671326
Here's a comparison between sorting methods: http://www.cprogramming.com/tutorial/computersciencetheory/sortcomp.html

HTH,
Dan
0

LVL 54

Assisted Solution

Scott Fell,  EE MVE earned 1200 total points
ID: 39671394
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

LVL 54

Assisted Solution

Scott Fell,  EE MVE earned 1200 total points
ID: 39671404
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

Author Comment

ID: 39671410
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

LVL 50

Assisted Solution

Martin Liss earned 400 total points
ID: 39671441
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

LVL 54

Accepted Solution

Scott Fell,  EE MVE earned 1200 total points
ID: 39671444
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!"
Set f = fso.OpenTextFile("c:\testfile.txt", ForReading)
WriteLineToFile = f.ReadAll
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

Author Closing Comment

ID: 39671449
Top shelf as always!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can launâ€¦
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process fromâ€¦
###### Suggested Courses
Course of the Month12 days, 11 hours left to enroll

#### 579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.