Query-volgorde

De queryprestaties verbeteren in SQL Server

Dean Smith

Founder, Atamai Analytics

Query voor zware aardbevingen

SELECT Country, Place, Magnitude
FROM Earthquakes
WHERE Magnitude >= 9
ORDER BY Magnitude DESC;

Country	Place	Magnitude
CL	Bio-Bio; Chile	9.5
US	Southern Alaska	9.2
ID	off the west coast of northern Sumatra	9.1
JP	near the east coast of Honshu; Japan	9.1
...	...	...

Syntaxisvolgorde

                                 -- Syntaxisvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

Syntaxisvolgorde

                                 -- Syntaxisvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

Syntaxisvolgorde

                                 -- Syntaxisvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;

Syntaxisvolgorde

                                 -- Syntaxisvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Verwerkingsvolgorde

                                 -- Syntaxisvolgorde | Verwerkingsvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Verwerkingsvolgorde

                                 -- Syntaxisvolgorde | Verwerkingsvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Verwerkingsvolgorde

                                 -- Syntaxisvolgorde | Verwerkingsvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT      3. SELECT
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Verwerkingsvolgorde

                                 -- Syntaxisvolgorde | Verwerkingsvolgorde
SELECT Country, Place, Magnitude -- 1. SELECT      3. SELECT
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY    4. ORDER BY

Verwerkingsfouten


SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes            
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

FROM verwerken

                        -- Verwerkingsvolgorde
SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes   -- 1. FROM - tabel LargeEarthquakes bestaat niet
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERROR
Invalid object name 'LargeEarthquakes'.

WHERE verwerken

                        -- Verwerkingsvolgorde
SELECT Country, 
       PlaceName, 
       Magnitude
FROM Earthquakes                              
WHERE Strength >= 9     -- 2. WHERE - kolom Strength bestaat niet 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERROR
Invalid column name 'Strength'.

SELECT verwerken

                        -- Verwerkingsvolgorde
SELECT Country, 
       PlaceName,       -- 3. SELECT - kolom PlaceName bestaat niet
       Magnitude 
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERROR
Invalid column name 'PlaceName'.

ORDER BY verwerken

                        -- Verwerkingsvolgorde
SELECT Country, 
       Place, 
       Magnitude
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;-- 4. ORDER BY - kolom spelfout

________________________________________________________

-- ERROR
Invalid column name 'Magnitud'.

Foutvrij

SELECT Country, 
       Place, 
       Magnitude   
FROM Earthquakes                
WHERE Magnitude >= 9      
ORDER BY Magnitude DESC;

Country	Place	Magnitude
CL	Bio-Bio; Chile	9.5
US	Southern Alaska	9.2
ID	off the west coast of northern Sumatra	9.1
JP	near the east coast of Honshu; Japan	9.1
...	...	...

Logische verwerkingsvolgorde

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

Laten we oefenen!

De queryprestaties verbeteren in SQL Server