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

Stored Procedure vs Parameterized queries Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2008 11:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
I have a developer who keeps praising the Parameterized Queries instead of stored procedures. Can you anyone put some light on which one is the best way? I personally feel that stored procs are the best and most efficient way of retrieving or updating data.

Razi, M.
http://questivity.com/it-training.html
Post #446034
Posted Tuesday, January 22, 2008 12:55 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 15,558, Visits: 27,932
That's one ugly debate you're entering into. Short answer is, parameterized queries function the same way as stored procedures. SQL Server will be able to reuse execution plans because it will recognized the parameters as such. So from the performance point of view, there's no real difference.

The difference comes from code maintenance, maintainability, and performance tuning. In order to tune a single line of code requires a complete deployment of that code. If you're in a tiny little shop with a couple of developers and you're going straight to production with every successful build, who cares. If you're in a larger shop or dealing with more sensitive applications, you'll need to go through QA, acceptance testing, financial testing, etc., for every change to a line of code. Where as, with proper configuration and testing, you can adjust the internals of a stored procedure without changing it's interface and deploy it without affecting the client or app code. Huge difference. I'd go for those parts of the argument rather than strictly over performance.


----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #446076
Posted Tuesday, January 22, 2008 1:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
Thanks for the reply!!!

Razi, M.
http://questivity.com/it-training.html
Post #446080
Posted Tuesday, January 22, 2008 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
my addition....

Indeed, for plan reuse, the line is getting very thin.

regarding security, the gap has not been closed.

- "curse and blessings of dynamic sql" is still valid; I haven't got its url at this moment, but google for it.

- You will want to prefer sprocs because of its documentation value, security, tunability (by the dba if needed), ...


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #446089
Posted Tuesday, January 22, 2008 1:29 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 15,558, Visits: 27,932
True, I forgot about that. Thanks.

----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #446098
Posted Monday, September 6, 2010 10:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 2,892, Visits: 1,785
I can't stress the maintenance issue enough.
Let us suppose you hire a good DBA. By locking SQL in a code release you are hamstringing your DBA; you are not going to get the best out of them unless they write the code.

The principle of DATA IS SECURE is a real bug bear as well. It is surprising what should actually be considered secure and what is not. You may think that a name and address is on the electoral role therefore why bother securing it? Well if you know that name and address is for a manager in the FTSE Top 100 it goes from being a £0.05 name to a £5.00 name.

If the name is on the MI6 employee register then it absolutely should be secure. Dynamic SQL implies direct table access implies there is a login that can smash and grab data en-masse.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #981187
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse