Why Execute Stored Procedures Is Faster Than SQL Query from a Script

Why execute stored procedures is faster than SQL query from a script?

SQL Server basically goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

1) syntactically check the query

2) if it's okay - it checks the plan cache to see if it already has an execution plan for that query

3) if there is an execution plan - that plan is (re-)used and the query executed

4) if there is no plan yet, an execution plan is determined

5) that plan is stored into the plan cache for later reuse

6) the query is executed

The point is: ad-hoc SQL and stored procedures are treatly no differently.

If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.

Update: using stored procedures over non-parametrized queries is better for two main reasons:

• since each non-parametrized query is a new, different query to SQL Server, it has to go through all the steps of determining the execution plan, for each query (thus wasting time - and also wasting plan cache space, since storing the execution plan into plan cache doesn't really help in the end, since that particular query will probably not be executed again)

• non-parametrized queries are at risk of SQL injection attack and should be avoided at all costs

Why Stored Procedure is faster than Query

Stored Procedures Are Faster Than SQL Code

This is a myth, the performance is always equivalent, from the book: Architecting Microsoft® .NET Solutions for the Enterprise:

SQL is a language through which you declare your intentions about the operations (query, update, or management operations) to execute on the database. All that the database engine gets is text. Much like a C# source file processed by a compiler, the SQL source code must be compiled in some way to produce a sequence of lower-level database operations—this output goes under the name of execution plan. Conceptually, the generation of the execution plan can be seen as the database counterpart of compiling a program.

The alleged gain in performance that stored procedures guarantee over plain SQL code lies in the reuse of the execution plan. In other words, the first time you execute an SP, the DBMS generates the execution plan and then executes the code. The next time it will just reuse the previously generated plan, thus executing the command faster. All SQL commands need an execution plan.

The (false) myth is that a DBMS reuses the execution plan only for stored procedures. As far as SQL Server and Oracle DBMS are concerned, the benefit of reusing execution plans applies to any SQL statements. Quoting from the SQL Server 2005 online documentation:

When any SQL statement is executed in SQL Server 2005, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2005 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server 2005 generates a new execution plan for the query.

The debate around SPs performing better than plain SQL code is pointless. Performance wise, any SQL code that hits the database is treated the same way. Performance is equivalent once compiled. Period.

SQL query runs faster than stored procedure

SPs are precompiled, it may be that a "bad" query plan is used which is not good for the arguments passed into the SP. You can add a OPTION (RECOMPILE) on the queries inside the SP to force SQL Server to re-run the query through the query optimizer and find the best query for the given parameters.

See here or here for some examples and background info...

SQL Server: Query fast, but slow from procedure

I found the problem, here's the script of the slow and fast versions of the stored procedure:

dbo.ViewOpener__RenamedForCruachan__Slow.PRC

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow
    @SessionGUID uniqueidentifier
AS

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

dbo.ViewOpener__RenamedForCruachan__Fast.PRC

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast
    @SessionGUID uniqueidentifier 
AS

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

If you didn't spot the difference, I don't blame you. The difference is not in the stored procedure at all. The difference that turns a fast 0.5 cost query into one that does an eager spool of 6 million rows:

Slow: SET ANSI_NULLS OFF

Fast: SET ANSI_NULLS ON

---

This answer also could be made to make sense, since the view does have a join clause that says:

(table.column IS NOT NULL)

So there is some NULLs involved.

---

The explanation is further proved by returning to Query Analizer, and running

SET ANSI_NULLS OFF

.

DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'

.

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

And the query is slow.

---

So the problem isn't because the query is being run from a stored procedure. The problem is that Enterprise Manager's connection default option is ANSI_NULLS off, rather than ANSI_NULLS on, which is QA's default.

Microsoft acknowledges this fact in KB296769 (BUG: Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects). The workaround is include the ANSI_NULLS option in the stored procedure dialog:

Set ANSI_NULLS ON
Go
Create Proc spXXXX as
....

SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes

This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance - parameter sniffing

There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time.

The recommended fix for most situations (though it depends on the structure of your query and sproc) is to NOT use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries.

Performance of SQL vs. stored procedures in Oracle

It doesn't make much sense to put something like select count(*) from table into a stored procedure. The overhead of calling the stored procedure is certainly higher than executing the statement directly.

If you have some code that selects and updates many rows, putting that into a stored procedure will probably faster than executing the same code from some client, because you save the network traffic/delay involved.

However, performance is not the only reason to do that. Some organizations will not grant any access to any table to the client, but provide a set of stored procedures to access data. That way, you can do additional security checks in your stored procedures. If your database client is not a web server, but some frontend running on a client PC, someone could debug the client to find out the password, then connect to the database using a tool like sqlplus. If the only way to access the actual data is through stored procedures, and these stored procedures won't allow more than 10 rows returned, it's much harder for a client to select * from anytable to get a dump of all the data. Additionally, of your stored procedures are the only way to write to the database, they can do a lot of sanity checking before modifying anything.

Query runs fast, but runs slow in stored procedure

OK, we have had similar issues like this before.

The way we fixed this, was by making local parameters inside the SP, such that

DECLARE @LOCAL_Contract_ID int, 
        @LOCAL_dt_From smalldatetime, 
        @LOCAL_dt_To smalldatetime, 
        @LOCAL_Last_Run_Date datetime

SELECT  @LOCAL_Contract_ID = @Contract_ID, 
        @LOCAL_dt_From = @dt_From, 
        @LOCAL_dt_To = @dt_To, 
        @LOCAL_Last_Run_Date = @Last_Run_Date

We then use the local parameters inside the SP rather than the parameters that was passed in.

This typically fixed the issue for Us.

We believe this to be due to parameter sniffing, but do not have any proof, sorry... X-)

EDIT:

Have a look at Different Approaches to Correct SQL Server Parameter Sniffing for some insightful examples, explanations and fixes.

---

Related Topics