Many Simple Stored Procedures VS. Few Complex Stored Procedures 
Author Message
 Many Simple Stored Procedures VS. Few Complex Stored Procedures
Hi, I was wondering if anybody could enlighten me as to whether it is
GENERALLY more advantageous to build a couple of very complex stored
procedures with many IF ELSE conditions to handle multiple scenarios
or to simply build many simple stored procedures to handle each
situation.  Which alternative would result in faster processing by SQL
Server?

Your feedback is much appreciated!

Darren



Sun, 28 Mar 2004 02:51:38 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures

This is an issue where you will probbaly find as many answers as there are
developers, but for my part I don't think Performance is the critical issue,
I think source code management to optimize reuseability, extensibility and
ongoing maintenance are the critical issues.

What I would recommend is that you organize your stored procs so that
Developers have a clear and simple organizational structure that allows them
to easily find and reuse any desired piece of functionality.  You want to
avoid having a developer rewrite code in another stored proc to do the same
thing as a piece of existing code.  That increases cost, increases the
probabiility  of reintroducing bugs that have already been eliminated once
before, increases the difficulties of eliminating bugs, (Because they
potentially exist in multiple places) and increases the work of maintenance
(Adding an additional constraint, for example, ot an update Stored proc
would have to be added to ALL the stored procs that implement Update
Functionality for that field.)


Quote:
> Hi, I was wondering if anybody could enlighten me as to whether it is
> GENERALLY more advantageous to build a couple of very complex stored
> procedures with many IF ELSE conditions to handle multiple scenarios
> or to simply build many simple stored procedures to handle each
> situation.  Which alternative would result in faster processing by SQL
> Server?

> Your feedback is much appreciated!

> Darren



Sun, 28 Mar 2004 03:04:26 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures
Hi Charles

I appreciate the suggestion you gave.  When I first began writing stored procedures, I didn't realize how important the naming/organization of stored procedures was, but now that I have over 50 of them for 1 database, I fully see the point you were making.

Thanks

Darren

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 28 Mar 2004 05:07:51 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures
I'd like to say I thought about this BEFORE I experienced the hassles and
problems myself, but unfortunately, I learned this the hard way too...  At
least someone taught me very early that  "sp_"  doesn't mean "Stored
Procedure" !!!

( I still smile (although it's really sad, not funny) when I open a database
for a client and see all the Stored Proc in a database named with sp_ as a
prefix!!)

Charly


Hi Charles

I appreciate the suggestion you gave.  When I first began writing stored
procedures, I didn't realize how important the naming/organization of stored
procedures was, but now that I have over 50 of them for 1 database, I fully
see the point you were making.

Thanks

Darren

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 28 Mar 2004 06:17:51 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures
Darren here are my suggestions.

 Its always better to keep stored procedures in modular fashion. This will
make your testing
easy also aid calling any module in your future development.
As far as processing time is converned i don't see any major difference in
putting all the code
in a single one or you split in small stored procedures(since all are
compliled)

Also bore in mind you should not make stored procedures which are 5 to 10
lines of code.
It requires some planning to decide how to disintegrate into perfect
modules.

Thanks
Sunil.


Quote:
> Hi, I was wondering if anybody could enlighten me as to whether it is
> GENERALLY more advantageous to build a couple of very complex stored
> procedures with many IF ELSE conditions to handle multiple scenarios
> or to simply build many simple stored procedures to handle each
> situation.  Which alternative would result in faster processing by SQL
> Server?

> Your feedback is much appreciated!

> Darren



Sun, 28 Mar 2004 06:37:19 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures

Quote:

> Also bore in mind you should not make stored procedures which are 5 to 10
> lines of code.

Why not? Too small?

In my current project, I decided to make all client access occur through
sprocs. No SQL in client code and no direct table (or even view) access.
Everything has to go through a sproc, so naturally there are plenty of short
ones.

I've been happy with this approach, since it substantially narrows the
interface to the database. This has advantages both for security and for
maintenance.



Sun, 28 Mar 2004 06:49:57 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures

I am with Mark on this one.  Give the user the Stored Procedures they need for their jobs.  Design from the outside (user side) in (database side).  

If you have common functions that can be extracted into smaller procedures, do it behind the scenes.  Look at the tool from Semantic Design for extracting common code, if you have a complex system.  

The "sp_" prefix is as bad as the "tbl_", "vw_", etc.  prefixes BASIC programmers like to use in SQL.  

Get some of the old Structured Analysis, Design and Programming books and re-learn those skills.  

Frankly, with CASE and COALESCE() in SQL, I find that I can put a lot of what would have been flow control into the SQL statments themselves.  Oh yeah, don't forget you can comment SQL code -- even if nobody else seems to do that.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

  _alt.0
5K Download


Sun, 28 Mar 2004 07:27:36 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures
Migrated to this identical approach two or three years ago, and I REALLY
like it.  Will never go back unless I'm forced to deal with existing code
base developed another way.

Charly


Quote:


> > Also bore in mind you should not make stored procedures which are 5 to
10
> > lines of code.

> Why not? Too small?

> In my current project, I decided to make all client access occur through
> sprocs. No SQL in client code and no direct table (or even view) access.
> Everything has to go through a sproc, so naturally there are plenty of
short
> ones.

> I've been happy with this approach, since it substantially narrows the
> interface to the database. This has advantages both for security and for
> maintenance.



Sun, 28 Mar 2004 07:32:27 GMT
 Many Simple Stored Procedures VS. Few Complex Stored Procedures


Quote:
> Migrated to this identical approach two or three years ago, and I REALLY
> like it.  Will never go back unless I'm forced to deal with existing code
> base developed another way.

Cool. I've been happy with it so far, but it's nice to know that it's a
technique that will stand the test of time.


Sun, 28 Mar 2004 08:10:28 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Stored Procedures vs Extended Stored Procedures

2. temporary tables in stored procedures/parameter views vs stored procedures

3. Identifying User Stored Procedures vs. System Stored Procedures

4. Stored procedures Vs Non stored procedures

5. External Stored Procedure vs. SQL Stored Procedure

6. Calling a Java Stored Procedure from another Java Stored Stored Procedure

7. Problem with complex sysindexes and complex stored procedures

8. Stored Procedure vs Remote Procedure Call

9. Stored Procedure calling Stored Procedure

10. Executing a Stored Procedure in a Stored Procedure and selecting on the result

11. Call a stored procedure from another stored procedure

12. Calling a stored procedure with parameters from another stored procedure


 
Powered by phpBB® Forum Software