Is There a Performance Difference Between Cte , Sub-Query, Temporary Table or Table Variable

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.

CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?

We got a 50% increase in speed moving to CTE in one particular case so it's worth giving it a go but any performance related enhancements need to be bench marked so you can compare one against another.

PS: we wrote more than one query with a CTE in it before we got the one we now use.

Which are more performant, CTE or temporary tables?

I'd say they are different concepts but not too different to say "chalk and cheese".

  • A temp table is good for re-use or to perform multiple processing passes on a set of data.

  • A CTE can be used either to recurse or to simply improved readability.

    And, like a view or inline table valued function can also be treated like a macro to be expanded in the main query

  • A temp table is another table with some rules around scope

I have stored procs where I use both (and table variables too)

Difference between CTE, Temp Table and Table Variable in MSSQL

CTE - Common Table Expressions
CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

When to use CTE?
This is used to store result of a complex sub query for further use.

This is also used to create a recursive query.

Temporary Tables
In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

Local Temp Table
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

CREATE TABLE #LocalTemp
(
UserID int,
Name varchar(50),
Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from #LocalTemp

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

Global Temp Table
Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp
(
UserID int,
Name varchar(50),
Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from ##GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable
This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
DECLARE @TProduct TABLE
(
SNo INT IDENTITY(1,1),
ProductID INT,
Qty INT
)
--Insert data to Table variable @Product
INSERT INTO @TProduct(ProductID,Qty)
SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC
--Select data
Select * from @TProduct

--Next batch
GO
Select * from @TProduct --gives error in next batch

Note
Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.

Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

Why is there a HUGE performance difference between temp table and subselect

Why it's not recommended to use subqueries?

Database Optimizer (regardless of what database you are using) can not always properly optimize such query (with subqueries). In this case, the problem to the optimizer is to choose the right way to join result sets. There are several algorithms for joining two result sets. The choice of the algorithm depends on the number of records which are contained in one and in the other result set. In case if you join two physical tables (subquery is not a physical table), the database can easily determine the amount of data in two result sets by the available statistics. If one of result sets is a subquery then to understand how many records it returns is very difficult. In this case the database can choose wrong query plan of join, so that will lead to a dramatic reduction in the performance of the query.

Rewriting the query with using temporary tables is intended to simplify the database optimizer. In the rewritten query all result sets participating in joins will be physical tables and the database will easily determine the length of each result set. This will allow the database to choose the guaranteed fastest of all possible query plans. Moreover, the database will make the right choice no matter what are the conditions. The rewritten query with temporary tables would work well on any database, this is especially important in the development of portable solutions. In addition, the rewritten query is easier to read, easier to understand and to debug.

It is understood that rewriting the query with temporary tables can lead to some slowdown due to additional expenses: creation of temporary tables. If the database will not be mistaken with the choice of the query plan, it will perform the old query faster than a new one. However, this slowdown will always be negligible. Typically the creation of a temporary table takes a few milliseconds. That is, the delay can not have a significant impact on system performance, and usually can be ignored.

Important! Do not forget to create indexes for temporary tables. The index fields should include all fields that are used in join conditions.

Why CTE is so slow comparing to Temp tables?

There are two reasons.

Probably the more important reason is that SQL Server does not materialize CTEs. So, for every reference, SQL Server recalculates the entire CTE. As far as I know, SQL Server also does not do common subquery optimizations on the execution DAG, so it always regenerates the CTES (although the execution plans might be different for each instance).

The second reason is that temporary tables have statistics, and these statistics can inform the query plan to create a better plan.

I suspect that you can simplify the logic. However, you would need to ask a new question with an explanation of what you want to do, along with sample data and desired results.

When to use cte and temp table?

With a temp table you can use CONSTRAINT's and INDEX's. You can also create a CURSOR on a temp table where a CTE terminates after the end of the query(emphasizing a single query).

I will answer through specific use cases with an application I've had experience with in order to aid with my point.

Common use cases in an example enterprise application I've used is as follows:


Temp Tables

Normally, we use temp tables in order to transform data before INSERT or UPDATE in the appropriate tables in time that require more than one query. Gather similar data from multiple tables in order to manipulate and process the data.

There are different types of orders (order_type1, order_type2, order_type3) all of which are on different TABLE's but have similar COLUMN's. We have a STORED PROCEDURE that UNION's all these tables into one #orders temp table and UPDATE's a persons suggested orders depending on existing orders.

CTE's

CTE's are awesome for readability when dealing with single queries. When creating reports that requires analysis using PIVOT's,Aggregates, etc. with tons of lines of code, CTE's provide readability by being able to separate a huge query into logical sections.


Sometimes there is a combination of both. When more than one query is required. Its still useful to break down some of those queries with CTE's.


I hope this is of some usefulness, cheers!

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.



Related Topics



Leave a reply



Submit