SQL Server variable table temporary table Analysis

Recently, a friend of mine told me that the execution of many stored procedures in his database has timed out. let me show him why. I saw that many temporary tables and variable tables were used in his stored procedures. so I told him that I was too late.

Using temporary tables or variable tables during storage can improve the speed and result in adverse effects. then, I gave him several examples for him to view and modify his database.

Is the table variable in the memory? Not necessarily.

Generally, when the table variable contains less data, the table variable exists in the memory. However, when the table variable retains a large amount of data and the memory cannot accommodate it, it must have a location on the disk to store the data. Similar to a temporary table, the table variable is in
TempdbCreated in the database. If enough memory is available, both the table variables and temporary tables are created and processed in the memory (data cache.

Note:

1) CPU-the CPU time used by the event (SQL statement) (in milliseconds ).

2) reads: The number of times the server reads the Logical Disk from the event. These read operations include the number of times the table and buffer are read during statement execution.

3) writes: the number of times the event is written to a physical disk by the server.

 

 

Example 1. Variable table

1) 10000 records

Declare @ t table
(
Id nvarchar (50 ),
Supno nvarchar (50 ),
ETA datetime
)
Insert @ t

Select top 10000 ID, supno, ETA from table

 

-- CPU: 125 reads: 13868 writes: 147

-- Table '# 286302ec '. Scan count 0, logical read 10129, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.

-- Table 'table '. Scan count 1, logical reads 955, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.

Declare @ t table
(
Id nvarchar (50 ),
Supno nvarchar (50 ),
ETA datetime
)
Insert @ t

Select Top 1000 ID, supno, ETA from table

 

-- CPU: 46 reads: 2101 writes: 17
-- Table '# 44ff419a '. Scan count 0, logical read 1012, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.
-- Table 'table '. Scan count 1, logical reads 108, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.

-- Example 2. Temporary table:

 

Create Table # T
(
Id nvarchar (50 ),
Supno nvarchar (50 ),
ETA datetime
)
End
Insert # T
Select top 10000 ID, supno, ETA
From table

-- CPU: 125 reads: 13883 writes: 148
-- Table '# t00000000005 '. Scan count 0, logical read 10129, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.
-- Table 'table '. Scan count 1, logical reads 955, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.

Create Table # T
(
Id nvarchar (50 ),
Supno nvarchar (50 ),
ETA datetime
)

Insert # T
Select Top 1000 ID, supno, ETA
From table

-- CPU: 62 reads: 2095 writes: 17

-- Table '# t1_0000003 '. Scan count 0, logical read 1012, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.
-- Table 'table '. Scan count 1, logical reads 108, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.

 

-- Example 3. Insert a temporary table directly to the temporary table without creating a temporary table.

Select top 10000 ID, supno, ETA
Into # T
From table

-- CPU: 31 reads: 1947 writes: 83

-- Table 'table '. Scan count 1, logical reads 955, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.

Select Top 1000 ID, supno, ETA
Into # T
From table

-- CPU: 0 reads: 997 writes: 11

-- Table 'table '. Scan count 1, logical reads 108, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.

From the above analysis, we can see that if the 3) method is used, a temporary table will be created less, and the read and write operations in IO will be reduced.

1) and 2) both have the action of creating a temporary table first, and perform the corresponding Io read operation.

From the perspective of the CPU usage of the server using SQL statements, the CPU usage is also relatively low in the third case.

From the perspective of physical write operations to the disk, there are fewer physical writes in the third case.

 

Under what circumstances should we use table variables to replace temporary tables:

It depends on the following three factors:

•	The number of rows inserted to the table. I think it is better to have less than 1000 rows.
•	The number of times the query is recompiled.
•	Query type and its dependence on performance index and statistical information.

In some cases, you can split a stored procedure with a temporary table into multiple smaller stored procedures for recompilation on smaller units.

We recommend that you use table variables whenever possible when the record row is smaller than 1000 rows, unless the data volume is large (more than 1000 rows) and the table needs to be reused. In this case, you can create an index on a temporary table to improve query performance. However, different solutions may be different.

Microsoft recommends that you perform a test to verify whether table variables are more effective for specific queries or stored procedures than temporary tables.