Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which is the faster ? Expand / Collapse
Author
Message
Posted Monday, January 20, 2014 12:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 12:07 AM
Points: 1, Visits: 5
Hi all,

I want to know which is the faster group of select statement or stored procedure with same select statements ?

Thanks.
Post #1532509
Posted Monday, January 20, 2014 12:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 40,193, Visits: 36,598
Should be the same all other considerations being equal.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1532511
Posted Monday, January 20, 2014 5:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:55 AM
Points: 13,884, Visits: 28,279
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1532580
Posted Monday, January 20, 2014 4:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532809
Posted Tuesday, January 21, 2014 8:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:57 PM
Points: 880, Visits: 2,433
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.
Post #1533168
Posted Tuesday, January 21, 2014 11:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 3,014, Visits: 3,100
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,
SQL Server developer at Seavus
www.seavus.com
Post #1533296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse