SQL Server, Avoid that damn Table Spool!

Hi guys,

Welcome back for another post!

A light one inspired from what i do during my work: many times I optimize queries rewriting them or adding index and i like it very much!

Sometimes you need to use a little imagination, but everything you learn ... sooner or later it will come in handy!

Enjoy the reading mates!

Avoid the Table Spool, when you can

A few days ago I came across a query whose execution time was very high ... or so it was for the client complaining about the problem.

Before recreating it, let's create the structures and fill the tables with some data

       
Create table PriceList (Id int identity(1,1) primary Key,Code Varchar(20))

Insert into PriceList (code)
  select top 1000 right('0000'+cast( row_number() over (order by id) as varchar(4)),4) from artico
Insert into PriceList (code)
  select top 4000 right('1000'+cast( row_number() over (order by id) as varchar(4)),4) from artico


Create table OrdersList (Id int identity(1,1) primary Key,
                         Idpl_Normal int, 
		     Idpl_Special int,
		     Idcustomer int);

Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,1)
Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,2) 
go 100
Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,3) 
go 200
Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,4) 
go 9699

This is the "problematic" Query:

       
Select 
  id 
From PriceList n 
Where Exists ( Select d.Id 
               From OrdersList d 
	     Where ( d.idcustomer = 1) and 
	           ( ( d.idpl_Normal=n.ID) or ( d.Idpl_Special=n.ID) )
	   )

See what happens when looking at the execution plan.

(All) rows are read from the Orderlist table (this rapresent the 6% of the execution time) then a Table Spool operator is used. This operatore is responsible 87% of the time.

Pages read are:

       
Table 'OrdersList'. Scan count 1, logical reads 33, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'PriceList'. Scan count 1, logical reads 15, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Now try to write the same statement in another way! 

For example:

       
select 
  id 
From PriceList n 
Where Exists ( Select d.Id From OrdersList d Where d.idcustomer = 1 and d.idpl_Normal=n.ID ) or
      Exists ( Select d.Id From OrdersList d Where d.idcustomer = 1 and d.Idpl_Special=n.ID )

The execution plan is different from before.

What difference is there?

The table spool operator is gone!

Pages read are:

       
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'PriceList'. Scan count 0, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrdersList'. Scan count 2, logical reads 66, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 

Comparing the number of pages read we notice that we read twice from the Orderlist table (from 33 to 66) and we read less from the table PriceList (from 15 to 4). This is however a little difference.

The cost of the original Query is 0,594 while the cost of the modified query is only 0,097. 

As mentioned above the big difference is the lack of the table spool operator!

A simple beckmark give me this result (60% less execution time):

 

Well, this is an example of tuning!

That's all for today guys!

P.S. Want to know more about the Table Spool operator?
Or maybe you want to see all the queries you have in the plan cache that are using the Table Spool?

Well you just have to follow me in the next posts!

I wish you an happy new year full of peace. Full of health, love and passions!

If you liked this post follow me on linkedin!

Luca

 

 

 

 

Do you want to support this blog? 

 

Next post:

Previous post: SQL Server and the Adaptive Join feature: "Adaptive Join" Vs. "wrong cardinality estimate" and "Uneven data distribution"