[Solved] Optimize a query for creating a ranking in MS SQL Server
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Optimize a query for creating a ranking in MS SQL Server

Database type:

I'm creating an application where users do workouts. They pass on their results via an app, and these results are stored in an SQL Server database. Results are saved in this way in a SQL Server table:

SQL Server table

I want to write a query to create a ranking based on the best score of each user. This is what I have so far:

SELECT id, 
       workout_id, 
       level_id, 
       a.user_id, 
       total_time, 
       score, 
       datetime_added
FROM nodefit_rankings_fitness as a INNER JOIN
    (
     SELECT user_id, 
            MAX(score) AS MAXSCORE 
     FROM nodefit_rankings_fitness 
     GROUP BY user_id
    ) AS lookup
ON  lookup.user_id = a.user_id
    AND 
    lookup.MAXSCORE  =  a.score
ORDER BY score DESC, 
         datetime_added DESC

This generates this ranking:

SQL Server

The problem is that if a user has achieved the same maximum score a number of times, he will appear multiple times in the ranking. The query must be adjusted so that when a user has the same maximum score a few times, only the result of the last attempt (based on the datetime_added column) is displayed in the rankings.

Unfortunately, I cannot find a solution myself. Help is certainly appreciated.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 13): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
The optimized query:
SELECT
        id,
        workout_id,
        level_id,
        a.user_id,
        total_time,
        a.score,
        datetime_added 
    FROM
        nodefit_rankings_fitness AS a 
    INNER JOIN
        (
            SELECT
                nodefit_rankings_fitness.user_id,
                MAX(nodefit_rankings_fitness.score) AS MAXSCORE 
            FROM
                nodefit_rankings_fitness 
            GROUP BY
                nodefit_rankings_fitness.user_id
        ) AS lookup 
            ON lookup.user_id = a.user_id 
            AND lookup.MAXSCORE = a.score 
    ORDER BY
        a.score DESC,
        datetime_added DESC

Related Articles



* original question posted on StackOverflow here.