To write SPs, or not to write SPs, that is the question....

microsoft.public.sqlserver.programming

7 Nov 2006 11:42 PM

Kevin S. Goff

Hi, all...

I'm involved in a debate with someone over the benefits and pitfalls
of stored procedures, versus other methods [mainly, SQL pass-through].
[yes, i know, this has been debated for years]

As an applications developer, I use SPs for most database operations,
and always use them as a starting point. I've found my productivity
and effeciency increased when I started using them regularly.    I'm
not opposed to SPT, I just find SPs to be a cleaner way to go.

If I had a nickel for everytime an I.S. manager breathed a sigh of
relief when I told him/her that an bug fix/change/enhancement could be
made by simply changing a stored proc, I'd have plenty of nickels.

Anyway, here are the arguments that have been made against SPs....I'm
not asking anyone to read through all these in detail and give long
answers. But if anyone has some strong opinions [one way or another]
on any of these, feel free to throw in your two cents.

In some cases, I know how to respond, because I've used scripts that
make these 'maintenance issues' pretty painless.   [and some of the
arguments are so general that they mean very little]....I'm just
curious how some of you would respond to any of these...

    1- If a SP takes one second to complete, it means it eats
resources (CPU, Memory, R/W Locks, Disk I/O) from other processes that
expect inmediate response, slowing that down.

    2- Performance? In most cases there is no measurable
difference as both SPT and SP execution plans are cached. However
since the inflexible nature of SPs you might get screwed when you want
to be smart and save you writing several SPs and use parameters to
influence the where clause of the SQL statement because in such cases
the plan is not refreshed and givin the new values of the parameters
your execution plan might cause your query to be very slow. (happened
to me a couple of times which forced me to recompile it at every
execute)

    3-SPs at a time were a good idea, because on older hardware
you could save some significant time by precompiling the SQL
statements. However they have lost much of their value along the way.

    4-Inserting and updating records with SP is a PITA and you
have to wonder whether you want this on an evolving system, each time
table structures change, you'll have to go through dozens of SP to
take care of it. Not exactly fun and and efficient use of time if you
ask me.

    5-SPT is generally more flexible than SPs

    6-The contents of a SP can be passed as SPT as well, producing
the exact same result in about the same time with the same performance
characteristics in server execution time (debunk roudtrip arguments).

    7-Things change, and the changes over time have reduced the
need for SPs. Sometime you have to go back to square one and oversee
the list of advantages and disadvantages. Many people already have
done that and you can read their story all over the web, just google
on "Stored procedures bad".

    8- Saves maintenance? Bogus, This probably is the worst case.
When maintaining the database things might be a total nightmare.

    9-Don't forget the concurrency problems that could occur quite
easily where actual processing is done in large time consuming SPs,
especially with larger numbers of users in the system. Depending on
the type of database application, you can set it isolation level to
dirty read, easing the pain a bit, but in situations where the level
of accuracy of data is of very high importance (e.g transactional
financial databases) you probably have to set it to serializable in
which writing data munging SPs is a real thread to concurrency and
have to watch your back for deadlocks, even when your SP only reads
records in stead of writing).

    10- You do not seem to understand that the contents of a SP
can be passed as SPT as well, producing the exact same result in about
the same time with the same performance characteristics in server
execution time (debunk roudtrip arguments).

    11--Security? You could probably do the same with role based
security and views, though I can see certain applications could be
helped with one static interface to the data. But this really is the
exception rather than a commonality.

Thanks in advance,
Kevin