Why are stored procedures sometimes so slow compared with plain SQL statements?

I have been doing lately lots of database testing by checking if data displayed in business intelligence reports is correct.

The reports data goes from the original database to an intermediate database (through some processing), then a cube is generated and finally, the reports.

The testing consists in writing SQL queries against the original database and comparing the query results with the BI reports data.

So far so good.

Some of the queries were very similar so I tried to re-use the code by creating stored procedures that have parameters.

For example, a simple SELECT statement like this

SELECT table1.field1, table2.field2
FROM table1
JOIN table2 ON table1.key = table2.key
WHERE table1.filter1 = 'value1' and table2.filter2 = 'value2'

was converted in the following stored procedure:

CREATE PROCEDURE doSomething

@Filter1Value as varchar(10)
, @Filter2Value as varchar(10)

AS
BEGIN

SET NOCOUNT ON;

        SELECT table1.field1, table2.field2
        FROM table1
        JOIN table2 ON table1.key = table2.key
        WHERE table1.filter1 = @Filter1Value and table2.filter2 = @Filter2Value

END

The stored procedure can then be executed as follows instead of running directly the SQL statement:

EXEC doSomething 'value1', 'value2'


So easy, right?

The problem is that the performance of the stored procedure was terrible compared with the performance of the SQL statement.

In some cases, the results of the SQL statement would be generated in 10 seconds and I would still wait for the results of the stored procedure after 15 minutes!

The SQL code could not be the cause of the performance problem since it is identical.

Why is the performance of the stored procedure so bad then?

The answer is that SQL Server does something called parameter sniffing for the parameters of stored procedures for performance optimization.

How can this be fixed?

See below the same stored procedure with a minor change:

CREATE PROCEDURE doSomething

@Filter1Value as varchar(10)
, @Filter2Value as varchar(10)

AS
BEGIN

       DECLARE @LocalFilter1Value as varchar(10)
       DECLARE @LocalFilter2Value as varchar(10)

       SET @LocalFilter1Value = @Filter1Value
       SET @LocalFilter2Value = @Filter2Value

SET NOCOUNT ON;

        SELECT table1.field1, table2.field2
        FROM table1
        JOIN table2 ON table1.key = table2.key
        WHERE table1.filter1 = @LocalFilter1Value and table2.filter2 = @LocalFilter2Value

END

The change consists in creating local variables in the stored procedure, initialize them with the values of the parameters and then use the local variables in the SQL statement.


As soon as this change is done, the performance of the stored procedure is the same with the performance of the plain SQL statement.

Share this post