Want to help us shape our training? Take 30 seconds and answer 5 questions.
Are you looking for sample code from the presentation? Download the samples from A Developers Guide to Dangerous Queries.
|
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 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 |
/************************ * Correlated Subqueries ***********************/ USE ContosoRetailDW; GO SELECT DISTINCT dd.DateDescription , SUM(fs.SalesAmount) AS Sales , SUM(COALESCE(fs.DiscountAmount, 0)) AS Discount FROM dbo.FactSales AS fs JOIN dbo.DimDate AS dd ON fs.DateKey = dd.Datekey WHERE fs.SalesAmount > ( SELECT AVG(fs2.SalesAmount) FROM dbo.FactSales AS fs2 JOIN dbo.DimDate AS dd2 ON fs2.DateKey = dd2.Datekey WHERE dd.CalendarQuarter = dd2.CalendarQuarter AND dd.CalendarYear = dd2.CalendarYear AND fs.ProductKey = fs2.ProductKey ) ; /******************************* * Correlated Subqueries - fixed ******************************/ USE ContosoRetailDW; GO SELECT DISTINCT dd.DateDescription , SUM(fs.SalesAmount) AS Sales, SUM(COALESCE(fs.DiscountAmount, 0)) AS Discount FROM dbo.FactSales AS fs JOIN dbo.DimDate AS dd ON fs.DateKey = dd.Datekey GROUP BY DateDescription HAVING SUM(SalesAmount) > AVG(SalesAmount) ; /**************************** * Nesting stored procedures ***************************/ USE AdventureWorks2012; GO DROP PROCEDURE dbo.OuterProc; DROP PROCEDURE dbo.InnerProc; Go CREATE PROCEDURE dbo.InnerProc AS SELECT SalesOrderID , OrderDate , DueDate , ShipDate , Status , SalesOrderNumber , PurchaseOrderNumber , AccountNumber FROM Sales.SalesOrderHeader AS soh ORDER BY OrderDate ASC; GO CREATE PROCEDURE dbo.OuterProc AS BEGIN CREATE TABLE #sales ( [SalesOrderID] [int] NOT NULL , [OrderDate] [datetime] NOT NULL , [DueDate] [datetime] NOT NULL , [ShipDate] [datetime] NULL , [Status] [tinyint] NOT NULL , [SalesOrderNumber] [nvarchar](25) , [PurchaseOrderNumber] NVARCHAR(25) NULL , [AccountNumber] NVARCHAR(15) NULL ); INSERT INTO #sales ( SalesOrderID , OrderDate , DueDate , ShipDate , Status , SalesOrderNumber , PurchaseOrderNumber , AccountNumber ) EXEC dbo.InnerProc; SELECT TOP 50 SalesOrderID , OrderDate , DueDate , ShipDate , Status , SalesOrderNumber , PurchaseOrderNumber , AccountNumber FROM #sales s JOIN Sales.SalesOrderHeader AS soh ON s.SalesOrderID = soh.SalesOrderID WHERE soh.OrderDate BETWEEN '2006-02-15' AND '2006-04-16' END GO EXEC OuterProc ; /* There's nothing to fix here, this is just an anti-pattern. * Nesting stored procedures can make tracking down performance * problems very difficult. */ /****************** * Excessive TVFs *****************/ USE AdventureWorks2012; GO SET STATISTICS IO ON; DECLARE @numbers AS VARCHAR(MAX) = '1,2,3,4,5,6'; DECLARE @words AS VARCHAR(MAX) = 'one,two,three,four,five,six'; DECLARE @spanish AS VARCHAR(MAX) = 'uno,dos,tres,quatro,cinco,seis'; CREATE TABLE #numbers ( pos INT , element VARCHAR(50) ); CREATE TABLE #words ( pos INT , element VARCHAR(50) ); CREATE TABLE #spanish ( pos INT , element VARCHAR(50) ); SELECT soh.PurchaseOrderNumber , sod.CarrierTrackingNumber , w.element AS EnglishQty , s.element AS SpanishQty FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN dbo.fn_split(@numbers) AS o ON sod.OrderQty = o.element JOIN dbo.fn_split(@words) AS w ON o.pos = w.pos JOIN dbo.fn_split(@spanish) AS s ON w.pos = s.pos ; /************************************************* * Reducing the effect of multiple joins to TVFs ************************************************/ USE AdventureWorks2012; GO SET STATISTICS IO ON; DROP TABLE #words; DECLARE @numbers AS VARCHAR(MAX) = '1,2,3,4,5,6'; DECLARE @words AS VARCHAR(MAX) = 'one,two,three,four,five,six'; DECLARE @spanish AS VARCHAR(MAX) = 'uno,dos,tres,quatro,cinco,seis'; CREATE TABLE #words ( pos INT , ordinal VARCHAR(10) , english VARCHAR(50) , spanish VARCHAR(50) ); INSERT INTO #words ( pos , ordinal ) SELECT pos , element FROM dbo.fn_split(@numbers) UPDATE #words SET english = element FROM fn_split(@words) AS w WHERE w.pos = #words.pos UPDATE #words SET spanish = element FROM fn_split(@spanish) AS s WHERE s.pos = #words.pos; SELECT soh.PurchaseOrderNumber , sod.CarrierTrackingNumber , w.english AS EnglishQty , w.spanish AS SpanishQty FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN #words AS w ON sod.OrderQty = CAST(w.Ordinal AS INT) ; |
4 Comments. Leave new
Thanks for sharing Jeremiah. Great stuff!
By the way, I think I’ve seen similar queries before from a product that will remain unnamed 😉
/*******************************
* Correlated Subqueries – fixed
* Removed DISTINCT
* Added alias in GROUP BY
******************************/
USE ContosoRetailDW;
GO
SELECT
dd.DateDescription ,
SUM(fs.SalesAmount) AS Sales,
SUM(COALESCE(fs.DiscountAmount, 0)) AS Discount
FROM dbo.FactSales AS fs
JOIN dbo.DimDate AS dd
ON fs.DateKey = dd.Datekey
GROUP BY dd.DateDescription
HAVING SUM(fs.SalesAmount) > AVG(fs.SalesAmount) ;
Thanks for the suggestion to remove the GROUP BY.
I edited your comment to make it clear to the reader which changes you made.