使用 WHERE 篩選

改進 SQL Server 中的查詢效能

Dean Smith

Founder, Atamai Analytics

WHERE 的運作方式

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

PlayerStats 資料表

WHERE 的運作方式

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

已篩選的 PlayerStats 資料表

WHERE 的處理順序

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

WHERE 會先於 SELECT 處理

-- ERROR
Invalid column name 'TotalRebounds'.

使用子查詢

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- 子查詢 tr 開始
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- 在子查詢中建立
ORDER BY TotalRebounds DESC;

使用子查詢

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- 子查詢 tr 開始
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- 在子查詢中建立
ORDER BY TotalRebounds DESC;

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

對欄位進行運算

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

在 WHERE 條件中對欄位做「_運算_」會增加查詢時間

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

對欄位使用函式

SELECT PlayerName, College, DraftYear 
FROM Players
WHERE UPPER(LEFT(College,7)) = 'GEORGIA'; 
-- 在篩選欄位上不必要地使用函式

在 WHERE 條件中對欄位套用「_函式_」會增加查詢時間

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

簡化 WHERE

SELECT PlayerName, College, DraftYear 
FROM Players 
        -- 無運算或函式
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
...	...	...

重點整理

WHERE 會先於 SELECT 處理
在 WHERE 條件中對欄位做「_運算_」會增加查詢時間
在 WHERE 條件中對欄位套用「_函式_」會增加查詢時間

一起來練習吧！

改進 SQL Server 中的查詢效能