Comment On The Stored ÜberProcedure

Impressive.  
Most impressive.

and painful.  It makes me sad to think that whoever made this is probably getting paid lots more than me.

 If the specification reads:

"All database operations shall be accessed by a stored procedure..." then Jon's predecessor did EXACTLY what he was told!

 

"The great thing about computers is that they do PRECISELY what you ask of them.

The problem with computers is that they do PRECISELY what you ask of them."

While I agree this is a WTF, I can (sort of) see how it might have come to pass...

I once had to write a proc to implement a query that had 38 user-specifiable fields. Naturally, I wanted to optimize the thing since only one or two would be used on most occasions. I wound up building a table of queryable fields and matching table and where-clause terms to be included for each search term, and then  checking each argument in turn: if not null, then include the relevant tables and where-clause in the query-string. Then normalize it to eliminate duplicates, and execute whatever it generated. Project rules required all db-logic to be in stored procs, so I had a proc generating the sql it needed and then dynaimcally executing it. It was thoroughly documented, but I feel the pain of whoever had to change that thing after I left the place.

Sweet.  I thought we covered this with vector oriented programming...

 

object DoEverythingConceivable(string whatToDo, object whatToDoItWith)

 

Or maybe DoEverythingConceivable is just wrapper for this.

Just when I thought I'd seen it all, here comes TheDailyWTF.com to prove me wrong.

I think you meant "loser coupling"

The sad thing is that he did

param = sqlCmd.CreateParameter("int_01_in", adInteger, adParamInput, 8, Null)
sqlCmd.Parameters.Append param

param = sqlCmd.CreateParameter("int_02_in", adInteger, adParamInput, 8, Null)
sqlCmd.Parameters.Append param

...

When he should have done

Dim val as String
for(int i = 0; i < 50; i++) {
val = i;
if(i < 10)val = " " + val;
param = sqlCmd.createParameter("int_" & val & "_in",adInteger, adParamInput, 8, Null)
sqlCmd.Parameters.Append param
}

(mind, my VB is a bit rusty, so this is probably not quite well written. But then, VB is a WTF in and of itself)

Ah, code reuse. What a joyous concept!

Oops, typo.

Replace
if(i < 10)val = " " + val;
With
if(i < 10)val = "0" + val;

Too bad I can't JUST EDIT THE POST.

ParkinT:

 If the specification reads:

"All database operations shall be accessed by a stored procedure..." then Jon's predecessor did EXACTLY what he was told!

 

"The great thing about computers is that they do PRECISELY what you ask of them.

The problem with computers is that they do PRECISELY what you ask of them."

Damn, you beat me to it!

Joel on SnagIt:

You think this stored procedure is bloated? Boy, are you in for a surprise. By using SnagIt, I can store my luggage, car and blow up doll in a stored procedure.

Indeed...

>BiggBru

It could be worse... at least, the the param variable was reused!

Just imagine: Dim param1 As ADODB.Parameter, param2 As ADODB.Parameter, param3 As ADODB.Parameter, param4 As ADODB.Parameter...

Is it just me, or does this not handle ternary boolean values, and file-not-found?

Anonymous:

I think you meant "loser coupling"

Isn't that where baby nerds come from?

Anonymous:

I once had to write a proc to implement a query that had 38 user-specifiable fields. Naturally, I wanted to optimize the thing since only one or two would be used on most occasions. I wound up building a table of queryable fields and matching table and where-clause terms to be included for each search term, and then  checking each argument in turn: if not null, then include the relevant tables and where-clause in the query-string. Then normalize it to eliminate duplicates, and execute whatever it generated. Project rules required all db-logic to be in stored procs, so I had a proc generating the sql it needed and then dynaimcally executing it.

Maybe I'm stupid but . . . there's got be a simpler way?  Your DBMS already has a query optimizer, do you need to do its job for it?

Anonymous:

While I agree this is a WTF, I can (sort of) see how it might have come to pass...

I once had to write a proc to implement a query that had 38 user-specifiable fields. Naturally, I wanted to optimize the thing since only one or two would be used on most occasions. I wound up building a table of queryable fields and matching table and where-clause terms to be included for each search term, and then  checking each argument in turn: if not null, then include the relevant tables and where-clause in the query-string. Then normalize it to eliminate duplicates, and execute whatever it generated. Project rules required all db-logic to be in stored procs, so I had a proc generating the sql it needed and then dynaimcally executing it. It was thoroughly documented, but I feel the pain of whoever had to change that thing after I left the place.

Please send it in.  Alex will put it on the front page!

This must be the subtle way a DBA tells his boss that he's doing everything.

Guess it didn't work.

Now that's good material, LOL!

GoatCheez:

ParkinT:

 If the specification reads:

"All database operations shall be accessed by a stored procedure..." then Jon's predecessor did EXACTLY what he was told!

 

"The great thing about computers is that they do PRECISELY what you ask of them.

The problem with computers is that they do PRECISELY what you ask of them."

Damn, you beat me to it!

That is because I am a bit farther north (in Florida) than you!  The electrons has less distance to travel.

Ouch.  My right buttock has locked up!  Someone please pass me the named pipe.

[moosely-coupled stored-proc]

Cheers! [:'(]

The Code is in VB.Net....enough said.

    Er...just plain old VB..even worse.

Maybe whoever wrote this code was getting paid per line.  Why reuse code when you can get paid more? :)

Volmarias:

Dim val as String

for(int i = 0; i < 50; i++) {

val = i;

if(i < 10)val = " " + val;

param = sqlCmd.createParameter("int_" & val & "_in",adInteger, adParamInput, 8, Null)

sqlCmd.Parameters.Append param

}



...



Ah, code reuse. What a joyous concept!

There seem to be a scary number of WTF-coders out there who haven't quite grasped the concept of a for loop, don't there?

makomk:

Volmarias:

Dim val as String

for(int i = 0; i < 50; i++) {

val = i;

if(i < 10)val = " " + val;

param = sqlCmd.createParameter("int_" & val & "_in",adInteger, adParamInput, 8, Null)

sqlCmd.Parameters.Append param

}



...



Ah, code reuse. What a joyous concept!

There seem to be a scary number of WTF-coders out there who haven't quite grasped the concept of a for loop, don't there?

Ah, the old "mangles newlines and > if someone posting with the rich text interface quotes a post made on a browser that doesn't support it". (CS 2.0 has at least partially fixed this - it may still mangle newlines though, needs further testing.)

Alex Papadimoulis:

Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.

I have to ask; why do people think stored procedures are the way to go? I know most people on this site are .net oriented, and I know MS for the longest time told people to use them (they have since gotten away from that, but hey, it is 2006), but what architectural reason could one use to justify this travesty?

Don't get me wrong, SPs have their place, but to use them for all your dataaccess and business logic is just plain nuts. If you are using VS.NET 2k5, look at DataSets or nHibernate. If you are using Java, take a gander at Hibernate (or EJB 3....though I still am leary of EJB) and, of course, there is Active Record for Ruby.

I suspect he didn't feel comfortable creating new stored procedures, and instead kept feeding this one more parameters.

It's always funny to see a "programmer" avoid learning something new, and instead go to great lengths to stay stupid.

Incidentally, correct VB would be:




Dim val as String, i As Integer

For i = 1 to 50

  val = i

  If i < 10 Then val = "0" + val

  param = sqlCmd.createParameter("int_" + val + "_in",adInteger, adParamInput, 8, Null)

  sqlCmd.Parameters.Append param

Next



My VB is also slightly rusty, but I'm pretty sure that's right...

loneprogrammer:

Anonymous:

I once had to write a proc to implement a query that had 38 user-specifiable fields. Naturally, I wanted to optimize the thing since only one or two would be used on most occasions. I wound up building a table of queryable fields and matching table and where-clause terms to be included for each search term, and then  checking each argument in turn: if not null, then include the relevant tables and where-clause in the query-string. Then normalize it to eliminate duplicates, and execute whatever it generated. Project rules required all db-logic to be in stored procs, so I had a proc generating the sql it needed and then dynaimcally executing it.

Maybe I'm stupid but . . . there's got be a simpler way?  Your DBMS already has a query optimizer, do you need to do its job for it?

There WAS an optimizer, but the requirements forced checking non-indexed columns for most of the fields, which forced the DBMS to do a table scan (I inherited the schema and was not allowed to change it). Show-plan detailed a series of nested table-scan loops that made you choke. The only way to prevent the multiply-nested table scans was to not reference the unused columns (this way, only the users who referenced the data paid the price). My boss recognized the problem, but the data was used by so many other groups that we just couldn't reorganize it.

BTW: if you used ALL of the parameters (nothing was null), the (Oracle) server on what was then, a fairly high-powered unix box) returned rows at the mind-numbing speed of approximately 3 rows per SECOND

To be fair to Oracle, the query was totally unreasonable, and it did grind through the work without any errors.

 

 

Übergay

makomk:

Incidentally, correct VB would be:

Dim val as String, i As Integer
For i = 1 to 50
  val = i
  If i < 10 Then val = "0" + val
  param = sqlCmd.createParameter("int_" + val + "_in",adInteger, adParamInput, 8, Null)
  sqlCmd.Parameters.Append param
Next

My VB is also slightly rusty, but I'm pretty sure that's right...

Actuall, I think this would be better:

Dim i as Integer

For 1=1 to 50

  param=sqlCmd.createParameter("int_"&format(i,"00")&"_in",adInteger,adParamInput, 8 Null)

  sqlCmd.Parameters.Append Param

Next

 

 

 

codeman:

There WAS an optimizer, but the requirements forced checking non-indexed columns for most of the fields, which forced the DBMS to do a table scan (I inherited the schema and was not allowed to change it). Show-plan detailed a series of nested table-scan loops that made you choke. The only way to prevent the multiply-nested table scans was to not reference the unused columns (this way, only the users who referenced the data paid the price).

I do not understand how the columns are "unused" if you are referencing them.  That sounds like they were being used, at least in somewhere in the query.

Do you mean you had something like:

SELECT RECORD FROM RECORDS WHERE RECORD_FUNK_LEVEL = ''

and you just wanted to strip out empty fields from the where clause?

ParkinT:

GoatCheez:

ParkinT:

 If the specification reads:

"All database operations shall be accessed by a stored procedure..." then Jon's predecessor did EXACTLY what he was told!

 

"The great thing about computers is that they do PRECISELY what you ask of them.

The problem with computers is that they do PRECISELY what you ask of them."

Damn, you beat me to it!

That is because I am a bit farther north (in Florida) than you!  The electrons has less distance to travel.

If a man makes a statement in the middle of a forest, where his wife can;t hear, is he still wrong?

Anonymous:

Alex Papadimoulis:

Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.

I have to ask; why do people think stored procedures are the way to go? I know most people on this site are .net oriented, and I know MS for the longest time told people to use them (they have since gotten away from that, but hey, it is 2006), but what architectural reason could one use to justify this travesty?

Don't get me wrong, SPs have their place, but to use them for all your dataaccess and business logic is just plain nuts. If you are using VS.NET 2k5, look at DataSets or nHibernate. If you are using Java, take a gander at Hibernate (or EJB 3....though I still am leary of EJB) and, of course, there is Active Record for Ruby.

A stored procedure's execution plan (or any other query's execution plan for that matter) is stored in the Procedure Cache in SQL Server's memory.  That's Library Cache for any Oracle folks.  When you call a stored procedure several times, the first call gets to generate the plan, and the next bunch of executions do not need to go through all the hoops and hassles of generating a new plan for each run (which is what dynamic or generated SQL statements will just about always do).  Because of this, using stored procedures (and by that I mean using them correctly) can result in a 33% boost in performance.  Not using them will result in your procedure cache getting cluttered with items like

select * from customer where customer_id = '816A4E82-615C-4BD7-9293-D92ED8BAE0ED'

Disgruntled DBA:

A stored procedure's execution plan
(or any other query's execution plan for that matter) is stored in the
Procedure Cache in SQL Server's memory.  That's Library Cache for any
Oracle folks.  When you call a stored procedure several times, the
first call gets to generate the plan, and the next bunch of executions
do not need to go through all the hoops and hassles of generating a new
plan for each run (which is what dynamic or generated SQL statements
will just about always do).  Because of this, using stored procedures
(and by that I mean using them correctly) can result in a 33% boost in
performance.  Not using them will result in your procedure cache
getting cluttered with items like

select * from customer where customer_id = '816A4E82-615C-4BD7-9293-D92ED8BAE0ED'

Thanks
for the thoughts. Unfortunately, I think some of this is misleading. If
you are talking about MSSQL, the cache actually extends to all sql
statements, not just stored procedures. And even if it didn't, the
maintenance nightmare that is SPs would negate any speed increase
(which could have been gained in other ways). Programmer productivity
should be the #1 optimization.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx



and

http://www.codinghorror.com/blog/archives/000117.html

Reminds me of Terry Pratchett's "one man, one vote" democracy ("Vetinari was the man; he had the vote").

"All database operations are executed by a stored procedure. This is it."

Akkkk!

That thing has more arguements than me and my ex.

Anonymous:

Don't get me wrong, SPs have their
place, but to use them for all your dataaccess and business logic is
just plain nuts. If you are using VS.NET 2k5, look at DataSets or
nHibernate. If you are using Java, take a gander at Hibernate (or EJB
3....though I still am leary of EJB) and, of course, there is Active
Record for Ruby.

Its awe inspiring on what a horrendous job Sun did with EJB entity
beans.  It was so bad that even when they offer a completely new
framework with the same name is still causes Java developers to
reflexively shudder.

loneprogrammer:

codeman:

There WAS an optimizer, but the requirements forced checking non-indexed columns for most of the fields, which forced the DBMS to do a table scan (I inherited the schema and was not allowed to change it). Show-plan detailed a series of nested table-scan loops that made you choke. The only way to prevent the multiply-nested table scans was to not reference the unused columns (this way, only the users who referenced the data paid the price).

I do not understand how the columns are "unused" if you are referencing them.  That sounds like they were being used, at least in somewhere in the query.

Do you mean you had something like:

SELECT RECORD FROM RECORDS WHERE RECORD_FUNK_LEVEL = ''

and you just wanted to strip out empty fields from the where clause?

 

Sorry, didn't want to get into a long-winded explanation. Basically, the users were given the ability to look up transactions matching a choice of any combination of 38 searchable attributes, only about 4 of which were indexed columns. The code passed into the stored proc either passed null (if they didn't specify anything on that search field) or "some value" (if they did want to search on that value). The proc then had to do something like (greatly simplified here):

select ... where ((col1 = @param1 and @param1 is not null) or (@param1 is null)) and ...

For one column, this isn't too bad, but multiply it 38 times, with nested subqueries, and inner and outer joins, and you can visualize the horror. Since most of the fields were usually left blank (passed as null), more than 90% of the underlying sql served no purpose except to burn cycles.

BTW: if all the search fields were specified, this monstrosity had more than two dozzen union'd queries, each consisting of multiple inner and outer joins and nested sub-queries, and the worst case sql "statement" was more than 1,000 lines long. If I pre-stripped out the stuff the user didn't ask to search on, and only generated SQL for what they specified, I could eliminate most (if not all) of the unions, many of the joins and most of the sub-queries - things the optimizer had no way of knowing to do, and leave the optimizer to do what it does best, on a minimized query.

Yes, we all knew it was stupid - the right way would have been to normalize the data, put in relevant indices and put the whole thing in a view, but my boss had his marching orders, so we all just groaned and lived with it.

 

As soon as I saw the title of this one in my RSS reader I knew exactly how things were going to play out.

And then it was worse.

Damn.

Alex Papadimoulis:

Though they take a bit more time to
develop upfront, using database stored procedures are definitely the
way to go for most information systems. They provide a looser coupling
to the "data layer" by limiting the points of entry and offer stronger
cohesion by breaking operations into reusable functionality.

Considerably more time upfront and considerably more time in
maintenance.  Every time a programmer has to tackle software using
stored procedures, calamity ensues.  And for what benefit?  I
have yet to see the case where SPs increase reliability or reduce
bugs.  Maybe improve performance.  Unfortunately, developer
time is a lot more expensive than the value of the time saved.



Violating patterns by spreading business logic across layers and adding
more variables and complexity into the mix.  SPs are not a
good idea.



The database stores data.



The business logic manipulates it.



The view displays it.



Sticking to these simple principles is a Good Idea.  For example,
consider the plight of your typically hapless developer.  There is
no reason that he or she should have to also be an expert in whatever
database you're using, let alone anything.  Period.  That
means using services that do the mapping between business objects and
persistence.  That work is best handled by someone who is a
database expert.



I can come up with more reasons but there are plenty of materals on the
subject which support this.  To get you started, Google on "Gang
of Four", "Spring", "Hibernate", "Pragmatic Programmer", and so
on.  Enjoy.

and of course, the REAL WTF is that it was done in VB right [:D]

Alex Papadimoulis:

They provide a looser coupling to the "data layer" by limiting the points of entry

I always thought they make for more coupling, because have to recreate sp's when you want to use a different database. For instance, in MS SQL Server you write @var, in Oracle you write :var.

Anonymous:

Alex Papadimoulis:

Though they take a bit more time to
develop upfront, using database stored procedures are definitely the
way to go for most information systems. They provide a looser coupling
to the "data layer" by limiting the points of entry and offer stronger
cohesion by breaking operations into reusable functionality.

Considerably more time upfront and considerably more time in
maintenance.

Wrong and wronger.

Anonymous:

Every time a programmer has to tackle software using
stored procedures, calamity ensues.  And for what benefit?  I
have yet to see the case where SPs increase reliability or reduce
bugs.

Every project I've worked on had increased reliability and reduced bugs directly attributable to using SPs. A lot merely from being able to unit test the data access before any businesss logic gets involved.

Anonymous:

Violating patterns by spreading business logic across layers and adding
more variables and complexity into the mix.  SPs are not a
good idea.

Let's see...how do we fix this? Oh yeah, don't put business logic in the SPs.

Anonymous:

The database stores data.



The business logic manipulates it.



The view displays it.



Sticking to these simple principles is a Good Idea.  For example,
consider the plight of your typically hapless developer.  There is
no reason that he or she should have to also be an expert in whatever
database you're using, let alone anything.  Period.  That
means using services that do the mapping between business objects and
persistence.  That work is best handled by someone who is a
database expert.

Which is why we have database experts write the SPs. Duh.

Look, if you use any technique improperly, you're gonna cause yourself pain. It's quite clear to anyone who has worked on any project of signigicant size that SPs improve almost every aspect of the project. The only real downside to SPs is being able to support multiple DBMSs, and I think that if you really need to do that for a large system, you have bigger problems.

Anonymous:

Alex Papadimoulis:

They provide a looser coupling to the "data layer" by limiting the points of entry

I always thought they make for more coupling, because have to recreate sp's when you want to use a different database. For instance, in MS SQL Server you write @var, in Oracle you write :var.

Alex means looser coupling between the app logic and the database.

Obviously the SP is rather tightly bound to the DBMS. See the last point in my previous post.

Anonymous:

Its awe inspiring on what a horrendous job Sun did with EJB entity
beans.  It was so bad that even when they offer a completely new
framework with the same name is still causes Java developers to
reflexively shudder.

Then we must see things differently.  What's so horrendous? 
Write up to four very short interfaces to go with your EJB class. 
Add stuff to a simple XML file with a well-documented structure.



Learning it was hard, but using it was easy.  An EJB 2.0 compliant
entity bean with a dozen persisted properties took me fifteen minutes
to write.  By hand, without any IDE (unless you count gvim and
ant).



Of course, I did smart things like using container-managed persistence,
because I know the internals of a vendor's JDBC driver are more
efficient than anything I can write.



Now, if you tried to muddle your way through it instead of reading the
EJB 2.0 specification, then I imagine it would have been a lot tougher.

John Smallberries:

Look, if you use any technique improperly, you're gonna cause yourself pain. It's quite clear to anyone who has worked on any project of signigicant size that SPs improve almost every aspect of the project. The only real downside to SPs is being able to support multiple DBMSs, and I think that if you really need to do that for a large system, you have bigger problems.

I have worked in this space for about 15 years. I have worked with the government, on projects for the olympics, for comapanie such as Enron, SBC and IBM and I have started two software companies, one of which I sold. Never have I seen a project improve because of SPs. Generally speaking, SPs were almost always directly related to projects slipping and increasing in budget.

Stored procedures were good back when the only alternative was writing straight SQL in your middle tier somewhere (and even then, most of us wrote our own ORMs), but today, in 2006, they serve very little purpose. I view them as a vestige of software's younger, less mature years. You can view them fondly if you wish, but to move on to serious development, you will have to let go or the world will pass you by.

Anonymous:

Alex Papadimoulis:

They provide a looser coupling to the "data layer" by limiting the points of entry

I always thought they make for more coupling, because have to recreate sp's when you want to use a different database. For instance, in MS SQL Server you write @var, in Oracle you write :var.

See The Secret to Better C for one possible solution to this problem.

Anonymous:

Stored procedures were good back when the only alternative was writing straight SQL in your middle tier somewhere (and even then, most of us wrote our own ORMs), but today, in 2006, they serve very little purpose. I view them as a vestige of software's younger, less mature years. You can view them fondly if you wish, but to move on to serious development, you will have to let go or the world will pass you by.

...this is the place where you would mention the more mature method that all the cool kids are using.  I'm still hand forging stored procedures over a coke fired furnace in my remote mountain cave, and wouldn't mind knowing what the current state of the art is.

Anonymous:

John Smallberries:

Look, if you use any technique improperly, you're gonna cause yourself pain. It's quite clear to anyone who has worked on any project of signigicant size that SPs improve almost every aspect of the project. The only real downside to SPs is being able to support multiple DBMSs, and I think that if you really need to do that for a large system, you have bigger problems.

I have worked in this space for about 15 years. I have worked with the government, on projects for the olympics, for comapanie such as Enron, SBC and IBM and I have started two software companies, one of which I sold. Never have I seen a project improve because of SPs. Generally speaking, SPs were almost always directly related to projects slipping and increasing in budget.

Stored procedures were good back when the only alternative was writing straight SQL in your middle tier somewhere (and even then, most of us wrote our own ORMs), but today, in 2006, they serve very little purpose. I view them as a vestige of software's younger, less mature years. You can view them fondly if you wish, but to move on to serious development, you will have to let go or the world will pass you by.

I guess you missed my first sentence above.

As a corollary, I've also found that the bigger the company, the poorer the software quality produced.

Bullet:

ParkinT:

GoatCheez:

ParkinT:

 If the specification reads:

"All database operations shall be accessed by a stored procedure..." then Jon's predecessor did EXACTLY what he was told!

 

"The great thing about computers is that they do PRECISELY what you ask of them.

The problem with computers is that they do PRECISELY what you ask of them."

Damn, you beat me to it!

That is because I am a bit farther north (in Florida) than you!  The electrons has less distance to travel.

If a man makes a statement in the middle of a forest, where his wife can;t hear, is he still wrong?

 

I have been told Yes.