Which one is better CTE or using Subquery in the from clause?

Question

Which one is better CTE or using Subquery in the from clause?

Sunel

Old Hand

Points: 346
More actions
October 20, 2008 at 4:23 am

#94149

Which one is better CTE or using Subquery in the from clause? I have written the query using CTE and subquery that filter the records using derived column.
---SubQuery
DECLARE @EndDateTime datetime
DECLARE @StartDateTime datetime
DECLARE @ATM_ID varchar(15)
select @StartDateTime = '2008-07-14 22:40:00.000', @EndDateTime ='2008-07-15 12:09:00.000', @ATM_ID = 'NIB00905'
Select sum(a.Downtime1) from (
Select
Case when @StartDateTime > (SLA.SLADate + SLA.Start_Time) then Datediff(mi , @StartDateTime, (SLA.SLADate + SLA.End_Time)) Else SLA.upTimeInMinutes End -
Case when (SLA.SLADate + SLA.End_Time) > @EndDateTime then Datediff(mi , @EndDateTime, (SLA.SLADate + SLA.End_Time)) Else 0 End as Downtime1
from DailySLALog SLA
where SLA.SlAdate Between convert(varchar, @StartDateTime, 106) and convert(varchar, @EndDateTime, 106)
and SLA.ATM_ID = @ATM_ID ) as a
where Downtime1 > 0
--- using CTE
DECLARE @EndDateTime datetime
DECLARE @StartDateTime datetime
DECLARE @ATM_ID varchar(15)
select @StartDateTime = '2008-07-14 22:40:00.000', @EndDateTime ='2008-07-15 12:09:00.000', @ATM_ID = 'NIB00905';
With Downtime ( DowntimeValue ) as
(
Select
Case when @StartDateTime > (SLA.SLADate + SLA.Start_Time) then Datediff(mi , @StartDateTime, (SLA.SLADate + SLA.End_Time)) Else SLA.upTimeInMinutes End -
Case when (SLA.SLADate + SLA.End_Time) > @EndDateTime then Datediff(mi , @EndDateTime, (SLA.SLADate + SLA.End_Time)) Else 0 End as Downtime1
from DailySLALog SLA
where SLA.SlAdate Between convert(varchar, @StartDateTime, 106) and convert(varchar, @EndDateTime, 106)
and SLA.ATM_ID = @ATM_ID
)
Select sum(DowntimeValue) from Downtime where DowntimeValue > 0
Thanks,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

If the CTE and the sub-query are the same, they will get the same execution plan and have the same performance. There is no special performance benefit to a CTE. They do, however, usually end up easier to follow.

Your query is going to perform pretty poorly regardless because your WHERE clause is a conditional against a somewhat complicated CASE statement. Because of this, the optimizer will need to evaluate the CASE statement on every row so it cannot use an index to narrow it's search.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Just to add on michal's answer. Few months ago Itzik Ben Gun published 2 articles in SQL Server Magazine about derived tables, CTE, table varibels and temporary tables. If you have access to SQL Server magazine, I recommend that you'll read it:

http://www.sqlmag.com/Article/ArticleID/96288/sql_server_96288.html

http://www.sqlmag.com/Article/ArticleID/96479/Debunking_the_Myths_of_Temporary_Objects_Part_2.html

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/