Optimization of SQL Server Stored Procedures: A simple test of the performance difference between temporary tables and Union all during stored procedures

From: http://www.cnblogs.com/chillsrc/archive/2008/04/15/1154186.html

I. Environment and test requirements

1. Test Environment

① Hardware: CPU: 2 GB, memory: 2 GB

② Database: SQL 2005

The result varies depending on the machine configuration.

2. Performance Test Description

① Query after the database server is restarted, that is, the result of the first query.
② Compare the performance of database queries.

Ii. Test Status

1. Use a temporary table in the stored procedure: (proc1)

Select Top 1000 * Into # T1 From somast Insert into # T1 Select Top 100 * From somast Select * from # T1 --- Delete table data Truncate table # T1 -- Delete a temporary table Drop table # T1

Execution time:1039 Ms

2. Use Union all: (proc2) in the Stored Procedure)

Select Top 1000 * From somast Union all Select Top 100 * From somast

Execution time:5017 Ms

Iii. Additional instructions

After the preceding two stored procedures are executed once, the preceding two stored procedures are executed again to find that the execution time has changed substantially:

The stored procedure proc1. The execution time is:728 Ms
The storage process is proc2 and the execution time is:998 Ms

However, in practice, it is unlikely that the same person will query the same stored procedure twice or more in a short period of time. In most cases, executed by different people.
In addition, the optimization of the stored procedure will shorten the execution time after optimization. If optimization can be performed, optimization should be performed.