Ordine della query

Migliorare le prestazioni delle query in SQL Server

Dean Smith

Founder, Atamai Analytics

Query: grandi terremoti

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
...	...	...

Ordine sintattico

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

Ordine sintattico

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

Ordine sintattico

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

Ordine sintattico

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

Ordine di esecuzione

                                 -- Ordine sintattico | Ordine di esecuzione
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Ordine di esecuzione

                                 -- Ordine sintattico | Ordine di esecuzione
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

Ordine di esecuzione

                                 -- Ordine sintattico | Ordine di esecuzione
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

Ordine di esecuzione

                                 -- Ordine sintattico | Ordine di esecuzione
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

Errori di elaborazione


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

Elaborazione di FROM

                        -- Ordine di esecuzione
SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes   -- 1. FROM - la tabella LargeEarthquakes non esiste
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERRORE
Invalid object name 'LargeEarthquakes'.

Elaborazione di WHERE

                        -- Ordine di esecuzione
SELECT Country, 
       PlaceName, 
       Magnitude
FROM Earthquakes                              
WHERE Strength >= 9     -- 2. WHERE - la colonna Strength non esiste 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERRORE
Invalid column name 'Strength'.

Elaborazione di SELECT

                        -- Ordine di esecuzione
SELECT Country, 
       PlaceName,       -- 3. SELECT - la colonna PlaceName non esiste
       Magnitude 
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERRORE
Invalid column name 'PlaceName'.

Elaborazione di ORDER BY

                        -- Ordine di esecuzione
SELECT Country, 
       Place, 
       Magnitude
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;-- 4. ORDER BY - errore di ortografia nella colonna

________________________________________________________

-- ERRORE
Invalid column name 'Magnitud'.

Senza errori

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
...	...	...

Ordine logico di elaborazione

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

Ayo berlatih!

Migliorare le prestazioni delle query in SQL Server