SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

Sub query pass through

Occasionally in forums and on client sites I see conditional subqueries in statements. This is where the developer has decided that it is only necessary to process some data under a certain condition.  By way of example, something like this :

Create Procedure GetOrder @SalesOrderId integer,
                          @CountDetails tinyint
as
Select SOH.salesorderid , 
       case when @CountDetails = 1 then 
        (Select count(*) 
            from Sales.SalesOrderDetail SOD 
            where SOH.SalesOrderID = SOD.SalesOrderID) end
  from sales.SalesOrderHeader SOH
 where SalesOrderID = @SalesOrderID

The count of details are only required when @CountDetails = 1.

If you where to execute the above stored procedure, in the execution plan you would see the ‘Pass Through’ specified on the nested loop operator thusly:

image

As you can see, or at least visualise, this specifies that the code branch is only executed upon the condition of @CountDetails = 1.

This bears out nicely when running the procedure with ‘SET STATISTICS IO ON’,  firstly ‘EXEC GetOrder 43663,1’, ie specifying that we wish to ‘count details’

Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

and secondly ‘EXEC GetOrder 43663,0’ , dont ‘count details’.

Table 'SalesOrderDetail'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So the pass through has done its job and when not required that data is not read,  quite a good cost saving.  However, is this method always a good idea ?.   Well no, im afraid not.  The pass through requires a nested loop join to operate even when a hash or merge would be more optimal.

Consider this stored procedure

Create Procedure GetAllOrders @CountDetails tinyint
as
Select SOH.salesorderid ,
       case when @CountDetails =1
            then (Select count(*)
                    from Sales.SalesOrderDetail SOD
                  where SOH.SalesOrderID = SOD.SalesOrderID) end
  from sales.SalesOrderHeader SOH
  Option(maxdop 1)

Now let us execute with ‘EXEC GetAllOrders 1’.  As Ive stated, in the the plan we will see a nested loop operator joining SalesOrderHeader and SalesOrderDetail, the nested loop is required to process a ‘pass through’.

image

As we need all rows to logically satisfy our query, a hash would have been more optimal.

Lets compare the IO between the above and a statement that does a hash join

First the procedure:

Table 'SalesOrderDetail'. Scan count 31465, logical reads 95887, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

and now for the statement :

Select SOH.salesorderid , 
        (Select count(*)
           from Sales.SalesOrderDetail SOD
          where SOH.SalesOrderID = SOD.SalesOrderID)
  from sales.SalesOrderHeader SOH

 

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That is a massive cost difference and one that certainly should be avoided.

if it were me, in this case my stored procedure would now be :

Create Procedure GetAllOrders @CountDetails tinyint
as
if(@CountDetails = 0) begin
    Select SOH.salesorderid ,
           0 as CountDetails
      from sales.SalesOrderHeader SOH
end else begin
    Select SOH.salesorderid , 
            (Select count(*)
               from Sales.SalesOrderDetail SOD
              where SOH.SalesOrderID = SOD.SalesOrderID)
      from sales.SalesOrderHeader SOH
end

So be careful with your pass throughs and make sure that you are not inappropriately looping due to them.

 


 

Read the complete post at http://feedproxy.google.com/~r/SqlAndTheLike/~3/uZAieXh9-1o/sub-query-pass-through.aspx