Temporary Tables vs. CTEs – The Ultimate Showdown!

Blogs

Exploring Time Series Analysis: Techniques for Data Smoothing and Forecasting
July 22, 2024
Effortless Partition Management : Automate your SQL server partitions
July 27, 2024

Temporary Tables vs. CTEs – The Ultimate Showdown!

Introduction:

Overview: In the competitive world of IT, where efficiency and performance are paramount, database management often poses significant challenges. One of our esteemed customers, a leading IT firm with over 3,000 SQL Server licenses and more than 400 applications in their portfolio, encountered a critical issue impacting their operational efficacy. As their daily processing jobs, application performance, and quarter-end activities began to suffer, a deeper investigation revealed the root cause: severe tempdb contention.

Tempdb contention is a critical bottleneck in our customer environments, particularly under high-demand scenarios.  This system database, essential for temporary storage and intermediate results, became a critical pain point for our customer, significantly hindering their performance and operational stability.

In this blog, we delve into the specifics of tempdb contention, examining its impact and exploring strategies to mitigate such challenges. By leveraging both Temporary Tables and Common Table Expressions (CTEs), we aim to provide a comprehensive guide to optimize database performance and reduce contention in high-stakes environments. Join us as we navigate through practical solutions and best practices, empowering you to enhance your SQL Server management and ensure seamless operations.

What is a CTE (Common Table Expression)?

A Common Table Expression (CTE) is a temporary result set in SQL Server that you can use to simplify complex queries. It helps make your SQL queries more readable and easier to manage. Think of it as a way to create a temporary table that you can use just for the duration of a single query.

How Does a CTE Work?

  1. Temporary Table: A CTE acts like a temporary table that you define within a query. You can use it to store intermediate results that can be referenced within the same query.
  2. Readability: It makes complex queries easier to read and maintain. Instead of writing long, nested queries, you can break them into simpler parts using a CTE.
  3. Scope: A CTE exists only for the duration of the query. Once the query execution is complete, the CTE is no longer available.

What is a Temporary Table?

A temporary table in SQL Server is a special type of table that is created and used during the execution of a single session or query. Temporary tables allow you to store and manipulate intermediate results temporarily. They are automatically deleted when they are no longer in use or when the session ends.

How Does a Temporary Table Work?

  1. Creation: You create a temporary table using the CREATE TABLE statement, but the table name is prefixed with a # (single #) for local temporary tables or ## (double #) for global temporary tables.
  2. Scope:
    • Local Temporary Tables: Available only to the session that created them. They are deleted automatically when the session ends.
    • Global Temporary Tables: Available to all sessions and users until the last session using the table ends.
  3. Usage: You can insert, update, and query data in temporary tables just like regular tables.
  4. Automatic Cleanup: SQL Server automatically removes local temporary tables when the session ends, and global temporary tables when the last session referencing them ends.

Types of Temporary Tables

  1. Local Temporary Table (#TempTable):
    • Scope: Limited to the session that created it.
    • Example: #EmployeeTemp
  2. Global Temporary Table (##TempTable):
    • Scope: Available to all sessions and users.
    • Example: ##EmployeeGlobalTemp

How Temp Tables Cause Contention:

Here is a small demonstration how cte and temp table works:

Step 1: create  a table with 50 columns

CREATE TABLE [dbo].[Orders](

[OrderID] [int] IDENTITY(1,1) NOT NULL,

[CustomerName] [varchar](100) NULL,

[OrderDate] [datetime] NULL,

[ProductID] [int] NULL,

[Quantity] [int] NULL,

[Price] [decimal](18, 2) NULL,

[Status] [varchar](50) NULL,

[ShippingAddress] [varchar](200) NULL,

[Country] [varchar](50) NULL,

[Comments] [varchar](1000) NULL,

[Column11] [varchar](50) NULL,

[Column12] [varchar](50) NULL,

[Column13] [varchar](50) NULL,

[Column14] [varchar](50) NULL,

[Column15] [varchar](50) NULL,

[Column16] [varchar](50) NULL,

[Column17] [varchar](50) NULL,

[Column18] [varchar](50) NULL,

[Column19] [varchar](50) NULL,

[Column20] [varchar](50) NULL,

[Column21] [varchar](50) NULL,

[Column22] [varchar](50) NULL,

[Column23] [varchar](50) NULL,

[Column24] [varchar](50) NULL,

[Column25] [varchar](50) NULL,

[Column26] [varchar](50) NULL,

[Column27] [varchar](50) NULL,

[Column28] [varchar](50) NULL,

[Column29] [varchar](50) NULL,

[Column30] [varchar](50) NULL,

[Column31] [varchar](50) NULL,

[Column32] [varchar](50) NULL,

[Column33] [varchar](50) NULL,

[Column34] [varchar](50) NULL,

[Column35] [varchar](50) NULL,

[Column36] [varchar](50) NULL,

[Column37] [varchar](50) NULL,

[Column38] [varchar](50) NULL,

[Column39] [varchar](50) NULL,

[Column40] [varchar](50) NULL,

[Column41] [varchar](50) NULL,

[Column42] [varchar](50) NULL,

[Column43] [varchar](50) NULL,

[Column44] [varchar](50) NULL,

[Column45] [varchar](50) NULL,

[Column46] [varchar](50) NULL,

[Column47] [varchar](50) NULL,

[Column48] [varchar](50) NULL,

[Column49] [varchar](50) NULL,

[Column50] [varchar](50) NULL)

Step 2: Insert one crore records using SQL Script that generates imaginary values.

DECLARE @counter INT = 1;

DECLARE @maxRecords INT = 10000000; — 1 crore records

 

WHILE @counter <= @maxRecords

BEGIN

INSERT INTO Orders (CustomerName, OrderDate, ProductID, Quantity, Price, Status, ShippingAddress, Country, Comments,

Column11, Column12, Column13, Column14, Column15, Column16, Column17, Column18, Column19, Column20,

Column21, Column22, Column23, Column24, Column25, Column26, Column27, Column28, Column29, Column30,

Column31, Column32, Column33, Column34, Column35, Column36, Column37, Column38, Column39, Column40,

Column41, Column42, Column43, Column44, Column45, Column46, Column47, Column48, Column49, Column50)

VALUES

(

‘Customer’ + CAST(@counter AS NVARCHAR(50)),

DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 546, ‘2023-01-01’),  — Random date between 2023-01-01 and 2024-07-01

ABS(CHECKSUM(NEWID())) % 1000 + 1,  — Random ProductID between 1 and 1000

ABS(CHECKSUM(NEWID())) % 10 + 1,  — Random Quantity between 1 and 10

CAST(ABS(CHECKSUM(NEWID())) % 50000 AS FLOAT) / 100,  — Random Price between 0.00 and 500.00

CASE ABS(CHECKSUM(NEWID())) % 3

WHEN 0 THEN ‘Pending’

WHEN 1 THEN ‘Shipped’

ELSE ‘Canceled’

END,  — Random Status

CAST(ABS(CHECKSUM(NEWID())) % 900 + 100 AS NVARCHAR(10)) + ‘ Main St, City’ + CAST(@counter AS NVARCHAR(50)),

CASE ABS(CHECKSUM(NEWID())) % 10

WHEN 0 THEN ‘USA’

WHEN 1 THEN ‘Canada’

WHEN 2 THEN ‘UK’

WHEN 3 THEN ‘Germany’

WHEN 4 THEN ‘France’

WHEN 5 THEN ‘Italy’

WHEN 6 THEN ‘Spain’

WHEN 7 THEN ‘Australia’

WHEN 8 THEN ‘India’

ELSE ‘Brazil’

END,  — Random Country

‘No comments’,

‘Column11’, ‘Column12’, ‘Column13’, ‘Column14’, ‘Column15’, ‘Column16’, ‘Column17’, ‘Column18’, ‘Column19’, ‘Column20’,

‘Column21’, ‘Column22’, ‘Column23’, ‘Column24’, ‘Column25’, ‘Column26’, ‘Column27’, ‘Column28’, ‘Column29’, ‘Column30’,

‘Column31’, ‘Column32’, ‘Column33’, ‘Column34’, ‘Column35’, ‘Column36’, ‘Column37’, ‘Column38’, ‘Column39’, ‘Column40’,

‘Column41’, ‘Column42’, ‘Column43’, ‘Column44’, ‘Column45’, ‘Column46’, ‘Column47’, ‘Column48’, ‘Column49’, ‘Column50’

);

 

SET @counter = @counter + 1;

END;

PRINT ‘Finished inserting records.’;

Step 3: Once after inserting the record, check the performance of the CTE like time taken, and memory,cpu utilization

SET STATISTICS TIME ON;

WITH CTE AS (

SELECT Country, COUNT(*) AS OrderCount

FROM Orders

GROUP BY Country

)

SELECT *

FROM CTE

WHERE OrderCount > 5000;

SET STATISTICS TIME OFF;

The elapased time and the cpu time taken for the above the code is :

The memory allocated by the above query in KB is:

The allocation of the cte in tempdb

The alloacation of cte in tempdb is zero kb

Step 4: , check the performance of the Temp tables like time taken, and memory,cpu utilization and their allocation space in tempdb

SET STATISTICS TIME ON;

CREATE TABLE #TempOrders (

Country NVARCHAR(50),

OrderCount INT

);

 

INSERT INTO #TempOrders (Country, OrderCount)

SELECT Country, COUNT(*) AS OrderCount

FROM Orders

GROUP BY Country;

 

SELECT *

FROM #TempOrders

WHERE OrderCount > 5000;

 

DROP TABLE #TempOrders;

SET STATISTICS TIME OFF;

The elapsed time and cpu time for the above code is :

The memory allocated for the above code in KB is:

The allocation of temporary table in tempdb:

The allocation size of user created objects in tempdb is 192 KB

Differences Observed Between Temporary Tables and CTEs

CTEs:

  • Limited to a single SELECT statement. It is defined within the execution scope of a single statement.
  • Generally optimized by SQL Server for recursive queries and simple hierarchical data retrieval. However, for large datasets or complex queries, performance may degrade.
  • Stored in memory during the execution of the statement, which can be efficient for smaller datasets.
  • Ideal for recursive queries, hierarchical data retrieval, and simplifying complex queries into more readable blocks.
  • Simpler to use and maintain as they do not require explicit creation and dropping.

Temporary Tables:

  • Can be referenced in multiple statements within the same session or stored procedure.
  • Typically faster for large datasets or when the data needs to be reused multiple times within the session. SQL Server can create indexes on temporary tables, enhancing performance for complex queries.
  • Stored in the tempdb database, which can handle larger datasets more efficiently. However, this can put a load on the tempdb system, affecting performance.
  • Suitable for scenarios requiring multiple manipulations of data, such as intermediate storage of results, complex joins, and operations across several steps.
  • Can have indexes defined, including primary keys and unique constraints, which can significantly improve query performance.
  • Require explicit creation and dropping along with indexes, which adds to the maintenance effort and till the session is closed the user created object wont be de allocated in the systems.
  • Additional overhead on the tempdb system, especially with large datasets and frequent use.

Conclusion

According to the above example, both the temporary table and the CTE provide similar output and execution time, using the same amount of resources. However, the overhead caused by the temporary table includes allocation space in tempdb, which is not deallocated until the session is closed.

If this temporary table code runs in 100 sessions, the objects remain allocated until each session ends. If this scenario scales to 1000 sessions, it can lead to tempdb contention.

Therefore, it is clear that for applications requiring data for simple calculations and transformations, it is more efficient to use CTEs. This approach avoids potential tempdb contention that can occur on a larger scale with temporary tables.

 

Happy Reading !!

 


Thejas K

Leave a Reply

Your email address will not be published. Required fields are marked *