Introduction

Improving Query Performance in SQL Server

Dean Smith

Founder, Atamai Analytics

Earthquakes database

Earthquakes Database

NBA Season 2017-2018 database

Customer Orders database

Is this easy to read?

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

Suggestions

Be consistent
Use UPPER CASE for all SQL syntax
Create a new line for each major processing syntax: SELECT, FROM, WHERE, etc.
Indent code:
- Sub-queries
- ON statements
- AND/OR conditions
- To avoid long single lines of code, for example, several column names
Complete the query with a semi-colon (;)
Alias where required, using AS

Much better...

From

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;

Commenting blocks

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

Use /* and */ to comment out a block of code or text

Commenting blocks

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

Use /* and */ to comment out a block of code or text

Team	NonNthAmerPlayers
HOU	24
LAL	24
MEM	24
MIL	24

Commenting lines

Use -- to comment out a single line of code or 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;

Commenting lines

Use -- to comment out a single line of code or 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;

Comment indicating that the new column is a count of players

Comment indicating that the sub-query is indented

Commenting lines

Use -- to comment out a single line of code or 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;

Comment marking a break before the INNER JOIN

Comment about the requirement of ORDER BY

Commenting lines

Use -- to comment out a single line of code or 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

Commented out ORDER BY statement

Let's practice

Improving Query Performance in SQL Server