Rewrite JOIN-query to Subquery / faster variant-CodePudding

I have two tables, one called shots with 9m rows and one called holes with 50k rows. My DBMS is SQLite.

The goal is to get replace the zero values for x, y, z in shots with the returned values from holes. I have the following query that has been running the whole day:

UPDATE shots
SET x = h.hole_x,
    y = h.hole_y,
    z = h.hole_z
FROM holes h
LEFT OUTER JOIN shots s
ON h.tournament = s.tournament
   AND h.course = s.course
   AND h.year = s.year
   AND h.round = s.round
   AND h.hole = s.hole
WHERE s.end = 'hole'
   AND s.x = '0.0'
   AND s.y ='0.0'
   AND s.z = '0.0'
   AND h.hole_x != '0.0'
   AND h.hole_y != '0.0'
   AND h.hole_z != '0.0'

I was reading here that a Subquery could be up to 260x faster than the JOIN. How do I rewrite my query so it becomes faster?

CodePudding user response：

Your main problem here is that you are doing an unnecessary join to holes which you must remove.

This is the correct syntax for a join-like UPDATE statement in SQLite:

UPDATE shots AS s
SET x = h.hole_x,
    y = h.hole_y,
    z = h.hole_z
FROM holes AS h
WHERE h.tournament = s.tournament AND h.course = s.course 
  AND h.year = s.year AND h.round = s.round AND h.hole = s.hole
  AND s.end = 'hole' AND s.x = '0.0' AND s.y = '0.0' AND s.z = '0.0' 
  AND h.hole_x <> '0.0' AND h.hole_y <> '0.0' AND h.hole_z <> '0.0';

Also, why do you compare against '0.0'? If the columns hole_? are numeric then you should compare them against 0.0 or just 0.