WHERE로 필터링

SQL Server에서 쿼리 성능 향상하기

Dean Smith

Founder, Atamai Analytics

WHERE 동작 방식

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

PlayerStats Table

WHERE 동작 방식

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

Filtered PlayerStats Table

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

서브쿼리 사용

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

컬럼 계산

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'; 
-- unnecessary use of functions 
-- on a filtering column

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

요약

WHERE 가 SELECT 보다 먼저 처리됨
WHERE 조건에서 컬럼에 _계산_을 적용하면 쿼리 시간이 늘어날 수 있음
WHERE 조건에서 컬럼에 _함수_를 적용하면 쿼리 시간이 늘어날 수 있음

연습해 봅시다!

SQL Server에서 쿼리 성능 향상하기