導論

改進 SQL Server 中的查詢效能

Dean Smith

Founder, Atamai Analytics

地震資料庫

NBA 2017-2018 球季資料庫

客戶訂單資料庫

這樣好讀嗎？

Select ps.Team, count(p.PlayerName) 
As NonNthAmerPlayers from 
 PlayerStats ps inner 
join (select PlayerName FROM Players 
    WHERE Country <> 'USA' Or Country 
 <> 'Canada' ) 
 p on p.PlayerName = ps.PlayerName 
 group BY ps.Team
having Count(p.PlayerName) 
>=24 Order by NonNthAmerPlayers desc

Team	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

寫作建議

保持一致
對所有 SQL 語法使用大寫
每個主要處理語法各換一行：SELECT、FROM、WHERE 等
縮排程式碼：
- 子查詢
- ON 敘述
- AND/OR 條件
- 避免過長單行，例如多個欄位名稱
查詢以分號（;）結尾
需要時使用 AS 取別名

好得多…

原始

Select ps.Team, count(p.PlayerName) 
As NonNthAmerPlayers from 
 PlayerStats ps inner 
join (select PlayerName FROM Players 
    WHERE Country <> 'USA' Or Country 
 <> 'Canada' ) 
 p on p.PlayerName = ps.PlayerName 
 group BY ps.Team
having Count(p.PlayerName) 
>=24 Order by NonNthAmerPlayers desc

改為

SELECT ps.Team, 
    COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps
INNER JOIN
        (SELECT PlayerName 
         FROM Players 
         WHERE Country <> 'USA'
                OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
ORDER BY NonNthAmerPlayers DESC;

區塊註解

/* 
Returns a list of NBA teams with 24 or more non-North 
American players on the team roster.
*/

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
         OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
ORDER BY NonNthAmerPlayers DESC;

使用 /* 與 */ 註解一個區塊的程式碼或文字

區塊註解

/* 
Returns a list of NBA teams with 24 or more non-North 
American players on the team roster.
*/

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
         OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
ORDER BY NonNthAmerPlayers DESC;

使用 /* 與 */ 註解一個區塊的程式碼或文字

Team	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

行內註解

使用 -- 註解單一行的程式碼或文字

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers
FROM PlayerStats ps

INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24

ORDER BY NonNthAmerPlayers DESC;

行內註解

使用 -- 註解單一行的程式碼或文字

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers -- Count of players
FROM PlayerStats ps

INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p -- Indented qub-suery
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24

ORDER BY NonNthAmerPlayers DESC;

註解：新欄位為球員計數

註解：子查詢已縮排

行內註解

使用 -- 註解單一行的程式碼或文字

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers -- Count of players
FROM PlayerStats ps
-- Inner join starts here
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p -- Indented qub-suery
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24
-- Remove the ORDER BY, it is not required
ORDER BY NonNthAmerPlayers DESC;

註解：在 INNER JOIN 前做分隔

註解：ORDER BY 非必要

行內註解

使用 -- 註解單一行的程式碼或文字

SELECT ps.Team, 
  COUNT(p.PlayerName) NonNthAmerPlayers -- Count of players
FROM PlayerStats ps
-- Inner join starts here
INNER JOIN 
    (SELECT PlayerName 
     FROM Players 
     WHERE Country <> 'USA'
        OR Country <> 'Canada' ) p -- Indented qub-suery
    ON p.PlayerName = ps.PlayerName
GROUP BY ps.Team
HAVING COUNT(p.PlayerName) >=24;
-- Remove the ORDER BY, it is not required
-- ORDER BY NonNthAmerPlayers DESC

已將 ORDER BY 敘述註解掉

一起來練習吧！

改進 SQL Server 中的查詢效能