Why stored procedures in SQL Server are slower than running SQL statements directly

The problem is the parameter of the stored procedure sniffing

In many of the data, it is described that SQL Server stored procedures have the following advantages over ordinary statements:

1. stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, and the SQL statements we typically use are compiled once per execution, so using stored procedures can improve database execution speed.

2. often encounter complex business logic and operation of the database, this time will use the SP to encapsulate the database operations. when complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database. It can greatly improve the efficiency of the database and reduce the execution time of the program, which is very important in the operation of the database with large data volume. In code, the separation of SQL statements and program code statements can improve the readability of program code.

3. stored procedures can be set parameters , can be reused according to the parameters of the same stored procedure, so as to effectively improve the optimization rate and readability of the code.

4. High Security , can be set only some of this user has the right to use the stored procedure for the specified stored procedure type:

A. System stored procedures: Start with sp_, used to make the system settings. Obtain information. Related management, such as sp_help, is the information that gets the specified object.

B. Extended stored procedures start with XP_ to invoke the functionality provided by the operating system
EXEC master. xp_cmdshell ' Ping 10.8.16.1 '

C. User-defined stored procedures, which we refer to as common formats for stored procedures

Template: Create procedure Procedue_name [@parameter Data_type][output]
[With] {recompile|encryption} as Sql_statement

Explanation: Output: Indicates that this parameter can be passed back

with {recompile|encryption} recompile: Indicates that each execution of this stored procedure is recompiled once; encryption: the contents of the stored procedure being created are encrypted.

But recently, someone in our project group wrote a stored procedure that was calculated at 1个小时47分钟 and sometimes running for more than two hours, and the colleague described that if the statements in the stored procedure were taken out directly, it would run 10 minutes or so, and I didn't take it seriously. But today I wrote the stored procedure also encountered this problem, in the search for information after the reason finally found the reason, the original is parameter sniffing problem.

Here's how I can optimize a stored procedure that runs for more than one hours to complete within a minute:

Original stored Procedure

CREATE PROCEDURE [dbo]. [Pro_imanalysis_daily]

@THEDATE VARCHAR (30)

As

BEGIN

IF @THEDATE is NULL

BEGIN

SET @THEDATE =convert (VARCHAR (+), GETDATE ()-1,112);

END

DELETE from rpt_im_userinfo_daily WHERE [email protected];

INSERT rpt_im_userinfo_daily (Thedate,alluser,newuser)

SELECT AA. Thedate,alluser,newuser

From

((SELECT thedate,count (DISTINCT USERID) AllUser

From FACT

WHERE [email protected]

GROUP by Thedate

) AA

Left JOIN

(SELECT thedate,count (DISTINCT USERID) NewUser

From FACT T1

WHERE not EXISTS (

SELECT 1

From FACT T2

WHERE T2. thedate< @THEDATE

and T1. Userid=t2. USERID)

and [email protected]

GROUP by Thedate

) BB

On AA. Thedate=bb. Thedate);

GO

Daily execution: exec pro_imanalysis_daily @thedate =null
Time: 1 hours 47 minutes ~ 2 hours 13 minutes

After looking for information, for the following reasons (by source is an English, some places written I am not particularly clear, the original see http://groups.google.com/group/microsoft.public.sqlserver.server/msg/ ad37d8aec76e2b8f?hl=en&lr=& amp;ie=utf-8&oe=utf-8):

There is a feature called "Parameter sniffing" in SQL Server. SQL Server will develop an execution plan before the stored procedure executes. In the example above, SQL does not know what the value of @thedate is at compile time, so it will have to make a lot of guesses when executing the execution plan. Assuming that most of the arguments passed to @thedate are non-empty strings, and that 40% of the thedate fields in the fact table are NULL, SQL Server chooses a full table scan instead of an index scan to make an execution plan for the parameter @thedate. A full table scan is the best execution plan when the parameter is empty or 0. However, full-table scanning severely affects performance.

Assuming that you first used exec pro_imanalysis_daily @thedate = ' 20080312 ' then SQL Server would use the value 20080312 as the reference value for the execution plan of the next parameter @thedate, Without a full table scan, but if you use @thedate=null, the next execution plan will be based on a full table scan.

Workaround:

There are two ways to avoid the "Parameter sniffing" problem:

(1) By using declare declared variables instead of arguments: Using set @[email protected], the SQL statements that appear @thedate are all replaced with @variable.

(2) Hide the affected SQL statements, such as:

A) Put the affected SQL statements into one of the sub-stored procedures, for example, we call a word stored procedure after the @thedate setting becomes today to pass the @thedate as a parameter.

b) Use sp_executesql to execute the affected SQL. The execution plan is not executed unless the sp_executesql statement is executed.

c) Use dynamic SQL ("EXEC (@sql)") to execute the affected SQL.

Use the (1) method to transform the stored procedure in the example as follows:

ALTER PROCEDURE [dbo]. [Pro_imanalysis_daily]

@var_thedate VARCHAR (30)

As

BEGIN

DECLARE @THEDATE VARCHAR (30)

IF @var_thedate is NULL

BEGIN

SET @var_thedate =convert (VARCHAR (+), GETDATE ()-1,112);

END

SET @[email protected]_thedate;

DELETE from rpt_im_userinfo_daily WHERE [email protected];

INSERT rpt_im_userinfo_daily (Thedate,alluser,newuser)

SELECT AA. Thedate,alluser,newuser

From

((SELECT thedate,count (DISTINCT USERID) AllUser

From FACT

WHERE [email protected]

GROUP by Thedate

) AA

Left JOIN

(SELECT thedate,count (DISTINCT USERID) NewUser

From FACT T1

WHERE not EXISTS (

SELECT 1

From FACT T2

WHERE T2. thedate< @THEDATE

and T1. Userid=t2. USERID)

and [email protected]

GROUP by Thedate

) BB

On AA. Thedate=bb. Thedate);

GO

Test execution speed of 10 minutes, I again check this SQL, I found that SQL has a problem, this SQL uses not exists, in a large table with not exists is not very wise, so, I have improved this SQL, changed to the following:

ALTER PROCEDURE [dbo]. [Pro_imanalysis_daily]

@var_thedate VARCHAR (30)

As

BEGIN

DECLARE @THEDATE VARCHAR (30)

IF @var_thedate is NULL

BEGIN

SET @var_thedate =convert (VARCHAR (+), GETDATE ()-1,112);

END

SET @[email protected]_thedate;

DELETE from rpt_im_userinfo_daily WHERE [email protected];

INSERT rpt_im_userinfo_daily (Thedate,alluser,newuser)

Select @thedate as Thedate,

COUNT (distinct case is today>0 then userid else null end) as AllUser,

COUNT (distinct case if dates=0 then userid else null end) as NewUser

From

(

Select UserID,

Count (case if thedate>[email protected] then null else thedate end) as dates,

Count (case when [e-mail protected] then thedate else null end) as today

From FACT

GROUP BY UserID

) as Fact

GO

The test result is below 30ms.

Reference: http://www.oecp.cn/hi/shua0376/blog/2062

Profile: http://www.cnblogs.com/lyhabc/articles/3222179.html