Blog

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.

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