Introduction

Améliorer la performance des requêtes dans SQL Server

Dean Smith

Founder, Atamai Analytics

Base de données sur les tremblements de terre

Base de données des tremblements de terre

Base de données NBA Saison 2017-2018

Base de données des commandes clients

Est-ce facile à lire ?

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

Équipe	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

Suggestions

Soyez cohérent
Mettez la syntaxe SQL en MAJUSCULES
Allez à la ligne pour chaque étape : SELECT, FROM, WHERE, etc.
Indentez le code :
- Sous-requêtes
- Clauses ON
- Conditions AND/OR
- Pour éviter de longues lignes, p. ex. plusieurs noms de colonnes
Terminez la requête par un point-virgule (;)
Ajoutez des alias au besoin avec AS

Beaucoup mieux…

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;

Commenter des blocs

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

Utilisez /* et */ pour commenter un bloc de code ou de texte

Commenter des blocs

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

Utilisez /* et */ pour commenter un bloc de code ou de texte

Équipe	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

Commenter des lignes

Utilisez -- pour commenter une seule ligne de code ou de texte

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;

Commenter des lignes

Utilisez -- pour commenter une seule ligne de code ou de texte

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;

Commentaire indiquant que la nouvelle colonne est un décompte de joueurs

Commentaire indiquant que la sous-requête est indentée

Commenter des lignes

Utilisez -- pour commenter une seule ligne de code ou de texte

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;

Commentaire marquant une pause avant le INNER JOIN

Commentaire sur le caractère facultatif de ORDER BY

Commenter des lignes

Utilisez -- pour commenter une seule ligne de code ou de texte

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

Instruction ORDER BY commentée

Passons à la pratique !

Améliorer la performance des requêtes dans SQL Server