Here’s the sample scripts from the performance tuning demo. These are designed to work with the Stack Overflow 2010 database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
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? */ |
The slides are going to go right here when I get a chance to upload them…