February 11, 2016 at 8:32 am
I have a number of queries that contain moderately complex business logic and that logic is used by multiple columns. I've begun to use CROSS APPLY to centralize calculations that are used to in the resulting query. QUESTION: is there a performance penalty or boost to using this simplification?
Simplified Example:
select o.OrderId
, d.ItemSKU
, d.ItemName
, d.Price
, d.Quantity
, c.ExtendedPrice
, c.SalesTax
, c.ExtendedCost
, c.ExtendedPrice + c.SalesTax as LineItemTotal
, c.ExtendedPrice - c.ExtendedCost as LineItemProfit
, convert(decimal(10,4), (1 - (d.Price / d.Cost)) * 100) as ProfitMargin
from dbo.Orders o
join dbo.OrderDetail d
on o.OrderId = d.OrderId
left
join dbo.TaxAuthority t
on o.FIPS = t.FIPS
cross
apply ( select d.Quantity * d.Price as ExtendedPrice
, d.Quantity * d.Cost as ExtendedCost
, d.Quantity * d.Price * isnull(t.SalesTaxRate, 0) as SalesTax
) c;
As you can see from the greatly simplified example above, I could easily do the calculations in-line, however, placing the calculations in a CROSS APPLY allows me to name a calculation that simplifies the upper query. My assumption is that any cost of the CROSS APPLY is outweighed by performing the calculations once and using them multiple times. Also, if there's a bug in a calculation then I only have to fix and test it in one place.
--Paul Hunter
February 11, 2016 at 8:43 am
If you are using the output of an APPLY block as the input for another then you are likely to find a performance hit after x "levels of nesting". x will depend on what you're doing but with the simplest of cases I've usually found it to be 6 or 7.
http://www.sqlservercentral.com/articles/T-SQL/97545/[/url]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 11, 2016 at 1:32 pm
Do not expect this use of Apply to reduce the number of calculations the optimizer puts in the plan. It might, but it often does not.
However, those computations are incredibly cheap in relation to the other things SQL Server has to do when performing a query, so I would not worry about it.
The main benefits of using Apply for this are increased simplicity of the query and easier future maintenance. And that's worth a billion times more than the tiny millisecond performance difference that this might or might not have.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply