January 22, 2008 at 11:43 am
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
January 22, 2008 at 12:55 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 22, 2008 at 1:06 pm
Thanks for the reply!!!
Razi, M.
http://questivity.com/it-training.html
January 22, 2008 at 1:14 pm
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
January 22, 2008 at 1:29 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2010 at 10:32 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply