Solved

Quicker Array Sorting

Posted on 2013-11-23
7
497 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export Data to Different .csv Files 26 103
Replace Dates in query 14 38
RUNRMTCMD from AS/400 12 43
Better way to make a query with date filter. 5 22
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

813 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

9 Experts available now in Live!

Get 1:1 Help Now