Tbl Var vs CTE

The system I write queries for, runs queries per product. So if there are 3k products, the query will run 3k times. This is by design and cannot be changed. There are variables established for various parameters (product, cycle (month/year), etc). Many of the queries are very complex and originally have the parameter variables listed many times. My job is to performance tune these queries and make them more readable.

My first thought was to create a CTE to isolate the parameters once, then feed that to the rest of the query. Thinking this would limit the rows/records the query would have to go through to get to the final result.

Now I am not so sure that is the best design. Would creating a table variable be more efficient, since it is only isolating on one record per query iteration?

It is hard to answer your question without seeing the query. However, you can evaluate the query yourself and see where the resources are being spent by looking at the execution plan. To view the execution plan, in an SSMS query window, run the query after selecting the Query -> include Actual Execution Plan from the top menu (or Control-M). That will run the query as usual, but also will show you a new tab with the execution plan. Even without knowing a whole lot about all the information it is giving you, you can see what percentage of resources each part of your query is taking up. Then try to optimize based on that.

If you do post the actual query you are trying to optimize, there are many people on the forum who might be able to offer suggestions.

1 Like

I'd definitely need to see some code to get my head around how they might be optimised.

Thank you everyone for your feedback. I realize that most answers to SQL questions is "it depends". There are 400+ queries that I am optimizing. I was looking more for a theoretical answer. However, I will give JamesK's suggestion a go. I'm still new at reading execution plans, so I may come back and post a query example.

Tbl Var vs CTE = neither!

  1. try temp tables with indices
  2. don't use loops

If the instance didn't close between each query run, that might work better. But that's not the case. Running SQL through 3rd party applications is tricky.