October 27, 2011 at 1:47 am
Seems there's a lot of misinformation about SP's so I'm curious what a poll of people here will show. Please answer what you thought before reading the poll answers... not what you think the answer probably is now that you've read them 🙂
October 27, 2011 at 3:35 am
I think that the misinformation comes from good advice that turned into bad advice with new SQLServer versions.
In SQL Server 2000 we did not have "plan cache", there was just "procedure cache" and it was a totally different story.
EDITED: phat phingered today
-- Gianluca Sartori
October 27, 2011 at 4:01 am
Adhoc queries need to be exactly same for reuse. i.e. case-sensitivity, white spaces, etc...
Generally, When a statement is used inside a stored procedure, it's not going to change and it can reuse the plan. I think this also contribute's to this misconception.
Vishal Gajjar
http://SqlAndMe.com
October 27, 2011 at 4:11 am
Vishal.Gajjar (10/27/2011)
Adhoc queries need to be exactly same for reuse. i.e. case-sensitivity, white spaces, etc...Generally, When a statement is used inside a stored procedure, it's not going to change and it can reuse the plan. I think this also contribute's to this misconception.
True indeed, except for whitespaces.
Actually, you can use dynamic sql inside a stored procedure with concatenated values instead of parameters, which doesn't help plan reuse at all.
-- Gianluca Sartori
October 27, 2011 at 4:56 am
Gianluca Sartori (10/27/2011)
In SQL Server 2000 we did not have "plan cache", there was just "procedure cache" and it was a totally different story.
2000 did cache ad-hoc queries, it was either 7 or 6.5 that didn't. Either way, ancient history.
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
October 27, 2011 at 4:59 am
Gianluca Sartori (10/27/2011)
Vishal.Gajjar (10/27/2011)
Adhoc queries need to be exactly same for reuse. i.e. case-sensitivity, white spaces, etc...True indeed, except for whitespaces.
Actually he's completely right. The plan cache key for ad-hoc queries is a hash of the query text. It's case-sensitive, white-space sensitive, will change for different parameter values (unless query is simple or forced parameterisation)
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
October 27, 2011 at 6:10 am
GilaMonster (10/27/2011)
Gianluca Sartori (10/27/2011)
In SQL Server 2000 we did not have "plan cache", there was just "procedure cache" and it was a totally different story.2000 did cache ad-hoc queries, it was either 7 or 6.5 that didn't. Either way, ancient history.
Ah, thanks! I knew it was an old version, was not sure which one.
-- Gianluca Sartori
October 27, 2011 at 6:13 am
GilaMonster (10/27/2011)
Gianluca Sartori (10/27/2011)
Vishal.Gajjar (10/27/2011)
Adhoc queries need to be exactly same for reuse. i.e. case-sensitivity, white spaces, etc...True indeed, except for whitespaces.
Actually he's completely right. The plan cache key for ad-hoc queries is a hash of the query text. It's case-sensitive, white-space sensitive, will change for different parameter values (unless query is simple or forced parameterisation)
I remember I read somewhere that it didn't consider white-spaces, but I must be wrong, obviously.
-- Gianluca Sartori
October 27, 2011 at 12:00 pm
Needs one additional answer: "It Depends"
Dynamic SQL in the middle of a proc will have little difference to the affect if intelligently parameterized for cache reusage.
Dynamic SQL from OUTSIDE that requires complex encoding will either be ugly to maintain, or will have the external application hitting the database too long.
That said, I avoid it, because of the negligible gain and the amount of problems external Dynamic SQL calls create.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2011 at 12:04 pm
Evil Kraig F (10/27/2011)
Needs one additional answer: "It Depends"Dynamic SQL in the middle of a proc will have little difference to the affect if intelligently parameterized for cache reusage.
Dynamic SQL from OUTSIDE that requires complex encoding will either be ugly to maintain, or will have the external application hitting the database too long.
That said, I avoid it, because of the negligible gain and the amount of problems external Dynamic SQL calls create.
These pretty much mirror my thoughts. It isn't so much about performance at this point, but more about maintenance and, in my opinion, security.
I actually didn't answer the poll because none of those answers fit what I think. Properly parameterized queries from the application will have the same performance characteristics as an SP and a poorly written SP with dynamic SQL will perform as poorly and be as dangerous as text queries from the application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 27, 2011 at 12:19 pm
The question is simply "are they faster", not "are they better", so I went with just that in mind when I answered.
I'm looking at it from "all else being equal", in terms of parameterization, et al.
I've probably just had too many devs tell me they want me to write a proc for them because it'll be precompiled and thus faster than inline SQL, and had to correct them on that point. Too used to answering this question too many times.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2011 at 12:57 pm
IMO the question should be : "where/who do you want to take care of security and performance".
If you think that is the sole responsibility of your devs, go for parameterised queries and don't bother any dba when you're in troubles.
If you would like your dba to be able to help out without having to recompile your apps or modify and distribute your query xml files, and want to have security nicely in place, go for stored procedures.
"But we build our queries dynamically based on user input", no problem, if you cannot enforce structure, you take care of it all. Just don't bother a dba. You have no control over the queries, wright ?!
Make your applications manageable and take ownership and control !
You'll gain a dba of experience at your disposal.
If it's a good one, you'll notice (s)he doesn't prefer picking on you, but will be constructive and collaborative, helping you out with more than just sprocs, resulting in better data applications for your company.
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
November 6, 2011 at 12:40 am
I posted this poll because the new lead developer on my team is wanting to move everything towards sprocs. He thinks they are faster because they are precompiled and that it is important to seperate your data layer.
This first argument is simply untrue. And while it's good to seperate your data layer, it's fallacious to argue in favor of sp's for that reason because you can just as easily have accomplish this without sp's.
I believe in using sp's in a targeted manner, because there are certainly situations where they are great. But for most stuff I find that using sp's increases overhead and maintenance, and the benefits are mostly only perceived or negligable.
It's sad to me that most people do not even consider development time and maintenance time when considering what the best method to do something is. Because *yes*, there are many situations where those things ARE more important than security and speed.
November 6, 2011 at 12:53 am
brandonmooreis (11/6/2011)
I posted this poll because the new lead developer on my team is wanting to move everything towards sprocs.
Good to hear.
I believe in using sp's in a targeted manner, because there are certainly situations where they are great. But for most stuff I find that using sp's increases overhead and maintenance, and the benefits are mostly only perceived or negligable.
My recommendation to clients is almost always stored procedures. Not because they're faster, but for several other reasons.
They're easier to maintain than embedded code. Changing a stored procedure while still returning the same results (eg performance tuning) doesn't require that the app be recompiled and if there's proper unit tests built around the stored procedure then the app doesn't even need significant testing.
It's another layer of security (since access to the base tables isn't needed) and protection against SQL Injection. Yes, you can still get SQL injection with procs, it's harder.
Plan compilation and reuse. I've lost count of how many systems I've seen with plan cache bloat due to unparameterised ad-hoc SQL
Reduced network communications. One call to a procedure rather than multiple passing long pieces of T-SQL around.
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
November 6, 2011 at 1:41 am
Gianluca Sartori (10/27/2011)
GilaMonster (10/27/2011)
Gianluca Sartori (10/27/2011)
Vishal.Gajjar (10/27/2011)
Adhoc queries need to be exactly same for reuse. i.e. case-sensitivity, white spaces, etc...True indeed, except for whitespaces.
Actually he's completely right. The plan cache key for ad-hoc queries is a hash of the query text. It's case-sensitive, white-space sensitive, will change for different parameter values (unless query is simple or forced parameterisation)
I remember I read somewhere that it didn't consider white-spaces, but I must be wrong, obviously.
You're thinking of plan guide matching. See the section "Plan Guide Matching Requirements" in sp_create_plan_guide.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply