Udi Idan Udi Idan - 1 year ago 111
SQL Question

Optimizing rank() query

I'm using the following query, within a stored procedure, to get user's ranking.

While trying to optimize the database, I’m trying to determine if this query can be optimize as it is the most used query in my database.

id column is indexed.

@p_rank = all_time_rank
RANK() OVER (ORDER BY score DESC) AS all_time_rank
user_rankings) t
id = @p_id;

Is there any way to get this query to perform better?

Answer Source

You can phrase this as:

SELECT @p_rank = COUNT(*) + 1
FROM user_rankings ur
WHERE ur.score > (SELECT ur2.score FROM user_rankings ur2 WHERE ur2.id = @pid);

You would want an index on user_rankings(id, score) and user_rankings(score).

I think this will have somewhat better performance.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download