Filteren met WHERE

De queryprestaties verbeteren in SQL Server

Dean Smith

Founder, Atamai Analytics

Hoe WHERE werkt

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

PlayerStats Table

Hoe WHERE werkt

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

Filtered PlayerStats Table

Verwerkingsvolgorde van WHERE

SELECT PlayerName, 
      Team, 
      (DRebound+ORebound) AS TotalRebounds
FROM PlayerStats
WHERE TotalRebounds >= 1000
ORDER BY TotalRebounds DESC;

WHERE wordt uitgevoerd vóór SELECT

-- ERROR
Invalid column name 'TotalRebounds'.

Een subquery gebruiken

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- Start of sub-query tr
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- created in the sub-query
ORDER BY TotalRebounds DESC;

Een subquery gebruiken

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- Start of sub-query tr
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- created in the sub-query
ORDER BY TotalRebounds DESC;

PlayerName	Team	TotalRebounds
Andre Drummond	DET	1247
DeAndre Jordan	LAC	1171
Karl-Anthony Towns	MIN	1012
Dwight Howard	CHO	1012

Berekeningen op kolommen

SELECT PlayerName, 
       Team, 
       (DRebound+ORebound) AS TotalRebounds
FROM PlayerStats
WHERE (DRebound+ORebound) >= 1000
ORDER BY TotalRebounds DESC;

Berekeningen op kolommen in de WHERE-filter kunnen de querytijd verhogen

PlayerName	Team	TotalRebounds
Andre Drummond	DET	1247
DeAndre Jordan	LAC	1171
Karl-Anthony Towns	MIN	1012
Dwight Howard	CHO	1012

Functies op kolommen

SELECT PlayerName, College, DraftYear 
FROM Players
WHERE UPPER(LEFT(College,7)) = 'GEORGIA'; 
-- unnecessary use of functions 
-- on a filtering column

Functies toepassen op kolommen in de WHERE-filter kan de querytijd verhogen

PlayerName	College	DraftYear
Damien Wilkins	Georgia
Derrick Favors	Georgia Tech	2010
Iman Shumpert	Georgia Tech	2011
R.J. Hunter	Georgia State	2015
...	...	...

WHERE vereenvoudigd

SELECT PlayerName, College, DraftYear 
FROM Players 
        -- No calculation or function
WHERE College like 'Georgia%';

PlayerName	College	DraftYear
Damien Wilkins	Georgia
Derrick Favors	Georgia Tech	2010
Iman Shumpert	Georgia Tech	2011
R.J. Hunter	Georgia State	2015
...	...	...

Samenvatting

WHERE wordt uitgevoerd vóór SELECT
Berekeningen op kolommen in de WHERE-filter kunnen de querytijd verhogen
Functies toepassen op kolommen in de WHERE-filter kan de querytijd verhogen

Laten we oefenen!

De queryprestaties verbeteren in SQL Server