Solved

Quicker Array Sorting

Posted on 2013-11-23
7
494 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

Open in new window

0
Comment
Question by:Bob Schneider
7 Comments
 
LVL 34

Assisted Solution

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

HTH,
Dan
0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 300 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 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 300 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Bob Schneider
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 45

Assisted Solution

by:Martin Liss
Martin Liss earned 100 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

Open in new window

0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 300 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

Open in new window

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

Open in new window


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

by:Bob Schneider
ID: 39671449
Top shelf as always!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard 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.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now