Stored procedures are BAD...a philosophical question
-
I'm not sure if this question belongs here as it's more philosophical than 'how to' but here goes...
I'm a vb and SQL developer. Corporate IT is pushing to centralize all databases at this company, so they want to move my departmental SQL database to a corporate server. There have been several meetings to prepare for this. During one of the meetings I was asked if my database uses many stored procedures. I said 'yes'. My questioners looked at each other and then one said to me 'Don't you know that stored procedures hurt performance? You'll have to get rid of those before your database will ever be accepted on a corporate server. You should be using views'
Well, I was left speechless. And now I'm confused. Is this a typical issue in large corporate environments? I've always viewed stored procedures as one of the GOOD things about SQL Server. Sure you have to test them to make sure they run efficiently, but c'mon! A blanket statement that stored procedures are BAD???
So what do you think. Is this an anomaly? Is this how it is where you work? Or am I dealing with some IT folks who don't know what they're talking about? (I haven't even addressed the fact that MOST of what my db does wouldn't work at all without stored procedures...)
-
Well I believe they are mistaken, however each one has his own experience and based on their experience they arrive at conclusions, so I cannot definitely comment on their view though. If you Look at BOL, in brief this is what it says
This is just copied from BOL
Effects of Stored Procedures on Application Performance
All well-designed Microsoft® SQL Server™ 2000 applications should use stored procedures. This is true whether or not the business logic of the application is written into stored procedures. Even standard Transact-SQL statements with no business logic component gain a performance advantage when packaged as stored procedures with parameters. Transact-SQL statements compiled into stored procedures can save a significant amount of processing at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use the network more efficiently than equivalent Transact-SQL statements sent to the server. For example, suppose an application needs to insert a large binary value into an image data column. To send the data in an INSERT statement, the application must convert the binary value to a character string (doubling its size), and then send it to the server. The server then converts the value back into a binary format for storage in the image column. In contrast, the application can create a stored procedure of the form:
CREATE PROCEDURE P(@p1 image) AS INSERT T VALUES (@p1)When the client application requests an execution of procedure P, the image parameter value will stay in binary format all the way to the server, thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when they include business services logic because it moves the processing to the data, rather than moving the data to the processing.
Prasad Bhogadi
www.inforaise.com -
Thanks Prasad! It's what I believe. I'd sure like to hear from others on the topic...Thanks in advance.
-
Well I had a nice long helpfully post but it got eaten by the post button without showing up. I will try to rewrite it later but needless to say there are too many performance enhancements to using an SP for them to be able to say this such as security, stored plans, transactional wrap for business logic, OUTPUT paramters with app side Execute No Records, fewer round trips from App to server, et al.
I suggest finding out where they got there info or that maybe they are trying to limit the amount of admin work they will be responsible for. After all if I give you access to all tables openly you can wrtie your own code and I don't have to do anything more than make sure the server runs. Personally that is what I am betting on or they are talkign out their ends.
-
hmm.. I also typed up a long response to this but it is not showing up after I click the post button (kinda frustrating).
anyways, stored procedures are good because they improve upon the use of execution plans because they parameterize its queries. first time you hit stored procedure, there is slight additional overhead but performance gain is realized the next time you run it (provided that it has not been cycled out of the cache yet due to old age). the performance gains may not be realized if stored procedure has to recompile plan every time you run it. there are certain things that would make a stored procedure recompile every time (eg. creating/dropping temp table) but there are things that you can do it prevent stored procedure recompilations (eg. use table variable instead on temp table).
-
I'm regretting not having used stored procedures in my last major project and it has nothing to do with performance. Performance is fine (and I presume in the same ballpark as SPs) by just using parameterized and prepared statements.
The reason is just maintanability during optimization. It's very convenient having the code that determines 95% of the execution time (database access/T-SQL) available in one place and modifiable on the fly (no recompile).
That being said, this discussion was about views I believe, so I'm posting this in the wrong place
. Views and stored procedures have partially, but not entirely, overlapping purposes, so I don't see how anyone could completely discount one for the other.T.
-
Thanks all for your responses. I hope to uncover some more details about this anti-sp attitude later on this week.
In response to Antares, Yes I think there's either an agenda or a plain lack of knowledge going on here...I'm just not sure which. I'm sorry I didn't get to read yours or bp's more extensive posts! I end up copying my post before submitting it here because of the same problem
To BP, I currently use temp tables extensively, but part of this migration will include moving to SQL 2000. I will finally be able to take advantage of table variables. For those who have compared, do you find table variables to be substantially faster in SPs than temp tables?
And to Tosh Tosh, I agree about maintainability. The stored procedures in this db are designed to be run from both VB and web clients. Keeping the business logic in this db centralizes it nicely.
And to all, it's nice to get the confirmation that I'm not missing something here!
I'd be happy to hear from as many more of you as are willing to respond. Thanks in advance!
-
I do find that table variables perform excellent but my datasets tend to be large so I still have to use temp tables so they get built in TempDB and don't hog the memory, that is the only downside to table variables.
-
One of the advantages that sp give and is not very often appreciated is that they can ISOLATE the application from the backend structure AND business rules so the changes on any of those can be totally transparent to the App Side
I do believe that something is terribly wrong with that argument against sp
* Noel -
Greg, It might be the "SP Hater" likes Views for limiting access to and from the data, and yes, I think most of us agree that Views are great "tools" for this and other things. While SPs can perform most anything a View can do, SPs can do many, many things Views can not. I really agree with Tosh about having uncompiled access to logic. Having code in SPs, particularly code dealing with the data, has nothing but advantages IMHO.
As far as @TableVars vs. #TempTables, I prefer #Temp tables. I've not seen any performance issue with #TempTables, and I HAVE seem some performance issues with @TableVars. Also, you can index, ALTER TABLE, the data "persists" while coding, etc. on #TempTables.. IMHO much more flexible than @TableVars.
Keeping short to avoid "post timeout"
Once you understand the BITs, all the pieces come together -
RE: Temp table vs @table var...As always I guess I'll have to test for myself whether #Temp beats @table or reverse in My scenario. My typical temp table has few rows, and is used as an inner join with a HUGE table or two to limit results.
Thomas, I agree. Other than the risk of SQL Injection to dynamic sql, It seems that stored procedures are AT LEAST as secure as Views, typically moreso.
Ok at least among responders here there seems to be consensus...Stored Procedure=Good.
Anyone wanna try to be devil's advocate? Come up with the best valid reason you can against SPs. Some of you have already done so, it seems like 'dba laziness' and 'misguided concept of security' are the best theories so far.
I sure appreciate this validation since I'll have to defend my use of SPs at an upcoming meeting this week.
-
One thing about SPs, as opposed to client / mid tier, is the code is more exposed. This has never been an issue for me, but it may be to some. This still does not make Views any more benificial.
Once you understand the BITs, all the pieces come together -
Perhaps this bias toward views stems from dealing with the updateable view paradigms. Thus, almost equivalent to sp. The only difference is would probably be the way parameters are avoided in the views case. Might be interesting to ask for some benchmarks about using views vs sp in this case. Perhaps their is something to the view over sp concept. I've seen instances where excessive business logic in the sp can bog down the server, but usually that's related to the sp code and not the task.
Peter Evans (__PETER Peter_)
-
Yes, I suppose that they may favor updatable views. Since my db is much more of a reporting db, updating is practically moot though. almost all data insert is done by the admin via a couple of stored procedures that run overnight (actually in a few minutes) once a month. The vast majority of the sps in this db are used for flexible data retrieval. There is a batch of SPs that inserts a few rows at at time from tiny text files into the db. It is user driven, but the whole batch of SPs which BULK INSERT 18 files to temp tables, validate data, denormalize a few places and insert into the database typically runs in under 3 seconds.
All of that doesn't matter, since they've made the Stored procedure=bad statement without any knowledge of my DB except how many Stored procedures there are. It's not based on any understanding of the db at all.
Apparently (according to them) DTS=Good and SP = bad for inserting data too...
-
Well now there you go changing the topic away from philosphy
That sounds more like politics... I.E., they're giving you the corporate norms lingo, just rewrite it so we think its a fixed cost manageable and trackable budget item that requires no labor.
First it was scheduled tasks that would save cut maintenance costs, then dts and bulk insert, Next the advance Metadata/biztalk/new dts stuff. At some point all the what we'll do people of the world still need the how can it be done people of the world. Ah the ever ebbing and flowing tide of the engineers and the managers.
Peace,
Peter Evans (__PETER Peter_)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply