Solved

Quicker Array Sorting

Posted on 2013-11-23
7
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 35

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 47

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach 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.
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

735 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