August 25, 2011 at 11:00 am
If I have a query that contains 5 SELECTs that are UNION'd (via UNION ALL) and each of the % has the same 4 Derived Tables that use the same Logic then I would think that if replacing one of these derived tables in all 5 SELECTS with a CTE boosts performance then doing the same for the remaing 4 Derived Tables would also boost performance.
While replacing the one derived tabel did improve the queries performance it seems that when I raplced the remaing 4 derived tables with 4 CTE's that the querys performance went down to where it was less effecient then the query with 1 CTE and 3 derived tables.
Is there some general rule regarding the use CTE's and using them to repalce Derived Tables? I would have thought that at a minimum the revised query that coatins the 5 SELECTS with no Derviced Tables and using 4 CTE's would perform on par with the original query if not better and yet that does not seem to be the case.
Thoughts?
NOTE: I would provde the DML but because of an NDA with the software vendor whose product uses the database this query is for, I can't post the actual code.
I would provide a substitute query (as an exmaple) using the PUBS or Adventureworks DB but the query is so big and complex with 5 SELECTs strung togther via the UNION ALL and each SELECT containing no less then half a dozen table joines (mostly Left Outer) that I doubt I could properly convey the thing and it would take a very long time.
What I will do for what its worth is provide a psudeocode example outlinging the first of the 5 SELCTS that are pulled together via UNION ALL. This is just a pseudo like example:
SELECT *
FROM TABLEA TA Join TABLEB TB ON TA.PK = TB.FK
Join TABLEC TC ON TB.PK = TC.FK
Left Outer Join TABLED TD ON TC.PK = TD.FK
Left Outer Join TABLEE TE ON TD.PK = TE.FK
Left Outer Join TABLEF TF ON TE.PK = TF.FK
Left Outer Join ( SELECT Max(OH1.PK) AS 'ID', OH1.MyObjectID AS 'MyObject'
FROM OBJECT_HISTORY OH1
WHERE OH1.dtAsOf <= @dtComparisonDate
GROUP BY OH1.MyObjectID
)
MAXOH ON MAXOH.MyObjectID = TF.PK
Left Outer Join OBJECT_HISTORY OH ON OH.PK = MAXOH.ID AND OH.FK = TF.PK
Left Outer Join ( SELECT Max(OS.PK) AS 'ID', OS.MyObjectID AS 'MyObject'
FROM OBJECT_STATUS OS
WHERE OS.dtAsOf <= @dtComparisonDate
GROUP BY OS.MyObjectID
)
MAXOS ON MAXOS.MyObjectID = TF.PK
UNION ALL
..........
BTW - I know the above looks horrific. Its not my own query, just one I am trying to re-vamp to improve perofrmance and I thought replacing the derived tables with CTE's would do this.
Thanks
Kindest Regards,
Just say No to Facebook!August 25, 2011 at 12:33 pm
A CTE is a derived table. So, saying which one is faster or not, is not really possible per se. It depends on how they're put together, how they're called, what kind of execution plans get created from them... There isn't a "this is good when" and "that one is good when" kind of set of rules you can work from.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2011 at 4:54 pm
Grant Fritchey (8/25/2011)
A CTE is a derived table. So, saying which one is faster or not, is not really possible per se. It depends on how they're put together, how they're called, what kind of execution plans get created from them... There isn't a "this is good when" and "that one is good when" kind of set of rules you can work from.
Thanks Grant.
BTW - Implementing the CTE in place of the multiple derived tables in this masive query that I changed has made a big difference. Man I love CTE's.
Kindest Regards,
Just say No to Facebook!Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply