Difference Between Cte and Subquery

Difference between CTE and SubQuery?

In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can't tell you the answer in full).

In general; A CTE can be used recursively; a sub-query cannot. This makes them especially well suited to tree structures.

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

SQL is a declarative language, not a procedural language. That is, you construct a SQL statement to describe the results that you want. You are not telling the SQL engine how to do the work.

As a general rule, it is a good idea to let the SQL engine and SQL optimizer find the best query plan. There are many person-years of effort that go into developing a SQL engine, so let the engineers do what they know how to do.

Of course, there are situations where the query plan is not optimal. Then you want to use query hints, restructure the query, update statistics, use temporary tables, add indexes, and so on to get better performance.

As for your question. The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. The results could then be stored and read multiple times. Unfortunately, SQL Server does not seem to take advantage of this basic optimization method (you might call this common subquery elimination).

Temporary tables are a different matter, because you are providing more guidance on how the query should be run. One major difference is that the optimizer can use statistics from the temporary table to establish its query plan. This can result in performance gains. Also, if you have a complicated CTE (subquery) that is used more than once, then storing it in a temporary table will often give a performance boost. The query is executed only once.

The answer to your question is that you need to play around to get the performance you expect, particularly for complex queries that are run on a regular basis. In an ideal world, the query optimizer would find the perfect execution path. Although it often does, you may be able to find a way to get better performance.

Is there any performance difference btw using CTE, view and subquery?

CTE is just syntax so in theory it is just a subquery. you may not get any performance difference while using CTE and Subquery.

I think the biggest benefit for using CTEs is readability. It makes it much easier to see what queries are being used as subqueries, and then it's easy to join them into a query, much like a view.

Now about CTE and View Hope you can read this and see what is suitable for you.

CTE vs View Performance in SQL Server

SQL - CTE vs VIEW

Its also depends how you designed you query and script structure.

SQL: is there ever any reason to use a subquery instead of a CTE?

Any query that you can write using only subqueries in the FROM clause and regular joins can use CTEs with direct substitution.

Subqueries are needed for:

• Correlated subqueries (which are generally not in the FROM clause).
• Lateral joins (in databases that support LATERAL or APPLY keywords in the FROM clause).
• Scalar subqueries.

Sometimes, a query could be rewritten to avoid these constructs.

Subqueries in the FROM clause (except for lateral joins) can be written using CTEs.

Why are subqueries used and not CTEs? The most important reason is that CTEs are a later addition to the SQL language. With the exception of recursive CTEs, they are not really needed. They are really handy when a subquery is being referenced more than one time, but one could argue that a view serves the same purpose.

As mentioned in the comments, CTEs and subqueries might be optimized differently. This could be a performance gain or loss, depending on the query and the underlying indexes and so on.

SQL - CTE vs VIEW

Views can be indexed but CTE can't. So this is one important point.

CTE work excellent on tree hierarchyi.e. recursive

Also, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide flexibility and centralized approach. CTE, on the other hand are temporary and will be created when they are used; that's why they are called as inline view.

Update

According to your updated question, views will be the right choice. Dealing with 3.5 million rows in CTE will create extra overhead on TempDb which will eventually slow down SQL Server performance. Remember, CTE is a disposable view hence no statistics are stored and you can't create Indexes too. It is just like a sub query.

---

Related Topics