Who Needs Stored Procedures, Anyways?

blog
61

My two cents. First of all, I notice that everyone is a bit self-conscious about how long their comments are on this topic. From all that I have read you are all doing very well expressing your views. So vent and feel good about it!

I myself am a Dynamic SQL believer simply for the fact that to get optimal query speed whether it be SP or Dynamic SQL, YOU LIMIT THE NUMBER OF FIELDS NEEDED in your request. If you are using dynamic SQL, you simply Add/Remove fields in the selection. Painless!

When you are a programmer trying to increase the application speed you usually find the problem is a process that only requires 3 fields, but for some reason, the SQL was written as “SELECT *…” (because at the time you thought all of them would be needed). If you are using dynamic SQL in a function called “GetThisData” then all you need to do is change the “SELECT *” to “Select Item1, Item2, Item3”and life goes on. For the die-hard SP followers reading, please tell me the easy way to find and then fix this when using a SP for a program that is not just in use for your company, but sold around the world and every customer has their very own database.

Finally, there is a lot of talk about abstraction in support of SP. What is my business layer for? Just to make another call someplace else to get the data? How many more layers do you really need? And are you honestly getting more done with 2 layers that do the same as one?

62

someone commented stored procedures give you scability. huh? in a web farm, each server can be doing some of the logic that most developers put in a stored procedure. much easier to scale a web farm than a database server.

everyone has made good points but most of us are smart enough to know there is always a trade-off. i use a combination of stored procedures an parameterized SQL. i use iBatis which is a data mapper more than a full blown ORM. iBatis gracefully handles both inline SQL and stored procedures. i use simple stored procedures to enforce relational integrity, for example cascading deletes. in this scenario ORMs are too network chatty. if there is business logic then i try to do it in code using iBatis’ DAO classes or parameterized SQL. two benefits off the top of my head:

  1. scalability - the database isn’t doing business logic processing for an entire web farm
  2. business logic isn’t interspersed between our C# business layer and in stored procedures

i use a database for what it is, a data store. not only does it perform and scale better when you use it as such, the code is more maintainable.

63

Here are some reasons why I prefer inline (though we do use stored procedures here and there, especially for reporting). t

  1. Dynamic SQL. Most of the list screens we have in our application include various combinations of search criteria that may or may not be used. Stored Procedures (at least pre-SQL 2005, which I havent used yet) dont let you conditionally change the statement, you either repeat the modified statement for each different criteria) or you do (field = THIS PARAMETER or THIS PARAMETER is null) style coding which I have seen greatly slow down the efficiency of a query when there are many of those conditions. There are places in our Dynamic SQL screens where we would have to write 1000s of Stored Procedure statements to mimic all the ones that can be generated dyamically.

  2. Searching your code. When I need to see where fields are being used for update and insert (which we do not do via stored procedures) I can just search the code directories and easily find the places using it). If in stored procedures I have to do one pass to find which stored procedures are involved and then many more passes to find where all these procedures are called. I believe it is far more maintanable to see usage of your SQL right in your code… whether it is in a DB layer or not.

  3. Showing your SQL statements. In our ASP (classic and .Net) we call GetDisconnectedRecordset(), ExecuteSQL() or GetDataset() functions (or methods) that wrap around the native ADO code. In doing so, we can consistently trap errors, record out of bounds timings (outside of performance monitoring), and even more importantly FLIP A SWITCH ON OUR WEBSITE and instantly see every query going on to load every page and during every update. That alone is worth its weight in gold. Debuging in many cases is reduced to seconds. And for new people… you want to see what tables and fields are being used? Turn on ShowSQL and get your answer right on screen!.. HUGE!.. and possible because its INLINE SQL and all run through common routines. Our common routines also include logic to look for, not process, and alert us to any injection attacks. The “showSQL” functionality when in classic ASP shows up right on the page where the SQL is called (very 2 tier)… in .Net where we have a data layer, our SQL calls write their processing info to a string that we can then get the value of pre-render to display when ShowSQL is on (which is only allowed for developers)… so that is a way to still keep your tier separation, if your app needs it, but still have showSQL functionality. The other thing I can note, with our inline usage, whenever we choose to move a statement to a stored procedure, we turn on Show SQL and in cutting and pasting we are at least 80% of the way there in one shot. We format our SQL statements in code with spaces and carriage returns and on output replace with %20 and BR so the statements are VERY readable.

  4. Many people dont like to talk about the actual needs of the application you work on. Do you need true Ntier separation? Are you overworking for potential scalability that may never matter? If you structure your app well, it should come down to matters of organization where you could separate to different DLLs and physical machines IF you need to. If you are building EBAY with millions of hits every hour, you have different needs than a company specific app not exposed to the public… with 1000 users and only 100 active at any time. In that type environment, the complexity of splitting your tiers up so dramatically may not yield you much benefit either in development or performance. In fact, the more tiers you have, the more scalable you MIGHT be (if you did it well engouh), but every tier adds more going on in terms of processing. If you read your data from the dataset/recordset and then move it to XML, array, string, etc, and then have to unpack it in the next tier just because you refuse to use DB objects in that tier, imo, that can be going too far in many situations and slowing down your processing needlessly. Calling your SQL within your ASP page code isnt always automatically wrong or due to having weak experience… you can make a case for your situation that it might be the right thing to do (especially if you arent dealing with lots of it and lots of concurrent users). Part of our job is to use the right solutions for the task at hand and there is such a thing as overworking a solution! Same with objects… object oriented isn’t automatically better, especially on the web. If you are building robust objects and now your page needs a few things from a few “objects” but now you have to load all of them completely to get your data, that is overkill compared to having SQL return only what you need for the page in a statement (even though that is not an object-oriented approach). I always believe the NEED at hand is the best way to dictate the approach… not a blanket approach for everything. I have done many n-tier systems and many two tier… neither is truly an EVERYDAY right or wrong approach, each can be the right approach depending on the need. Especially on the web, if an area lends it self to business objects… do that… if it doesnt, dont be afraid to NOT use business objects there. Ultimately, efficiency matters… and in many cases inline SQL right in the page is going to be more efficient and responsive. As someone said, inline vs stored proc is always an evaluation of pros and cons… both from developer viewpoints, and from the needs of the application. What is right in one case might be wrong in many others, but it doesnt mean each approach itself is always right or wrong.

  5. Maintenance. If every SQL call in a large app is in stored procedures, I think you have a huge mess. It’s alot to look through… and SQL Server (or most database tools) do not give you much in terms of choices in organizing. SQL 2000 and before (again, I havent used 2005)… all stored procedures are in ONE directory and you only control the naming. Even though you can use .SQL script files to initiate changes (and get into source safe) (which we do) it still becomes a mess juggling all of these… especially when some are so simple they hardly are worth the bloating of the DB objects.

  6. Bugs due to stored procedures separation of statements. If you add new fields to a table and thus have to change insert and update stored procedure to accept these fields, but have 6 apps using it. As soon as you change the procedure, you better have updates for ALL those apps in place or else they will all break on the change. If you add new fields and use inline code, each app will generally work fine with the new fields there (if they allow nulls) and you can then work in changes to use those fields by updating those apps one by one all the while the prior version still runs (and there are cases where this is useful). Again, its relative to what your system does, who the users are, and how its rolled out, among many other things.

  7. We use Crystal Reports… and the last thing we want is Crystal controlling the queries… so there we use stored procedures that Crystal Reports will work with.

  8. We have a couple cases where we couldnt run a statement inline (too complex: many joined tables, group by clauses in statements acting as tables within outer queries that are also grouped) and moved it to a stored procedure because that made it run. Some would say, you shouldnt write such complex queries. I say, in relational databases, where you have many tables involved in the answer (this is what normalization leads you to often) if you can write a statement that is VERY complex but runs in 2 seconds compared to one that is made up of many simpler statements but then takes 40 seconds… I lean on the speedy approach!

For me, comparing inline SQL to Stored Procedures is like Comparing Manual Transmissions to Automatic. I think Automatic can isolate you from many things, but in the end, there so many more pros to the Manual transmission that Automatic cant hang with… and thus, thats also why I choose inline over Stored Procedures. It’s not a product of being “lazy” at all, it is that there are many overwhelming advantages that taken altogether make it, for me, the best choice… IF YOU USE IT SAFELY AND WISELY and focus on efficient statements and injection protection. I also hate the notion that you have to isolate the DB from your junior or UI only developers. If that is how you run your business, imo, you run your business wrong. If developers cant handle DB work, they simply shouldnt be developers! Developers need to be encouraged, trained, and expected to handle DB coding and learn how to write efficient SQL Statements (whether inline or in stored procedures), they either have to learn or should leave the profession. Having DBA handle all that and isolating the database from the developer is not an approach I favor. Having DBs review things, look for problems, and help developers, on the other hand is just as valuable as having people who do nothing but test the application!

When applications get too rigid in rules and layers and layer separation, I find very often functionality is skipped because its too hard to do given the structure of the app and the layers. That is a shame, because ultimately, a system is used at least 90% of the time to make someone’s job faster and easier… so the tools and techniques should not routinely impose limits on that goal!

Finally, inline SQL does not equate to crappy sql. You get way more bang for the buck in learning the right techniques to speed up your SQL Statements (hit indices, use joined table results as its own table as if the results were a single table and then join to those results)… and those techniques can produce amazing results whether inline or stored proc and they are the more important bang for the buck. Crappy is independent of tools and techniques. You can write ASP with inline SQL that is very clean. You can write .Net with stored procedures where its a mess. The nature of there being a mess is not driven by or limited by the tools used!

(Sorry for the length, but I unless I missed something, I think I added some significant additional thoughts here).

Too often, I think people use a certain tool or technique because they were told that was the way to do it… and they believe it like a religion. Think deeper than that… there is place for just about everything… stored procedures arent always wrong… inline SQL is certainly not always wrong… but how you use that mix really depends on your application, its purpose, and the size and needs of the user base. I have yet to work on any application where stored procedures for all queries and insert or update statements would have been the best approach and in every case I would have greatly missed the benefits that inline SQL provided in comparison!!

64

“Anyways” is not a word. The correct usage is “anyway.” You wouldn’t write “anyhows” would you?

65

Lets put things in perspective. I work at a place where users build asp.net applications. They stick SQL wherever its convient and they spit out a working application in 2 weeks. Does inserts, updates and all the things that a lot of you would pat yourselves on the back for.

Here’s news for you. Cracking out apps without any regard for good application design is is not rocket science. You don’t even have to be that intelligent to do it. I could teach just about anyone basic programming concepts like conditional statements, for loops and basic SELECT and Insert statements in a couple of weeks they’ll be writing applications that are just about as good as a lot of “professionals” out there.

Without going off on a tangent, calling stored procedures from your code behind or windows form is not a good idea either. There’s something called a “business layer” which is used to abstract your “data layer”. Look it up.

Oh and how many of you actually have had to access databases from different vendors anyway?

So instead of defending putting your SQL Queries Inline, why don’t you crack open a book on application design and learn something that might separate you from some guy in India with a “teach yourself SQL in 5 minutes” book making 4 bucks an hour.

66

You wouldn’t write “anyhows” would you?

Maybes I would.

67

What a load of bollocks. I completely agree with Mike C on this. SPs make code more maintainable, increase performance and help when implementing N tier applications. It also allows you to share business logic between the database and the business objects. You probably write loads of nasty code with UI logic mixed up with business logic. And the argument about SP compatibility, initially this can be overcome by creating new SPs with structured names to indicate version or by simply setting arguments to default values and ordering them correctly.
Try Rocky Lhotkas CSLA.NET site for some rock solid techniques on how to implement data access and business logic using his framwwork.
While you guys are flaming use of English (not American) what does the phrase “Go Figure” actually mean. Its not a sentence is it. Surely its “Go and figure it out yourself” is the correct use!

68

looking through most of these comments, it’s almost as if people don’t know you can use default values for stored procedure parameters that aren’t specified at run-time.

69

My two cents:

I’ve heard this about stored procedures:

  1. They are more secure. But… in every database I’ve seen, the database user has dbo access. BTW, I work in a large bank. Go figure.
  2. You can debug them as easily as .NET code. But… I’ve never seen anyone set up SQL debugging in Visual Studio. Most developers run the SP and hope it works.
  3. They can be version-controlled. But… try putting them in SourceSafe and see if you like it. Honestly, can you tell me how this SP looked a year ago? What were the last 5 changes to it? Who made them?
  4. It’s easy to control schema changes. But… if you’re planning to rename a table, is it easier to hit Ctrl-F in your code and search (or search VSS), or to assess this impact on SPs in the database? Sysdepends is great, I know. I guess I could just script the entire database, dump it to Notepad and look for the table name. Sure.
  5. They’re faster. But… nobody seems to be willing to prove it. And even if they are, nobody cares that an SP is 10ms faster if it’s 10 times harder to maintain.

LINQ, anyone?

70

This debate has been going on for how long, almost 4 years. As I said in the past I have been developing database application since the late 80’s and electro-mechanical devices since the mid-70’s.

We forget that we deal with 1’s and 0’s which running in resident memory is always the fastest method. Anytime we transfer that information via storage or network connections it slows down the process. So I am not advocating only stored procedures, you have user defined functions, views, triggers, and other parts of a DBMS to compile the data to be send back to the requester.

Now companies in the 90’s to make budget developed the easiest methods and many times they scrimped on quality (who won Beta or VHS?). Does not mean it is the right way to develop applications. Also because of market shares and business the best methods are lost to marketing and who has the money.

Still I content that front-ends and data manipulation must be separated to be a successful system. In long term managing of data it is the safest method. Microsoft Visual Studio allows several technologies to protect your business presentation layer from the user interface. Remoting or web services are two technologies to keep your business presentation layer separated from the UI, and ADO.NET controls data concurrency with industry databases.

Now I do think Microsoft or one of its development partners should develop a debugger for SQL that works within Visual Studio. I know the technology exists and have used it successfully in the past. It does need two parts one that resides on the database server and one on the client development computer.

Anyway stored procedures have a valid part within our IT environment. As developers we have to decide on response times and manageability of systems, also availability for the specified business. Some systems were built on limited technologies, it does not make them wrong but they could be improved upon.

71

Mike C: “I could teach just about anyone basic programming concepts like conditional statements, for loops and basic SELECT and Insert statements in a couple of weeks they’ll be writing applications that are just about as good as a lot of “professionals” out there.”

Do you seriously believe that? That is the stupidest thing I’ve heard in a while!!

72

“I’ll give one example: 6 years ago I wrote 3000+ plus lines of C++ code to provide automatic quality control of atmospheric data. Last year I wrote the same application in PL/SQL (95% SQL) in less than 500 lines and it runs 10x faster.”

Dude! What the hell are you doing in your C++?

If automatic quality control means “sorting and searching” for stuff, then sure, SQL would achieve it nicely. But anything other than sorting and searching needs to be done in business logic, i.e. C++ code.

Petey

73

Wow, I just got done reading every comment on this entire page, and I still don’t know what to do. I’ve been given the task of refactoring (more like overhauling) a system which makes judicious use of inline sql in the very BAD way … no prepared statements, no data access layer … just concatenated strings passed to JDBC. It makes me want to become a dentist.

But any-hoo … I wanted to completely redo the way this application accesses it’s data, which it must do in several different business environments supporting both MySQL and Oracle. Do I create stored procedures to handle similar data needs in both databases? Do I simply use Java’s PreparedStatement object and centralize things in a nicely compact DAL? Do I create a Servlet to run on the DB server awaiting my demands? I’ve learned quite a bit by reading all this stuff … except what is the right thing to do. Help me Obi-Wan Kenobi.

74

Tweedledum: I love TSQL and writing stored procedures. ALWAYS use stored procedures. If you disagree with me you’re an idiot.

Tweedledee: I love C# and writing my select statements using a SQLCommand object. ALWAYS stay away from stored procedures. If you disagree with me you’re an idiot.

Pathetic!

You people make me embarassed to be called a software developer.

“Tweedledum and Tweedledee
Agreed to have a battle;
For Tweedledum said Tweedledee
Had spoiled his nice new rattle.
Just then flew down a monstrous crow,
As black as a tar-barrel;
Which frightened both the heroes so,
They quite forgot their quarrel.”

Where’s a monstrous black crow when you need one?

75

It is quite laughable to hear arguments against the stored procedures. As many people have already said, this is not about the amount of code (with quotation marks, concatenation, conversions etc SP win that argument hands down). Most importantly, stored procedures provide a modular design. A good coder does not ponder of SP only after completing the calling constructs. That is for juniors. The SP is an interface to a data server. So, the difference between those who use SP and those who don’t equals careful design vs casual coding. I cannot recall any serious commercial application that uses in-line SQL. Perhaps real programmers have reasons for that.

76

Sjeez, people - Horses for Courses.
A flat out “don’t bother with [sprocs|dynamic SQL]” is just being bone headed.

There are situations where it makes sense not to put your SQL in one place.
There are also situations where you’d be mad not to put your SQL in a sproc.

It all depends upon the project environment and requirements.

77

My God, is this post still getting responses 3 years after it was written. Right now I hate SQL and stored procs as I’ve just finished a 600 line procedure to prepare investment portfolios for Capital Gains Tax. The whole project is over a dozen sp’s and tonnes of code. Could of been written in half the time/code using java or C# but its the way its done here.
Everything has its place and I’ve written several sp’s that couldn’t have been done any other way as efficiently or speedily but this latest project…

78

nice post…
a good online assembly language guide
http://Assembly.co.nr

79

This is a great thread!!! I love all of the differing opinions. I tend to sit on the fence between the business aspect and the technical aspect (as any good architect would do). I am not convinced that there is a right way at all. You know what’s right? Getting the job down on time, on budget, and it works. I’ve never been awarded a bonus because my customer opened up the code and said: “I love the way you isolated the different tiers here”. And don’t talk to me about lower TCO with an embedded SQL vs. SP’s approach. If SP’s are the way to lower TCO, why do DB vendors still support SQL at all? Heck, what version of Oracle are they on? What version of MS SQL? Why not create the uber database paradigm that gets rid of “clunky, embedded SQL” and then show me the independent case study that shows I was able to get rid of 25 developers. Now that’s a compelling reason to put things in SP’s. The answer is quite simple…because there is no right answer for all. Why do we (I use the term we) continue to act like we have the answer for all? We’ve only had OUR experiences and that’s it! Even the GODS of IT only have their experiences.
The argument has been made that hiding everything in SP’s is the best solution. And conversely the argument has been made that embedded SQL is not that bad. Let developers write code, not DB logic. Well, in my experience DBA’s cost a heck of a lot more than developers. And by using SP’s you’ve simply moved the problems you all are trying to solve from the application code to the database layer. That’s it. And now I have to pay some guy huge bucks to add a column to a table, change the stored procedure’s calling parameters, and oh BTW, still have the developer change the code that calls the SP, and put the new attribute on the UI for updating. And that’s better? What about the costs and complexity of coordination this has introduced into my schedule having to deal with two different teams? Isn’t that a real cost also? I’ll ask you all this… In your company are there more developers or DBA’s? However, I like SP’s because I can share one piece of code (sp) with an entire database worth of applications. That’s real value. But it CAN be bad…what if one piece of the app needs to access it differently? With embedded SQL, I open up that piece of code and do the inner join I need for that particular query. I’m assured I’m not affecting all of the other lines of code that access the same table. Or do I copy the SP and make a new one that is essentially the same except for a new inner join? Now I have two stored procedures to change if I add a column. I don’t claim to have the answer. I’m just pointing out that you all don’t either.
What if you have a parameter that you are simply increasing the size of (varchar 50 to varchar 100). I’ve got to not only change the table, but find everywhere that that column is referenced as a calling parm in SP’s, functions, etc… and change it (At least in MSSQL. I really miss Oracle and being able to declare variables as column types of tables). If it was embedded SQL I don’t really care. Change the table, and leave the insert statement alone in the code. I’m done.
And what if you have 100’s of databases (let’s say you’re an ISV and you have one DB per customer because customer’s don’t really like their data intermingled with other customers). Now the high priced DBA’s have to run the script over, and over, and over, and over again to modify each customer’s database stored procedures interface. All you have done is hardcoded attribute lengths. And the testing argument is crap too. Ok…so the DBA can tell that his stored procedure works just fine. But what if the developer calls the stored procedure with a column that exceeds the length of the calling parameter, or a differing data type? Does the DBA testing help that? No…you still have to test the entire thing… period. But I will concede that isolating this logic helps that problem.
And what if I want to minimize round trips to the database when inserting 1 parent and 20 child records and ship that all as one statement? How do I pass in the 20 child records to the SP? Or do I make a round trip calling a SP for each record? And that helps performance? Uh…how?
And the security argument is bogus. This applies only if you have the need for it and if you log into the database with each user’s account as opposed to using a connection pooling approach.
You get the point. There are pro’s and con’s to each approach. I like SP’s or VIEWS ability to hide complex SQL from the developer. Typically, developers don’t write efficient SQL anyway.
I don’t think that there is a right answer for every situation. I think even inside of one organization there is no right answer. It depends on:

  1. The developers ability to write SP’s
  2. The availability of DBA’s
  3. Volatility of the schema
  4. The needs of the application
  5. Schedule and budget limitations
  6. Large vs. small development efforts

There is no right answer. That’s why there is a debate. Do what works for you… Because in three years there will be something new that everyone will hype up as being the end all be all of IT. BTW, whatever happened to network computers? :slight_smile:

80

Hi all,
just me 1.5 cents…

Last year I had to speed up a reporting job that had to be done every 3 moth a year.
The original setup was a java programm getting data out of an oracle database, doing some computation, generated pdf reports and wrote back the result for resuse in the next calc cycle. The whole computation for the mentioned three months (using the result of the last run) for about 14000 customers took about three weeks.
BTW. most of the time was spent in the java part not in the database.

My job was to speed up the whole thing. So i just recoded the x000+ lines java code into y00 lines of pl/pgsql on a postgres database and changed the needed lines of embedded sql in the java code for the report generation. I also did every report from the start, not using “previous” runs. The whole thing took about 24 hours on a small box instead of the big boxe where the original computation was done…

That project told me “do the computation where the data is”. I am sure on the same “big box” with a database like sybase/mssql or oracle or optimizing the algorithm the computation could have be done within 3 - 6 hours.