SQL and the like

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

March 2013 - Posts

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.

 


 

SQL Saturday 194

On the weekend of the 9th March 2013, the second SQL Saturday in the UK occurred.  A massive thank you must go to all the sponsors of the event, FusionIO, Confio, Nexus, Purple Frog, Redgate, Idera and Big Bang Data. Additionally big congratulations to Jonathan and Annette Allan, Thomas Rushton and Mark Price-Mayer for putting on such a great event,  everyone had a whale of a time. 

At the event,  I was honoured to present my first pre-con with my co-presenter Dave Morrison and I think our style and material complement each other well.  Together, we looked at many aspects of TSQL and Internals and received some great feedback from the attendees who, hopefully, will be able to look at their systems and working practices with much more knowledge about SQL Server than previously.

If you attended the pre-con then the slides and examples from the day are available from here.  These are password protected so you will only be able to gain access if you attended, sorry.

If this sounds like the sort of thing that you could also benefit from,  I am also delivering a two day course in London on the 19th and 20th of March looking at Advanced TSQL.  More details on this course and full itinerary can be found here.

In addition to the pre-con I also delivered a new presentation of “Estimation, Statistics and Oh My!”.  This was also well received and I got some very positive feedback.  If you attended this session the slides are available from here.

It was great to catch up with friends and #SQLFamily from home and abroad, and am now looking forward to SQLBits where I shall be again presenting,  see you there Smile