CODEWITHSUNDEEP
sqlperformance
Meowbits
Meowbits

Reputation: 586

How to improve the speed of this SQL update query?

Sorry, this is my first time using this forum. Apparently people can edit my post which although helpful, has taken some information out. I will try to make it more understandable.

I am using SQL Compact 3.5 as a local database. The program is written in VB.NET.

The problem is with querying one of my tables that is taking too long.

The player table has, among other things, id, skill, school, weight, starter.

What I am trying to do is set the starter value = 'true' for the player with the highest skill at a given weight for a given school. So if there are 100 players at a school, there will be 14 starters, one for each weight.

The player table has 170,000 players, each having 1 of 14 different weights, and each belongs to 1 of 4500 schools.

Someone commented below and showed this statement which appears to be on the right track. I am a novice and have not gotten it implemented quite yet.

  "UPDATE p " &
  "SET starter = 'TRUE' " &
  "FROM player p" &
  "JOIN (" &
  "SELECT DISTINCT school, weight, MAX(skill) AS MaxSkill " &
  "FROM player " &
  "GROUP BY school, weight" &
  ") q ON q.school = p.school AND q.weight = p.weight AND q.MaxSkill =
   p.skill"

Upvotes: 1

Views: 1185

Answers (2)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

There's some minor confusion over the use of weight, as I'm assuming you you're not doing this on a per-unit basis. You may want to extract out ranges to another table, then use the id's there instead of a numeric weight.

In any case, here's a query that should work for all RDBMSs

UPDATE player a SET starter = TRUE
WHERE NOT EXISTS (SELECT '1'
                  FROM player b
                  WHERE b.school = a.school
                  AND b.weight = a.weight
                  AND b.skill > a.skill)

The inner query should return null (thus setting starter true) if:

  1. There are no other players at the school
  2. There are no players at the same school, in the same weight class
  3. There are no players with a higher skill level, for the same school and weight class

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Instead of doing a group-by-group, row-by-row approach, this update query does it all at once:

First, it gathers the highest skill for each school / weight combination.

It then joins that to the player that has the matching school / weight / skill combination, and then sets that player to the starter.

UPDATE p
SET starter = 'TRUE'
FROM player p 
JOIN (
    SELECT school, weight, MAX(skill) AS MaxSkill
    FROM player
    GROUP BY school, weight
) maxResults 
    ON maxResults.school = p.school 
    AND maxResults.weight = p.weight 
    AND maxResults.MaxSkill = p.skill

However, in the case of a tie in skill, all players with the highest skill would be set to a starter...

Upvotes: 3

Related Questions