CODEWITHSUNDEEP
sql-servert-sql
NikRED
NikRED

Reputation: 1195

What is the reason for performance difference?

I am creating a report, for that I have written 2 different type of query. But I am seeing a huge performance difference between these 2 methods. What may be the reason? My main table (suppose table A) contain a date column. I am filtering the data based on date. Around 10 table join I have to do with this table.

First method:

select A.id,A1.name,...
from table A
join A1
join A2 ....A10
where A.Cdate >= @date
and A.Cdate <= @date

Second method:

With CTE as(select A.id from A where A.Cdate>=@date and A.Cdate<=@date)
select CTE.id, A1.name,.... from CTE join A1 join A2....A10

Here second method is fast. What is the reason? In first method, the filtered data of A only will be join with other tables data right?

Upvotes: 0

Views: 67

Answers (3)

NG.
NG.

Reputation: 6043

CTE is basically for the handling the more complex code(be it recursive or having subquery), but you need to check the execution plan regarding the improvement of the 2 different queries. You can check for the use of CTE at : http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Just a note, in many scenarios, temp tables gives better performance then CTE also, so you should give a try to temp tables as well.
Reference : http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d040d19d-016e-4a21-bf44-a0359fb3c7fb

Upvotes: 0

Gulli Meel
Gulli Meel

Reputation: 891

One suggestion.You should be able to answer this question yourself as you have both the plans. Did you compare the two plans? Are those similar? Also, when performance is bad what do you mean ,is it time or cpu time or IO's or what did you compare?

Thus before you post any question you should check these counters and I am sure they will provide with some kind of answers in most of cases.

CTE is for managing the code it wont improve the performance of query automatically. CTE's will be expanded by optimizer and thus in your case these both should have same queries after transformation or expansion and thus similar plans.

Upvotes: 1

anon
anon

Reputation:

Execution plans will tell us for sure, but likely if the CTE is able to filter out a lot of rows before the join, a more optimal join approach may have been chosen (for example merge instead of hash). SQL isn't supposed to work that way - in theory those two should execute the same way. But in practice we find that SQL Server's optimizer isn't perfect and can be swayed in different ways based on a variety of factors, including statistics, selectivity, parallelism, a pre-existing version of that plan in the cache, etc.

Upvotes: 1

Related Questions