Query tuning: Apply yourself

,

It’s Friday and I’m ready for the weekend as I’m sure everyone else is. This weekend I’m looking forward to getting yard work done and browsing through the whoisactive SQL Ops Studio extension code.

I plan on writing a few posts on creating extensions; but, before we get to those I’d like to share a tip from one my my Co-workers at DBBest.

Recently we were doing a project that heavily focused on query tuning and many tables had various outer joins. My co-worker pointed out that many of these could be converted to an apply rather than a join.

Apply gives you both CROSS and OUTER. Think of CROSS APPLY like an INNER JOIN and OUTER APPLY like an OUTER JOIN.

Let’s compare some code to see how APPLY stacks up.

For this we’ll be comparing a derived table, CTE, and APPLY. We want to get the Amount for the last transaction for a certain range of customers.

First create the demo code:

create table #Users(UserID int not null primary key clustered,

UserName varchar(100),

Password varchar(100),

Fullname varchar(100))

create table #Transactions(TransactionID int identity not null primary key clustered,

UserID int,

Amount int)

;with Numbers as

(select top(100000) ROW_NUMBER() over(order by (select 1)) rn

from master..syscomments a

cross apply master..syscomments b

)

insert into #Users(UserID, UserName, Password, Fullname)

select rn, NEWID(), NEWID(), NEWID()

from Numbers

;with Numbers as

(select top(1000) ROW_NUMBER() over(order by (select 1)) rn

from master..syscomments a

cross apply master..syscomments b

)

insert into #Transactions(UserID, Amount)

select UserID, rn*40

from (select top 90 percent UserID

from #Users) u

cross apply (select top(cast(rand(checksum(newid()))*100 + 1 as int)) rn

from Numbers

where UserID > 0) n

Next, we want to run the queries with STATISTICS IO ON.

select u.UserID, t1.Amount

from #Users u

inner join (select UserID, max(TransactionID) LastiD

from #Transactions

group by UserID) t on t.UserID = u.UserID

inner join #Transactions t1 on TransactionID = LastiD

where u.UserID between 10 and 40

;with Trans as

(select u.UserID, t.Amount, row_number() over(partition by u.UserID order by t.TransactionID desc) rn

from #Users u

inner join #Transactions t on t.UserID = u.UserID

where u.UserID between 10 and 40

)

select UserID, Amount

from Trans

where rn = 1

select u.UserID, t.Amount

from #Users u

cross apply (select top 1 Amount

from #Transactions t

where t.UserID = u.UserID

order by t.TransactionID desc) t

where UserID between 10 and 40

The results are quite surprising indeed.

image

The query plan shows a much lower cost for the APPLY and the IO reports slightly fewer as well.

image

What if we look at “real” data. I mean demo data is fine and dandy but it’s often not representative of the real world. I could fill a database with realistic data using dbForge Data Generator but for this scenario I’ll use AdventureWorks2012. HAHA! Yes, I know I said “real-world data” but let’s look at AdventureWorks anyway.

Here’s the code:

*note that some of you may point out that a join isn’t needed at all since CustomerID is in Sales.SalesOrderHeader. I typed this up and then thought about it and let’s say instead you want to use u.AccountNumber. The query results are the same and now you have reason to use the Sales.Customer table.

select u.CustomerID, t1.TotalDue

from Sales.Customer u

inner join (select CustomerID, max(SalesOrderID) as LastSalesOrderID

from Sales.SalesOrderHeader

group by CustomerID) t on t.CustomerID = u.CustomerID

inner join Sales.SalesOrderHeader t1 on t1.SalesOrderID = t.LastSalesOrderID

WHERE u.CustomerID between 11019 and 11174

;with Trans as

(select u.CustomerID, t.TotalDue, row_number() over(partition by u.CustomerID order by t.SalesOrderID desc) as rn

from Sales.Customer u

inner join Sales.SalesOrderHeader t on t.CustomerID = u.CustomerID

WHERE u.CustomerID between 11019 and 11174

)

select CustomerID, TotalDue

from Trans

where rn = 1

select u.CustomerID, t.TotalDue

from Sales.Customer u

cross apply (select top 1 TotalDue

from Sales.SalesOrderHeader t

where t.CustomerID = u.CustomerID

order by t.SalesOrderID desc) t

WHERE u.CustomerID between 11019 and 11174

We’re still looking at a specific group of customers and getting a list of their totaldue for the last transaction. How does it turn out?

image

This shows that the CTE is actually the lowest cost; but, what if we look at the CPU and IO used with SET STATISTICS?

What we find is that the APPLY uses 1 fewer IO and elapsed time is consistently 0 ms. The CTE has 1 more IO and is consistently greater than 0 ms and the derived table has significantly greater IO.

What does this mean? Two things:

  • Don’t always trust cost in a query plan
  • APPLY can be used to enhance some queries. Note that your mileage will vary and you should always test before blindly making changes.

A good example of where you probably shouldn’t use apply is when we change the queries to look between certain dates. Date is on the joined table not the customer and this makes a big difference.

select u.CustomerID, t1.TotalDue

from Sales.Customer u

inner join (select CustomerID, max(SalesOrderID) as LastSalesOrderID

from Sales.SalesOrderHeader

where OrderDate between ‘2008-01-01’ and ‘2008-03-01’

group by CustomerID) t on t.CustomerID = u.CustomerID

inner join Sales.SalesOrderHeader t1 on t1.SalesOrderID = t.LastSalesOrderID

;with Trans as

(select u.CustomerID, t.TotalDue, row_number() over(partition by u.CustomerID order by t.SalesOrderID desc) as rn

from Sales.Customer u

inner join Sales.SalesOrderHeader t on t.CustomerID = u.CustomerID

where t.OrderDate between ‘2008-01-01’ and ‘2008-03-01’

)

select CustomerID, TotalDue

from Trans

where rn = 1

select u.CustomerID, t.TotalDue

from Sales.Customer u

cross apply (select top 1 TotalDue

from Sales.SalesOrderHeader t

where t.CustomerID = u.CustomerID

and t.OrderDate between ‘2008-01-01’ and ‘2008-03-01’

order by t.SalesOrderID desc) t

image

Note that the APPLY in this scenario is dreadful; so, be sure test!

I hope you found this article useful and it adds to your query tuning kit. Have a great weekend!

Rate

Share

Share

Rate