Frågeordning

Förbättra frågeprestanda i SQL Server

Dean Smith

Founder, Atamai Analytics

Fråga om stora jordbävningar

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

Syntaxordning

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

Syntaxordning

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

Syntaxordning

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

Syntaxordning

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

Bearbetningsordning

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

Bearbetningsordning

                                 -- Syntaxordning | Bearbetningsordning
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

Bearbetningsordning

                                 -- Syntaxordning | Bearbetningsordning
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

Bearbetningsordning

                                 -- Syntaxordning | Bearbetningsordning
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

Bearbetningsfel


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

Bearbetning av FROM

                        -- Bearbetningsordning
SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes   -- 1. FROM - tabellen LargeEarthquakes finns inte
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- FEL
Invalid object name 'LargeEarthquakes'.

Bearbetning av WHERE

                        -- Bearbetningsordning
SELECT Country, 
       PlaceName, 
       Magnitude
FROM Earthquakes                              
WHERE Strength >= 9     -- 2. WHERE - kolumnen Strength finns inte 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- FEL
Invalid column name 'Strength'.

Bearbetning av SELECT

                        -- Bearbetningsordning
SELECT Country, 
       PlaceName,       -- 3. SELECT - kolumnen PlaceName finns inte
       Magnitude 
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- FEL
Invalid column name 'PlaceName'.

Bearbetning av ORDER BY

                        -- Bearbetningsordning
SELECT Country, 
       Place, 
       Magnitude
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;-- 4. ORDER BY - felstavad kolumn

________________________________________________________

-- FEL
Invalid column name 'Magnitud'.

Utan fel

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

Logisk bearbetningsordning

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

Nu kör vi en övning!

Förbättra frågeprestanda i SQL Server