Optimization of SQL Server Stored Procedures (Simple Analysis of variable tables and temporary tables ))

Yesterday, a friend sent a piece of SQL stored procedure (as shown below). Let me see if it can be optimized.
Insert @ T1
Select g_no, co_no, si_no, str_no, sum (ind_qty) as Qty
From instock_detail where in_id = @ ID group by g_no, co_no, si_no, str_no

--? Unitstock --> save in the variable table
Insert @ T2
Select .*
From unitstock A, @ t1 B
Where a. g_no = B. g_no and A. co_no = B. co_no
And a. si_no = B. si_no and A. str_no = B. str_no
 
Delete unitstock
From @ T1
Where unitstock. g_no = A. g_no and unitstock. co_no = A. co_no
And unitstock. si_no = A. si_no and unitstock. str_no = A. str_no

Insert unitstock
Select g_no, co_no, si_no, str_no, sum (qty) as qty from
(Select * From @ T1 Union all select * From @ T2
) AA
Group by g_no, co_no, si_no, str_no

Today I am free. I made a variable table to analyze the performance of temporary table data insertion.
1. Variable table:

Declare @ t table
(
Id nvarchar (50 ),
Supno nvarchar (50 ),
ETA datetime
)
Insert @ t
Select top 10000 ID, supno, ETA from table

This statement takes 16806 ms to execute the SQL statement.

2. Temporary table:

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

This statement takes time to execute SQL: 76 Ms

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

It takes 30 ms to execute the SQL statement.

Through the above analysis, we can clearly see the advantages and disadvantages.

The above is a simple analysis. Therefore, try to use a temporary table to store temporary data during the storage process. Do not use a variable table.

 

 

////
You can declare functions and variables as table types. Table variables can be used in functions, stored procedures, and batch processing.

Try to use table variables instead of temporary tables. Table variables have the following advantages:

The behavior of table variables is similar to that of local variables and has a clearly defined scope. This scope is the function, stored procedure, or batch processing that declares the variable.
In its scope, table variables can be used as regular tables. This variable can be applied to tables or table expressions in select, insert, update, and delete statements. However, table cannot be used in the following statements:

Insert into table_variable exec stored procedure.

Select select_list into table_variable statement.

When the function, stored procedure, or batch processing of the table variable is defined, the table variable is automatically cleared.

Using table variables in a stored procedure reduces the amount of recompilation in a stored procedure compared to using a temporary table.

Transactions involving table variables only exist during table variable update. This reduces the need for table variables to lock and record resources.
Table variables cannot be assigned values. In addition, because the table variable scope is limited and is not part of the persistent database, it is not affected by transaction rollback.

////

 

If the data volume is not large (100,000), the query time is similar to that of the two. The difference is 1, 2 s, which is nothing for 10 s and 20 s.
Analyze the specific situation.
After using the above optimization methods, my friend has not changed anything, saving nearly half of the time.
@ Webber
According to the specific analysis, the variable table is like a local variable in C #, so the life cycle is short, but it does not mean that it must be better than the temporary table.
A temporary table needs to clear its resource usage by itself.