1. Registered User
Join Date
May 2006
Posts
65

Hi all,

until recently ive been using a rank equation to calculate rank,

essentially doing a select statement and selecting this as the rank field, where query 2 is the same as query 1:

((select count(*) from (query1) where (query1).value < (query2).value) +1)) as Rank

problem is that this is now running like a dog (takes 10 secs) and i'd like to try and do this another way- 2005 has a rank function, how can i do this in 2000?

here is the full statement :

SELECT StudentId, GCSE_Score, LTRIM(STR
((SELECT COUNT(*)
FROM dbo.[Score2004-05]
WHERE GCSE_score < s.GCSE_Score) + 1)) AS GCSE_Rank, SetId
FROM dbo.[Score2004-05] S
WHERE (SetId = '2004/2005')

greg

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Well, I'd dump the LTRIM/STR first, 'cause I don't know why you would want to convert a perfectly good number to a useless string.

Then, you can give this alternate method a shot, though it generally runs the same speed as the method you have tried:
Code:
```SELECT	StudentId,
GCSE_Score,
count(*) AS GCSE_Rank,
SetId
FROM	dbo.[Score2004-05] S
inner join dbo.[Score2004-05] T	on s.GCSE_score >= t.GCSE_Score
WHERE	SetId = '2004/2005'
group by StudentId,
GCSE_Score,
SetId```
I assume that GCSE_score is indexed?

Another method that can be used in a stored procedure is to load all your data into a temporary table with an IDENTITY value in sorted order. Then select from the temporary table as output.
This is usually pretty fast.

3. Registered User
Join Date
May 2006
Posts
65
hi,

ah the string conversion is because im combining the value into a string in the next query but will do the convesrion at that stage rather at the stage when it's trying to work out the rank.

GCSE_SCORE isnt indexed because its a view which combines data from my non-indexed database (yes the db i ref is not indexed! not my doing, long story!)- other than that i would have tried the tored procedure method but didnt want to have to create a table, i wanted the ranks to automatically update when ever a value was changed in the main data.

so will try that other method, thanks for showing me this

greg

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
You don't have to create a table. Just a temporary table (you know, with the "#" designation?)
Without indexes, the temptable/sproc route is your best bet for performance.

5. Registered User
Join Date
May 2006
Posts
65
you know, i didnt know that- excellent!! im dead happy with that...im off to experiment with temp tables.

greg

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•