Improve performance of a query

Question

Hi, This question is regarding one of my previous posts. I am not sure which would be the best way to write this query.

Link to Original Post:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/94f8905e-7005-490e-a903-c36f322f9db4

Is the performance better if I re-write the select statments for MTD, YTD as a CTE and join based on the conditions I need to. I tried doing it but no idea which would perform better. I have the execution plans .sqlplan file how do I attach them?

Answer 1

Best to upload the XML execution plan to a sharing site like skydrive.com .

Before you do that, make sure there are indexes on each JOIN keys and WHERE condition columns.

What is your index maintenance schedule?  STATISTICS UPDATE?

Check point by point:

http://www.sqlusa.com/articles/query-optimization/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design

Answer 2

Well, let me re-phrase what I am trying to do. I am trying to weigh 2 different approach of writing SQL of which would be better.

Having MTD, YTD columns from a derived table in the original post (Named execution plan as Select Plan) or changing the MTD, YTD columns from the original post to CTE (Named execution plan as CTE Plan) CTE Plan:

Please help me understand the execution plans which would be better.

Thanks..................

Select Plan:

Answer 3

Click on Include Actual Execution Plan, run both queries together.

What are the relative costs?

Generally there is no performance difference between CTE and derived table solutions.

Can you post DDL (CREATE TABLE, indexes)? Thanks.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design

Answer 4

Hi Kalman, These are the actual execution plans. I tried running them together and they both take the same time but I was not so sure on how to interpret the execution plan and they are part of a stored procedure which does some calculations and this piece of code I am trying to fix is actually an intermediary resultset. So CTE's and Derived Tables should not have much difference in performance right?

Thanks........