Table variables and temporary tables

Table variables are stored in the memory, while temporary tables are stored in tempdb, which involves physical I/O reads and writes. Can we conclude that, it is more efficient to use table variables than to use temporary tables? I believe some people will share the same idea with me. It is highly efficient to use table variables. Is that true? Let's start with an experience of optimizing the storage process.

The stored procedure involves two tables: one is the user's current point Table @ tableuserscore (the data source is from the data of today in the user point Details table) and the other is the user point statistics table userscoresum, the stored procedure logic is to count the user's points for different reasons in @ tableuserscore and generate them to the userscoresum table. The data volume is not very large. @ tableuserscore contains about 0.4 million records, but the execution time of this stored procedure is somewhat astonishing, usually over 1 hour. The final result of optimization is to replace the table variable @ tabeuserscore with a temporary table # tableuserscore, and add a joint index on userid and reason. The optimization effect is that the execution time is controlled at around 40 s. The efficiency of temporary tables and table variables is a hundred times different. This optimization experience gave me a new understanding of temporary tables and table variables, and also gave me a series of questions about how they are stored, how is efficiency and how to choose?

Declare @ tableuserscore table (userid int, -- User ID name varchar (10), -- User Name reason varchar (32), -- point reason score int -- point value) create Table userscoresum (userid int, -- User ID name varchar (10), -- User Name createtime datetime, -- time reason1score int, -- cause 1 credit value reason2score int, -- cause 2 point value reason3score int, -- cause 3 point value reason4score int, -- cause 4 point value)

Table Structure

The following is my understanding after reading the materials. I hope to remind people I know the same, and I hope you can correct the mistakes.

Temporary table

Temporary tables can be local tables or Global tables. During the connection of the same SQL server instance when you create or reference a table for the first time, the local temporary table is only visible to the Creator. When the user is disconnected from the SQL server instance, the local temporary table is deleted. After a global temporary table is created, it is visible to any user and any connections. When all users that reference the table are disconnected from the SQL server instance, the global temporary table is deleted. The names of local temporary tables are prefixed with "#", and the names of global temporary tables are prefixed.

Temporary tables are stored in tempdb. Therefore, access to temporary tables may cause physical Io. Of course, logs must be generated during modification to ensure consistency, and the lock mechanism is also indispensable.

Temporary tables can create indexes or define statistical data. Therefore, you can use the Data Definition Language (DDL) statement to block restrictions and constraints added to temporary tables, and reference integrity, such as primary key and foreign key constraints.

Table Variables

Table variables are one type of variables. Table variables are also divided into local and global variables. The names of local table variables are prefixed, only the local user connection can be accessed. The names of global table variables are prefixed with "@". Generally, they are system global variables. For example, @ error indicates the error number, @ rowcount indicates the number of affected rows.

Table variables are stored in the memory, because SQL server does not need to generate logs when all users access table variables. At the same time, variables do not need to consider other session access issues, so they do not need the lock mechanism. For very busy systems, avoiding the use of the lock can reduce part of the system load. [There are limits on table variables stored in the memory. If the table variable data volume exceeds the threshold, the memory will be used up and the tempdb space will be used up. In this case, the hard disk space will be used, but at the same time, the memory is basically exhausted, increasing the chance of memory calling and calling, but reducing the speed]

Another restriction on table variables is that indexes cannot be created. Of course, there is no statistical data problem, therefore, when users access table variables, there is no Execution Plan Selection Problem (that is, they think there is no optimization stage after the compilation stage). This feature is a good thing sometimes, in some cases, it may cause some trouble.

Temporary table vs. Table variable

1. storage location: the temporary table uses the hard disk (tempdb database), and the table name variable occupies the memory. Therefore, the small data size is of course the table variable in the memory is faster. When there is a large amount of data, you cannot use table variables, which consumes too much memory. Temporary tables are suitable for large data volumes.

2. Performance: it cannot be generalized. Table variables store data at a critical point of personality. Within this critical point, table variables are faster than temporary tables, and table variables are stored in memory.

3. Index: Table variables do not support indexes and statistical data, but can have primary keys. Temporary tables support indexes and statistical data.

We consider using Table variables for small temporary computation datasets. If the dataset is large, if it is used for temporary computing in the code, and such temporary use is always a simple full dataset scan without any optimization, for example, table variables can be used if no group or few groups are aggregated (such as Count, sum, average, and Max. Another consideration for using Table variables is the memory pressure in the application environment. If there are many running instances of code, pay special attention to the memory consumption caused by memory variables. Generally, we recommend that you use temporary tables for large datasets and create indexes at the same time.

Table variables and temporary tables