Hi Guys,Today for you a basic post!
We do a comparison between a Query with Sub-Queries and with the Apply operator!
So, what are differences between using sub-query and using cross/Outer Apply?
Well, if you don't know you should absolutely read this post!
Introduction
For our comparison we choose these queries:
A query with two sub-queries (A subquery is simply a query with another query inside it's select)
Select
(Select MAX(OrdRig.Qta1) as conta
from OrdRig
where Ordrig.idordtes = o.Id and OrdRig.IdComrig is not null),
(Select MAX(OrdRig.Qta2) as conta
from OrdRig
where Ordrig.idordtes = o.Id and OrdRig.IdComrig is not null)
From eco..ordtes o
A query that use a Cross Apply operator
select t.Qta1, t.Qta2
from eco..ordtes o
CROSS APPLY
(Select MAX(OrdRig.Qta1) as Qta1, MAX(OrdRig.Qta2) as Qta2
from OrdRig
where Ordrig.idordtes = o.Id and OrdRig.IdComrig is not null
) as t
As you can see, through the Apply operator (cross or outer) you will be able to write a query that return the same results as the Query that use the sub-Query.
Yes, but what are differences between these two ways to write the query?
Surely you will have just noticed that in the first Query it was necessary to use two sub-queries while using the cross apply it is possible to read data from the OrdRig table only once.
Yes actually this is a smart thing!
But let's look at the differences in more detail
Sub-queries vs. Cross Apply
First let's compare how many pages are read executing this T-SQL command:
SET STATISTICS_IO ONNow execute both queries:
(123201 righe interessate)
Tabella 'OrdRig'. Conteggio analisi 18, letture logiche 74392, letture fisiche 0, letture read-ahead 30437, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'OrdTes'. Conteggio analisi 9, letture logiche 18112, letture fisiche 3, letture read-ahead 8020, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'Workfile'. Conteggio analisi 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'Worktable'. Conteggio analisi 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
(1 riga interessata)
(123201 righe interessate)
Tabella 'Worktable'. Conteggio analisi 123201, letture logiche 2042619, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'Worktable'. Conteggio analisi 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'OrdTes'. Conteggio analisi 9, letture logiche 18112, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'OrdRig'. Conteggio analisi 1, letture logiche 35478, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
(1 riga interessata)
But what is the CONS?
The CONS is that, is this case, SQL Server create a temporary table called worktable
Tabella 'Worktable'. Conteggio analisi 123201, letture logiche 2042619, letture fisiche 0,
This table is stored on the tempdb database and will contain these data:
Select MAX(OrdRig.Qta1) as Qta1, MAX(OrdRig.Qta2) as Qta2
from OrdRig
where Ordrig.idordtes = o.Id and OrdRig.IdComrig is not null
But, due to the fact that tempdb database is used performances may be affected!
So this proves that using an apply operator is not always better that use two Sub-queries!
Let's now take a look to the execution plans:
Also the execution plan tells the same story.
Running both queries togheter shows that the query with two subqueries is faster (39%)
While using the apply operator the subtree estimated cost is greater (107,7)
Conclusions
Using Apply operator is not always better the using a sub-query.We proved the a query with inside two sub-queris is faster that the same query using the Apply.
This is due to the fact that Apply operator create a temporary table that is stored into the tempdb.
However, if the number of subqueries were to increase, the result of this test would most likely change in favor of the solution that uses cross apply.
I leave you as homework to check what happens in case the query has four subqueries.
Which of the two solutions would be more efficient?
I look forward to your reply!
To all a very good weekend and see you soon!
If you like these articles, I remind you to subscribe to this blog
Luca Biondi @ SQLServerPerformance blog!
A song with the mood of the day: U Can't Touch This