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 12345»»»

Stored Procedures Reconsidered Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2008 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:31 AM
Points: 9, Visits: 45
Comments posted to this topic are about the item Stored Procedures Reconsidered
Post #542915
Posted Tuesday, July 29, 2008 10:52 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:49 PM
Points: 389, Visits: 1,042
Interesting post. One that will no doubt generate some responses here.

I disagree with the scope of the post. Or rather, I sense a compression of incompatible scopes in this editorial. Allow me to explain:

It's somewhat common for SQL Server database professionals to narrowly view the server platform. We (I include myself) see our little niche and believe the rules for our niche apply universally. I don't see this as particularly productive. In fact, I've seen it fly in the face of productivity on a few occassions.

Just look at the differences in design between a data warehouse and an OLTP database for an example.

SQL Server is an extremely complex product - especially 2005. I keep running into folks who find neat uses for different functionality in SQL Server - stuff I would've never thought of. All components, including stored procedures, have their place.

:{> Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #543184
Posted Tuesday, July 29, 2008 11:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 24, 2010 9:35 PM
Points: 78, Visits: 30
I disagree with many aspects of this post.

First, on performance. The scenario you describe where performance is hindered by a stored procedure only occurs when different parameters result in wildly different distributions of data. As an example, say we have a stored proc which accepts a date range within which records should be fetched.

A small date range will yield a small number of records, whereas a large date range could result in a very large number of records. The query plan for small sets of records and large sets of records is very different in terms of how joins are constructed... so if the small plan is used when a large set of data is requested, it will be inefficient.

In the majority of cases, this does not occur. For the most part, the precompiled aspect of the stored proc will result in much faster execution time than in compiling every time. For those stored procedures which are affected by this data distribution problem, you simply set WITH RECOMPILE and the stored proc recompiles each time as a normal statement would, and the problem goes away. Overall, performance is improved the majority of times that you don't need to use this feature.

Your next arguments about performance and cursors are irrelevant. That's an issue with coding practices not the technology. Cursors can be written anywhere.

You have more of a point about security, I still feel more comfortable having total control over entry and exit points of the application though.

Regarding reusability... this is an important reason to use stored procedures. Most of the projects we do have multiple sources connecting to the database, such as website, a windows service, an externally facing web service, and so on. For operations which rely on database processing, the only sensible thing to do is to make this code usable for all entry points.

A good example of this would be using SQL encryption and opening an encryption key, reading and decrypting the data, and closing the encryption key. There is no reason why this shouldn't be done at the database level.

Separation of duties however is the big one. This is where I very strongly disagree with you. Simply because they are not "extensible" doesn't mean that separation of duties isn't important.

In fact, if you think of stored procedures as interfaces, the "it's not OO" argument fails immediately. The database is an object, and having direct table access to the database is the same as one object (the application) being able to dig around inside another objects internal structure and workings, a clear violation of the encapsulation principle of object-oriented programming. Using stored procedure "interfaces" to encapsulate the private data held in the database is the very essence of OO programming. It is clearly and perfectly object oriented.

Stored procedures allow you to have a well-defined application / database layer. As an application is used over time, databases accumulate more and more data. It becomes necessary to start tweaking the database by denormalising, creating indexed views and so forth.

With a stored procedure driven database, you can make arbitrary changes to the underlying database structure transparently. Multi-level joins can become a single indexed view. A calculated field can become a denormalised field. A single huge table can become a partitioned table. A whole series of queries computed on the fly may now be served directly from a newly created reporting database, all without your application having to know a thing about any of it.

If you have baked all of this logic into your application code, good luck is all I can say. Even with a well architected application, the developing / testing / deploying cycle of these changes is going to be very, very expensive, with the increased risk of bugs that modifying a stable application inherently brings.

Having the stored procedure layer to work with gives you an additional layer of abstraction and flexibility. It allows the application to communicate with an abstract datasource of arbitrary structure, so long as these interfaces exist.

Overall I don't accept that this particular change is inevitable nor that I will "have to prepare myself for trends". The only trends I see here are cowboy application developers thinking they should have their sticky fingers deep in the database pie, and that's not a trend I ever intend on following. Note, I am primarily a developer though I am a qualified DBA as well.
Post #543189
Posted Wednesday, July 30, 2008 12:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:46 AM
Points: 44, Visits: 155
It’s a good point, I remember from working in Oracle DB , we’d almost always rather use parametised queries in a DAL rather than stored procedures, the only exception was our accounting module, where the code was considered too complex for the average developer, and most developers there had rights to change the DAL, but not to change stored procedures. Weird setup I know.

If you look at something like LINQ, it’s a lightweight ORM in a way, you can see MS is looking outside of stored procedures.
Personally? I like them. It gives you a nice insight into what’s happening at a database level, i.e. database operations happen at a database level. From a performance point of view though, there are cases where constructing a statement will yield a much better performing solution than trying to write either a generic sp, or multiple sps for each use case.

From an installation and maintenance point of view, source control for compiled code is easier than maintaining SPs, there’s a plus point. That’s just management practise, but still. Releasing a new app to a client with an installer is easier than building an installer that archives the old SPs (for uninstallation) and installs the new one. I’ve done it, and it’s possible, even easy, but it’s not completely trivial / automated, and is time consuming.
Post #543203
Posted Wednesday, July 30, 2008 1:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 6, 2009 3:55 PM
Points: 7, Visits: 23
Technology Spechnology.

I use Stored procs so I can encrypt them and deploy critical business logic to client sites and not have someone rip off my source code.

I use them because they are easier (not foolproof) to secure from injection attacks and they hide the database schema from would be IP thieves.

I use them because they are faster. One one project I did we took the processing time of the nightly data uploads using from 36 hours (using visual basic) down to 4 hours using well constructed SQL procs, platter splattering the read write functions and stuffing as much memmory as we could fit into the server.

I use them because I can have high value (dedicated local client) and low value (web based) clients access through a single source (Stored procedures).

I use them because they require a higher level of discipline to create, so are a good indicator of something that can be not rushed.

Thats why people still like them, they may not be technically perfect, but they are practically great.
Post #543211
Posted Wednesday, July 30, 2008 1:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:44 AM
Points: 76, Visits: 379
I think some comments here are missing the point.

The question is not whether it is a good idea to use stored procedures, the question is, is it a good idea to have four completely trivial stored procs for every situation where an application needs to access the database.

Maybe I'm old-school, but I see the completely meaningless repetition of a data structure with trivial functionality as a complete waste of time and a huge maintenance burden.
I've always written parameterised SQL statements for simple table CRUD operations, and I've actively weeded out trivial sprocs when some contractor has used 'best practice' and incuded them.
They are a complete waste of space.

The only time it is excusable is when the sprocs are auto-generated.

Of course when the operation is non-trivial, if you want wrapping for future development, then get on your sproc boots and fire away. The principle is, 'if there's a good chance you'll never need it, then don't do it until you do need it'.

Even then, there is a good argument for doing the wrapping in your ORM layer rather than in the DB using sprocs. Depends entirely on circumstances.
Much of your ORM layer should be autogenerated nowadays, too.

Ben McIntyre
Post #543224
Posted Wednesday, July 30, 2008 2:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 4, 2012 9:20 AM
Points: 583, Visits: 1,060
Databases crunch numbers and do set based math like theres no tomorrow - no OOP language can perform as fast doing loops.

Hell yes! Single source for integrity and rule enforcement is where its at.

I've looked at LINQ and at first glance my instincts are saying looks good but why do this?

The only thing I can think is marketing gimick, and peer pressure - SQL has to be seemless with other datatypes. "Dumbass developers should only be using one access method period!"

Danger is poorly developed database structure and relationships will cripple it.






Hiding under a desk from SSIS Implemenation Work
Post #543228
Posted Wednesday, July 30, 2008 2:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 9, 2011 7:49 AM
Points: 343, Visits: 188
For trivial inserts and deletes, there can be an argument for avoiding stored procedures but, for anything more complex, there are clear performance gains. The biggest bottleneck is likely to be network bandwidth - you don't want to be downloading volumes of data just so that you can do complex joins less efficiently in your app than you could on the database server (and perhaps return a single value). One has to consider the whole system holistically although this can only be done really well by people who are multi-skilled across dba and developer roles - something of an artificial distinction anyway in my view. Neither end of the spectrum can perform well without an understanding and appreciation of the other.



Post #543230
Posted Wednesday, July 30, 2008 2:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2008 4:53 PM
Points: 147, Visits: 11
hear, hear! I've been arguing against ritualistic sprocs for years. By generating parameterized sql on the fly using reflection, I can eliminate the vast majority of my sprocs and get CRUD access to a new table in minutes, with no discernible sacrifice in performance and no exposure to sql injection. Hey, always use the appropriate tool for the job, and always factor in what you're most comfortable with and best at. But the mindless mantra that Everyone Must write the Same Four Sprocs over and over again is counter-productive.
Post #543233
Posted Wednesday, July 30, 2008 2:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 7, 2012 5:00 AM
Points: 8, Visits: 60
As per Microsoft documentation, SQL was not intended as an object orientated programming language, it is a procedural system that stores and moves data.

Even though the Microsoft employee, whose name you are comfortably not mentioning, is telling people not to use stored procs, Microsoft (documentation) still advocates that it is best practice and until they say otherwise, I'll use it.

I feel that I have much more control over what is done and how objects are accessed inside a proc.

As for the performance benefit I agree. Level of performance is decided by how you construct your query.

There are pros and cons to maintaining procs. The biggest pro for me is when I have used them in my SSIS packages and a change to the SQL logic is easy without affecting the SSIS packages etc.
Post #543235
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse