Ordre d'une requête

Améliorer la performance des requêtes dans SQL Server

Dean Smith

Founder, Atamai Analytics

Requête : grands séismes

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

Country	Place	Magnitude
CL	Bio-Bio; Chili	9.5
US	sud de l'Alaska	9.2
ID	au large de la côte ouest du nord de Sumatra	9.1
JP	près de la côte est de Honshu; Japon	9.1
...	...	...

Ordre de la syntaxe

                                 -- Ordre de la syntaxe
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

Ordre de la syntaxe

                                 -- Ordre de la syntaxe
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

Ordre de la syntaxe

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

Ordre de la syntaxe

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

Ordre de traitement

                                 -- Ordre de la syntaxe | Ordre de traitement
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Ordre de traitement

                                 -- Ordre de la syntaxe | Ordre de traitement
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

Ordre de traitement

                                 -- Ordre de la syntaxe | Ordre de traitement
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

Ordre de traitement

                                 -- Ordre de la syntaxe | Ordre de traitement
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

Erreurs de traitement


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

Traitement de FROM

                        -- Ordre de traitement
SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes   -- 1. FROM - la table LargeEarthquakes n'existe pas
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

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

Traitement de WHERE

                        -- Ordre de traitement
SELECT Country, 
       PlaceName, 
       Magnitude
FROM Earthquakes                              
WHERE Strength >= 9     -- 2. WHERE - la colonne Strength n'existe pas 
ORDER BY Magnitud DESC;

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

Traitement de SELECT

                        -- Ordre de traitement
SELECT Country, 
       PlaceName,       -- 3. SELECT - la colonne PlaceName n'existe pas
       Magnitude 
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;

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

Traitement de ORDER BY

                        -- Ordre de traitement
SELECT Country, 
       Place, 
       Magnitude
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;-- 4. ORDER BY - faute d'orthographe de colonne

________________________________________________________

-- ERROR
Invalid column name 'Magnitud'.

Sans erreur

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

Country	Place	Magnitude
CL	Bio-Bio; Chili	9.5
US	sud de l'Alaska	9.2
ID	au large de la côte ouest du nord de Sumatra	9.1
JP	près de la côte est de Honshu; Japon	9.1
...	...	...

Ordre logique de traitement

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

Passons à la pratique !

Améliorer la performance des requêtes dans SQL Server