Does Stored Procedure increase performance.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 29 Jul 2008 17:03 GMT

For example I want to get salary from Customers table based on CustomerID.

I can create a Stored procedure called GetCustomerSalary with CustomerID as
parameter
OR
I can write parameterized query "Select Salary fromm Customers where
CustomerID=@CustomerID".

Will there any effect in performance.
My question is only about performance not about security and other issues.

Thanks for any help you can provide.
Mark

Reply to this Message

Tibor Karaszi - 29 Jul 2008 18:33 GMT

The possible performance gain for using a procedure for this particular example is probably barely
measurable. There are other cases, where there might be performance differences (Google for
"parameter sniffing" and read http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx for
more details).

(And as you already mentioned there can be other advantages using procedures, but I won't go into
them here, as per your request.)

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> For example I want to get salary from Customers table based on CustomerID.
>
[quoted text clipped - 7 lines]
> Thanks for any help you can provide.
> Mark

Reply to this Message

Rick Sawtell - 29 Jul 2008 18:38 GMT

> For example I want to get salary from Customers table based on CustomerID.
>
[quoted text clipped - 9 lines]
> Thanks for any help you can provide.
> Mark

There is a lot more at play than a simple, yes/no. Read through the
following

http://msdn.microsoft.com/en-us/library/ms181055.aspx

And once you get through that one, put on your thinking cap and tackle this
one.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Rick Sawtell

Reply to this Message