Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Case Through a Range? 2

Status
Not open for further replies.
dlongnecker

dlongnecker

MIS
Dec 27, 2001
114
US
Background:
I have 5 pages of matricies for percentile rankings based on age in months that, unfortunately, has no mathematical calculation--it's just matching one score to another.

Ex. if a 36 month old got a score of 10, they have a percentile of 10, etc..

I have individuals inputting the birthdates, scores, etc into an ASP-based online form to be stored in a database. I'm then figuring percentiles and a few other calculations and storing them as well...

Base Language:
ASP 3.0, VB

Issues:
I'm using select case statements to these percentile rankings; however, there are thousands...

Example:
Code: 
select case student_age
	case 36
		select case motor_total
			case 0
				motor_percentile = 14
			case 1
				motor_percentile = 22
			case 2
				motor_percentile = 34	

			case 3
				motor_percentile = 47			
			case 4
				motor_percentile = 61			
			case 5
				motor_percentile = 71			
			case 6
				motor_percentile = 81			
			case 7
				motor_percentile = 88			
			case 8
				motor_percentile = 93
			case 9
				motor_percentile = 96
			case 10
				motor_percentile = 98
			case 11
				motor_percentile = 99
			case 12
				motor_percentile = 99
			case 13
				motor_percentile = 99
			case 14
				motor_percentile = 99
			case 15
				motor_percentile = 99
			case 16
				motor_percentile = 99
			case 17
				motor_percentile = 99
			case 18
				motor_percentile = 99
			case 19
				motor_percentile = 99
			case 20
				motor_percentile = 99
			case 21
				motor_percentile = 99
			case 22
				motor_percentile = 99
			case 23
				motor_percentile = 99
			case 24
				motor_percentile = 99
			case 25
				motor_percentile = 99
			case 26
				motor_percentile = 99
			case 27
				motor_percentile = 99
			case else
				motor_percentile = NA
			end select
	case 37
		select case motor_total
			case 0
				motor_percentile = 14
			case 1
				motor_percentile = 22
			case 2
				motor_percentile = 34			
			case 3
				motor_percentile = 47			
			case 4
				motor_percentile = 61			
			case 5
				motor_percentile = 71			
			case 6
				motor_percentile = 81			
			case 7
				motor_percentile = 88			
			case 8
				motor_percentile = 93
			case 9
				motor_percentile = 96
			case 10
				motor_percentile = 98
			case 11
				motor_percentile = 99
			case 12
				motor_percentile = 99
			case 13
				motor_percentile = 99
			case 14
				motor_percentile = 99
			case 15
				motor_percentile = 99
			case 16
				motor_percentile = 99
			case 17
				motor_percentile = 99
			case 18
				motor_percentile = 99
			case 19
				motor_percentile = 99
			case 20
				motor_percentile = 99
			case 21
				motor_percentile = 99
			case 22
				motor_percentile = 99
			case 23
				motor_percentile = 99
			case 24
				motor_percentile = 99
			case 25
				motor_percentile = 99
			case 26
				motor_percentile = 99
			case 27
				motor_percentile = 99
			case else
				motor_percentile = NA
			end select
	case else
		motor_percentile = 0
end select

That gives ages 36 and 37... it goes to 82. Also, this is for one subject (motor), there is 2 other areas.

46 age brackets * 28 options per bracket * 3 subjects = 3,864 possibilities in a HUGE select case.

I have serious performance concerns... is there a better/faster/more efficient way to this? I'm open to ideas and suggestions.

Thanks in advance!

-David

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259
 
Sort by date Sort by votes
If you were to put the motor percentiles into arrays in a function, then you could use something like this:

Code: 
motor_percentile = GetMotorPercentile(student_age, motor_total)

Function GetMotorPercentile(studentage, motortotal)

Dim percentiles(82), agepercentile, minimumage

minimumage = 36

percentiles(36) = Array(14,22,34,47,61,71,81,88,93,96,98,99,99,99,99,99,99,99,99,99,99,99,99,99,99,99,99,99)
percentiles(37) = Array(14,22,34,47,61,71,81,88,93,96,98,99,99,99,99,99,99,99,99,99,99,99,99,99,99,99,99,99)

If studentage <= UBound(percentiles) And studentage >= minimumage Then
  agepercentile = percentiles(studentage)
  If motortotal <= UBound(agepercentile) Then
    GetMotorPercentile = agepercentile(motortotal)
  Else
    GetMotorPercentile = "NA"
  End If
Else
  GetMotorPercentile = 0
End If

End Function

Lee
 
Upvote 0 Downvote
My first suggestion would be to build a database of all of the information and simply select against it. I could see having all of your information in a few tables and letting the more optimized database or database drivers do the real work. if you don't want to install a full-blown databas, MS Access shold be fine in this situation. if that is not an option you could always just build it as a big CSV file and use the text ADO driver to treat it as a small db (while still getting the better performance of a compiled drivere).
I don't have enough information to suggest a good database design for a multi-table approach. If you were going to use a flat file I would probably suggest buiding one line per combination (which it sounds like you have already in your code, some search and replace in your future :p)

If you feel like providing a little moe info on your select case statement (ie, like whether all three subjects are related, etc) I could probably give you more specific advice.

-T

barcode_1.gif
 
Upvote 0 Downvote
Definitely not something you want to hand code, so my example was just to show a way to store the data in memory using the original data given. I'd suggest at least reading it from a text file, or multiple text files.

Lee
 
Upvote 0 Downvote
Lee: yeah, I started typing a similar response before I realized that as well :) The other downside to using an array would be the memory usage tat would need to be referenced on every call to the page. While it isn't necessarally a whole lot ('I've had worse', it would likely still be better to go with a flat file or database that you can connect to via an ADO connection. Hmm...I think I smell another benchmark on the horizon ;)

-T

barcode_1.gif
 
Upvote 0 Downvote
But it would be a small table. Why not just create a table (which is what you actually have here) that has all that info in it.
The three fields I see are Student_Age, Motor_Total and Motor_Percentile. Then to return a value you just use a select statement to pull out the info you want.
"Select Motor_Percentile From TableName Where Student_Age = FormField1 And Motor_Total = FormField2"

The syntax would have to be written for your specific pages, but this should solve any performance issues.


Paul
 
Upvote 0 Downvote
Sorry, I must be a slower typer than I thought. I obviously like Tarwn's answer.

Paul
 
Upvote 0 Downvote
Haha, wow, lots of replies just in the drive home!

I tried the array idea, but that got pretty messy...

The database idea is brilliant... I normally shove _everything_ into a database... the horrors that a long week can do to a programmer and cause brain numb, apparently.

Thanks for all the responses and the brain jolt! I can't wait to get it all hooked up and taken care of Monday morning! Kudos to both of you!

Regards,

David

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259
 
Upvote 0 Downvote
Okay, thanks to everyone's help, I built a simple query:

Code: 
strSQL = ""
strSQL = "SELECT motor_percentile FROM " & motor_table & " WHERE student_age = " & student_age & " AND motor_total = " & motor_total & ""
Set rs = cnn.Execute(strSQL)

While Not rs.EOF
	motor_percentile = rs("motor_percentile") ' Grab first record, push into variable.
	response.write(motor_percentile) ' Write it out real quick so I can verify (remove this later).
Wend
	motor_percentile = "NA"  ' Nothing there or BOF/EOF error, give an NA.

cnn.Close
Set cnn = Nothing

However, while I can run the SQL statement just fine in Query Analyzer, I'm getting timeout errors on the web page itself:

Active Server Pages error 'ASP 0113'

Script timed out

/dial3/addentry.asp

The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.
Click to expand...

The timeout is set to 60 seconds... and, right now, it's only processing one total--it should be almost instant considering the pipe between the database server and the web server (and the horsepower of both).

Edit : The database connectivity is taken care of using an include statement that I'm using throughout the site. That functionality is working already.

Ideas?

Thanks!

-David

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259
 
Upvote 0 Downvote
The first thing you might want to try is

Code: 
strSQL = "SELECT motor_percentile FROM " & motor_table & " WHERE student_age = " & student_age & " AND motor_total = " & motor_total & ""
Response.Write strSQL

See if you are getting the values back you expect for your variables. Also, check the syntax. This would be my interpretation of the syntax based on txt, int and int datatypes.
Code: 
strSQL = "SELECT motor_percentile FROM '" & motor_table & "' WHERE student_age = " & student_age & " AND motor_total = " & motor_total & ""

Note the single quote marks I've added around & motor_table.

Paul
 
Upvote 0 Downvote
Actually, I think before you try the above you may want to double check your recordset loop. I think you forgot the rs.MoveNext, so even with a print out of the SQL statement you will still be in an endless loop later on that will only occur when your SQL executes correctly.

Ie, because your SQL is alright your ending up in an endles loop, so your SQL is syntactically correct and you just need to increment your recordset pointer:
Code: 
strSQL = ""
strSQL = "SELECT motor_percentile FROM " & motor_table & " WHERE student_age = " & student_age & " AND motor_total = " & motor_total & ""
Set rs = cnn.Execute(strSQL)

While Not rs.EOF
    motor_percentile = rs("motor_percentile") ' Grab first record, push into variable.
    response.write(motor_percentile) ' Write it out real quick so I can verify (remove this later).
    [highlight]rs.MoveNext[/highlight]
Wend
    motor_percentile = "NA"  ' Nothing there or BOF/EOF 
error, give an NA.

cnn.Close
Set cnn = Nothing

Also, the way you have your code right now you are going to always have a final value of NA. You should probably place that in an if statement instead of sitting right after your while loop. The way it is now, that assignment will execute every single time you leave the loop (once you fix the missing MoveNext :) ).

-T

barcode_1.gif
 
Upvote 0 Downvote
Tarwn-

Yep, your solution with the MoveNext in the wrong spot was right--sorry for the long delay... finally managed to figure that out on my own while roamin' mobile. I still ran into a few other issues (not the NA issue), but rewrote it:

Code: 
strSQL = ""
strSQL = "SELECT language_percentile FROM " & language_table & " WHERE student_age = " & student_age & " AND language_total = " & language_total & ""
Set rs = cnn.Execute(strSQL)

If Not rs.EOF then
	language_percentile = rs("language_percentile")
Else
	language_percentile = "NA"
End if

I decided not to use the While Not because there can only be one entry (primary key field), so, the first is always (by the logic of the program) the entry that I'm looking for. So far, this solution is working beautifully.

Thanks for everyone's help and suggestions!

Regards,

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
536
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
538
leifoet
leifoet
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
543
leifoet
leifoet
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
967
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
471
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top