When should functions and internal stored procedures be created??
-
Hi, I have a stored procedure that creates an issue in a case management system. The stored procedure inserts records into several tables and is about 1.5 A4 pages in length.
A collegue of mine is insisting that the stored procedure should be split into internal stored procedures and functions for reusability, maintainability and readability. The stored procedure has now been split into two stored procedures and seven functions (most of which return table variables).
I feel that the fragmentation of the stored procedure is defeating its purpose. It is now extremely hard to follow the thread of what the procedure is trying to do, and I can't see any reuse in the table valued functions that have been created. In addition the stored procedure's execution time is now 50% longer (measured after 200 executions).
Does anyone have any advice on when a stored procedure should be split up? Do people generally try to fragment their stored procedures in this way? and, does anyone have any comments on the increased execution time and whether this should be of concern?
Thank you,
Paul.
-
Let us suppose that your procedure inserts records into a customer table, and order table and an order item table.
You could have a single procedure to do this, but there could be cases where a customer comes back asking you to add extra items onto their order. Your single procedure won't do this, unless you include huge convoluted logic.
It would make sense to have an addCustomer, addSales and addSalesItem procedure.
I tend to write a set of small procedures for precisely this reason. The procedures are used in different contexts. If this doesn't apply to you then stick with your great big lump.
I also find that testing small stored procedures makes it far easier to spot bugs because the small sp's can be tested in isolation.
I would investigate why your execution times have increased. Is it that a task within your large sp has to be rerun several times now the procedure is split out?
-
David.Poole is right. SQL Sprocs should follow the same sort of logic applied to any programming language. The decision to break something into a subroutine, or separate sproc, should be based upon how many times you may run a section of code by itself.
To further David's example, there may be times you want to create a customer record without creating a sale. If you had a separate addCustomer procedure, you could execute that to save the customers details. If it's all rolled into one, you would be forced to create an order against the customer (or worse, store the customer somewhere else).
Julian Kuiters
juliankuiters.id.au -
Thank you David and Julian,
I agree that if you have several different tasks to perform then it makes sense to write several different stored procedures, and in some cases some of the stored procedures may call others.
What has happened in this case is that a stored procedure has been broken into a stored procedure that calls another stored procedure which calls seven functions. The stored procedure hasn't been broken up for code reuse, it has primarily been broken up for abstraction in the belief that creating lots of SQL fragments and joining them together is clearer than creating a single SQL statement. The complex SQL statement in question only joins about 5 tables and returns about 12 columns, so it's actually not particularly complex. My feeling is that the fragmentation actually makes the code harder to read and thus maintain.
Splitting code for abstraction is particularly useful in standard procedural programming languages where output of a call is a set of defined output parameters or a return value. With a query language the output of a call may be one or more result sets and a return value; this is not defined in the call itself so must be discovered by either reading the development code or a discription of the call. Unfortunately developers coming from a procedural programming background don't always appreciate this. I don't suppose anyone has any 'best practice' documentation or guidelines out there on the topic that may be useful - I suppose a lot of it comes down to common sense?
I agree with David that the longer execution time of the fragmented stored procedure is strange. I can further examine the profiling, but at this stage I can only put it down to the extra overhead of the function and stored procedure calls. I have already taken the fragmented stored procedure and joined it back together to confirm that it's the same as the original (which it is), I'll just have to perform further testing.
Thank you again,
Paul.
-
if you should split for reusability,
and you cannot see any reusable parts,
what's left to be split ??
Choose your means wizely.
There is only one MUST is any rdbms and that is "you MUST test", all the rest are guidelines.
Don't use a technique you just because it looks good.
IMO reusability should be aimed for, but maintainability still has to have top-priority !
So, when making some split-decisions, document it.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply