Performance

Here’s the sample scripts from the performance tuning demo. These are designed to work with the Stack Overflow 2010 database.

USE StackOverflow2010
GO

/* Turn on Actual Execution plan */
/* Top 100 answer scorers on Stack Overflow as of 2010 (highest average answer score) */
/* Sample query from here https://data.stackexchange.com/stackoverflow/query/952/top-500-answerers-on-the-site */
SELECT TOP (100)
u.Id AS UserId
,u.DisplayName
,Count(p.Id) AS Answers
,CAST(AVG(CAST(Score AS float)) AS numeric(6,2)) AS [Average Answer Score]
FROM Posts p
INNER JOIN Users u ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 AND CommunityOwnedDate IS NULL AND ClosedDate IS NULL
GROUP BY u.Id, u.DisplayName
HAVING Count(p.Id) > 10
ORDER BY [Average Answer Score] DESC


/* Turn statistics on, run the above query again */
SET STATISTICS TIME,IO ON

/* Here SQL; take a cuddly dinosaur, I'm sorry for what's about to happen to you
__
/ _)
_.----._/ /
/ /
__/ ( | ( |
/__.-'|_|--|_|

*/


/* Horrible Cursor */
/* Selects top 100 results from dbo.Users as an example */
/* If you do this to real servers they will cry */
DROP TABLE IF EXISTS #Users;
SELECT TOP (100) Id, DisplayName, LastAccessDate INTO #Users FROM dbo.Users;

DECLARE UpdateUsers CURSOR FOR
SELECT Id, DisplayName, LastAccessDate
FROM #Users

DECLARE @id INT; DECLARE @name VARCHAR(255); DECLARE @lastaccessdate datetime; DECLARE @i INT = 1;

OPEN UpdateUsers;
FETCH NEXT FROM UpdateUsers INTO @id, @name, @lastaccessdate;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE u
SET
u.LastAccessDate = p.CreationDate
FROM #Users u
INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
WHERE @id = u.Id
FETCH NEXT FROM UpdateUsers INTO @id, @name, @lastaccessdate;
END
CLOSE UpdateUsers;
DEALLOCATE UpdateUsers

SELECT * FROM #Users

/* Set based alternative */
DROP TABLE IF EXISTS #Users;
SELECT TOP 100 Id, DisplayName, LastAccessDate INTO #Users FROM dbo.Users;

UPDATE u
SET
u.LastAccessDate = p.CreationDate
FROM #Users u
INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId

/* Let's use a function to reduce the code we're having to do, great idea right? */
SELECT TOP (100)
u.Id
,u.DisplayName
,u.Reputation
,(SELECT count(1) FROM Posts p WHERE p.OwnerUserId = u.Id) 
FROM dbo.Users u
GROUP BY u.Id, u.DisplayName, u.Reputation

/* Let's replace that with a function */
DROP FUNCTION IF EXISTS [dbo].[PostCount] GO
CREATE FUNCTION [dbo].[PostCount] (@UserId INT)
RETURNS INT
AS
BEGIN
DECLARE @ReturnedValue int
SET @ReturnedValue = (SELECT count(1) FROM Posts p WHERE p.OwnerUserId = @UserId) 
RETURN @ReturnedValue
END

/* Let's use this function, any better? */
SELECT TOP (100)
u.Id
,u.DisplayName
,u.Reputation
,dbo.PostCount(u.id) AS PostCount
FROM dbo.Users u
GROUP BY u.Id, u.DisplayName, u.Reputation


/* Subquery in the Join */
SELECT TOP (100)
u.Id
,u.DisplayName
,u.Reputation
,sub.PostCount
FROM dbo.Users u
INNER JOIN (SELECT p.OwnerUserId, count(1) PostCount FROM Posts p GROUP BY p.OwnerUserId) sub
ON u.Id = sub.OwnerUserId
GROUP BY u.Id, u.DisplayName, u.Reputation


/* Indexing, depending upon time */
/* How long have you been talking for Benner? */

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

USE StackOverflow2010

/* Turn on Actual Execution plan */

/* Top 100 answer scorers on Stack Overflow as of 2010 (highest average answer score) */

/* Sample query from here https://data.stackexchange.com/stackoverflow/query/952/top-500-answerers-on-the-site */

SELECT TOP (100)

u.Id AS UserId

,u.DisplayName

,Count(p.Id) AS Answers

,CAST(AVG(CAST(Score AS float)) AS numeric(6,2)) AS [Average Answer Score]

FROM Posts p

INNER JOIN Users u ON u.Id = p.OwnerUserId

WHERE PostTypeId = 2 AND CommunityOwnedDate IS NULL AND ClosedDate IS NULL

GROUP BY u.Id, u.DisplayName

HAVING Count(p.Id) > 10

ORDER BY [Average Answer Score] DESC

/* Turn statistics on, run the above query again */

SET STATISTICS TIME,IO ON

/* Here SQL; take a cuddly dinosaur, I'm sorry for what's about to happen to you

/ _)

_.----._/ /

/ /

__/ ( | ( |

/__.-'|_|--|_|

/* Horrible Cursor */

/* Selects top 100 results from dbo.Users as an example */

/* If you do this to real servers they will cry */

DROP TABLE IF EXISTS #Users;

SELECT TOP (100) Id, DisplayName, LastAccessDate INTO #Users FROM dbo.Users;

DECLARE UpdateUsers CURSOR FOR

SELECT Id, DisplayName, LastAccessDate

FROM #Users

DECLARE @id INT; DECLARE @name VARCHAR(255); DECLARE @lastaccessdate datetime; DECLARE @i INT = 1;

OPEN UpdateUsers;

FETCH NEXT FROM UpdateUsers INTO @id, @name, @lastaccessdate;

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE u

SET

u.LastAccessDate = p.CreationDate

FROM #Users u

INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId

WHERE @id = u.Id

FETCH NEXT FROM UpdateUsers INTO @id, @name, @lastaccessdate;

END

CLOSE UpdateUsers;

DEALLOCATE UpdateUsers

SELECT * FROM #Users

/* Set based alternative */

DROP TABLE IF EXISTS #Users;

SELECT TOP 100 Id, DisplayName, LastAccessDate INTO #Users FROM dbo.Users;

UPDATE u

SET

u.LastAccessDate = p.CreationDate

FROM #Users u

INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId

/* Let's use a function to reduce the code we're having to do, great idea right? */

SELECT TOP (100)

u.Id

,u.DisplayName

,u.Reputation

,(SELECT count(1) FROM Posts p WHERE p.OwnerUserId = u.Id)

FROM dbo.Users u

GROUP BY u.Id, u.DisplayName, u.Reputation

/* Let's replace that with a function */

DROP FUNCTION IF EXISTS [dbo].[PostCount] GO

CREATE FUNCTION [dbo].[PostCount] (@UserId INT)

RETURNS INT

BEGIN

DECLARE @ReturnedValue int

SET @ReturnedValue = (SELECT count(1) FROM Posts p WHERE p.OwnerUserId = @UserId)

RETURN @ReturnedValue

END

/* Let's use this function, any better? */

SELECT TOP (100)

u.Id

,u.DisplayName

,u.Reputation

,dbo.PostCount(u.id) AS PostCount

FROM dbo.Users u

GROUP BY u.Id, u.DisplayName, u.Reputation

/* Subquery in the Join */

SELECT TOP (100)

u.Id

,u.DisplayName

,u.Reputation

,sub.PostCount

FROM dbo.Users u

INNER JOIN (SELECT p.OwnerUserId, count(1) PostCount FROM Posts p GROUP BY p.OwnerUserId) sub

ON u.Id = sub.OwnerUserId

GROUP BY u.Id, u.DisplayName, u.Reputation

/* Indexing, depending upon time */

/* How long have you been talking for Benner? */

The slides are going to go right here when I get a chance to upload them…