RE: How big is too big

---------- Robert Wines (Robert_Wines@ama-assn.org) asked: >I know this question may not have any hard rules but I am interested in >any experiences, thoughts or WAGs. I have recently moved to a new >company. This company loves to write complicated long stored >procedures. One such stored procedure is about 10,000 lines long. >Needless to say the SP slices, dices and munches 7 ways to Sunday it >also runs like a sick dog. >My experience has always been to keep a SP simple. I try to keep them >to no more than 100 lines but I have written some that were longer but >none over 500 lines. I don't believe you can get good performance from >a SP that is 10,000 lines longs. That is massive; sounds rather like a semi-converted Orrible shop. Haven't they heard of the new invention in programming known variously as subroutines, functions, procedures, methods, ...? I would refuse to read any function which was 10,000 lines long-they are someone else's problem. What are the alternatives to the SP? Presumably extracting the code into something resembling an I4GL or ESQL/C program. What are the implications of that? The main one is that any data fetched from the database has to be transmitted to the application program, processed, and sent back to the database, whereas in an SP, there is no network traffic (in the simple cases). So, the SP should work faster than the basic procedural application because of that. If the SP is slower than an I4GL program, then someone has seriously screwed up the implementation of SPL - simply because of the overhead of the communications between engine and application should dwarf the processing itself. Yours controversially, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>