Introducere

Îmbunătățirea performanței interogărilor în SQL Server

Dean Smith

Founder, Atamai Analytics

Baza de date Cutremure

Baza de date NBA Sezonul 2017-2018

Baza de date Comenzi Clienți

Este ușor de citit?

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

Sugestii

Fiți consecvenți
Folosiți MAJUSCULE pentru toată sintaxa SQL
Creați o linie nouă pentru fiecare element major de procesare: SELECT, FROM, WHERE, etc.
Indentați codul:
- Subinterogări
- Instrucțiuni ON
- Condiții AND/OR
- Pentru a evita linii prea lungi, de exemplu, mai multe nume de coloane
Terminați interogarea cu punct și virgulă (;)
Folosiți alias acolo unde este necesar, cu AS

Mult mai bine...

De la

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;

Comentarea blocurilor

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

Folosiți /* și */ pentru a comenta un bloc de cod sau text

Comentarea blocurilor

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

Folosiți /* și */ pentru a comenta un bloc de cod sau text

Team	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

Comentarea liniilor

Folosiți -- pentru a comenta o singură linie de cod sau text

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;

Comentarea liniilor

Folosiți -- pentru a comenta o singură linie de cod sau text

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;

Comentariu care indică faptul că noua coloană reprezintă numărul de jucători

Comentariu care indică faptul că subinterogarea este indentată

Comentarea liniilor

Folosiți -- pentru a comenta o singură linie de cod sau text

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;

Comentariu care marchează o întrerupere înainte de INNER JOIN

Comentariu despre necesitatea ORDER BY

Comentarea liniilor

Folosiți -- pentru a comenta o singură linie de cod sau text

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

Instrucțiunea ORDER BY comentată

Să exersăm!

Îmbunătățirea performanței interogărilor în SQL Server