Which is the faster ?

Question

Which is the faster ?

devu

Newbie

Points: 1
More actions
January 20, 2014 at 12:15 am

#303122

Hi all,
I want to know which is the faster group of select statement or stored procedure with same select statements ?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Should be the same all other considerations being equal.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

To a very large degree, a query is a query. As Gail says, it will be the same. The main thing would be how the query is put together. Let's assume a stored procedure and parameters. Then, do the same query, but with local variables instead of parameters. You may see huge differences there. But it's because the fundamentals of the query changed from using parameters (which can be sniffed) to using variables (which are only sniffed during recompiles).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

devu (1/20/2014)
Hi all,
I want to know which is the faster group of select statement or stored procedure with same select statements ?
Thanks.

My question would be, why don't you just try it and find out for yourself?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Jeff Moden (1/20/2014)
devu (1/20/2014)
Hi all,
I want to know which is the faster group of select statement or stored procedure with same select statements ?
Thanks.
My question would be, why don't you just try it and find out for yourself?

+1

Start with Profiler, watching the CPU, Read, Write, and Dur columns, for the SQL:BatchCompleted and RPC:Completed events, filtered on your SPID (session_id) if you're using SSMS, and on your host name and application name and perhaps even some of the text of the query if you're using an app/website/etc.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

Nadrek (1/21/2014)
Jeff Moden (1/20/2014)
devu (1/20/2014)
Hi all,
I want to know which is the faster group of select statement or stored procedure with same select statements ?
Thanks.
My question would be, why don't you just try it and find out for yourself?
+1
Start with Profiler, watching the CPU, Read, Write, and Dur columns, for the SQL:BatchCompleted and RPC:Completed events, filtered on your SPID (session_id) if you're using SSMS, and on your host name and application name and perhaps even some of the text of the query if you're using an app/website/etc.

It can be one time better with the select statement, one time better with the stored procedure or the next time equal. As Gail says - same if all considerations being equal.

It matters whether the query causes locking in the system, and if that locking disturbs other operations? In that case with sql procedure you have more options. However consider sniffing parameters with stored procedures.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com