Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-16 : 09:55:30
|
| RH writes "We're developing an ERP application on MS SQL2K server and decided that almost all business rules should reside on the server in the form of various objects (mainly PROCS).As the application grows so does the quantity of procedures.My question is:What is better or faster; have many procedures in which the SQL is separated and have the programmers call them separately or have fewer procedures in which the SQL is separated with input parameters and IF clauses?Thanks in advance." |
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2002-01-16 : 10:10:04
|
| Based on what you said, I'd say having fewer stored procedures with the SQL separated by IF statements is faster. One of the advantages of stored procedures is you can run several SQL statements with one database call. |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-16 : 10:35:22
|
| I disagree with andre on this. Keeping a stored procedure short ensures that it doesn't take a lot of time to recompile. More importantly, since a short proc will probably reference the smaller number of tables, it is likely to be recompiled less often. In other words, for performance reasons I'd recommend going with many short stored procedures over few long ones. Edited by - izaltsman on 01/16/2002 10:36:24 |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-01-16 : 16:43:35
|
| Oh gawde keep them short and sweet. I once had this quirky idea to use TSP's (TABLE MANIPULATOR PROCEDURES) which basically handled everything that has to be done to a certain table (insert, update, delete, basic select) this was a terrible idea. It made one procedure I could call with different params but the execution was a lot slower than all the ones I now write (one for insert, update, delete, etc) which allows the compiler to get a better idea of what to cache the procedure. I started with 12 tables a year ago and now have close to 80 and about 200 stored procedures and have recently nurished a love/hate relationship with functions in sql 2000 in result I have about 80 functions as well. functions are wonderful for manipulating data and sometimes for creating table functions. ya you'll have a lot but you'll know EXACTLY where to go for a procedure and won't have to worry about odd side effects that spawn due to a change in one proc that could have affected another part of it without your knowledge... Phew... hope that's convincing enough for ya ;-)- Onamuji |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-16 : 18:21:11
|
| I agree that keeping SPs small and to the point is better than making larger, complex ones. Don't forget that you can call a procedure from another procedure, so if you do have a big process you can always break it into smaller individual SPs and then make a "master" SP that calls each step needed. A lot of data loading and translating SPs are done this way. This also makes it a lot easier to run a modified sequence; simply call the individual sprocs instead of the master. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-16 : 18:57:01
|
I'd like to back up Rob's feelings...Lots of little procs that can be nested are the way to go....The power of the @@NestLevel function makes this a great solution..eg.An SP that cleans out data and returns a set containing the types and number of data deleted...("Cleaning")Another proc that moves data and returns a set containing the types and number of records affected...("Moving")This "Moving" proc also calls the "Cleaning" Proc to clean it out... Now I don't want the result set from the "cleaning" SP if I run the "Moving" SP.. In the "Cleaning" Proc... --If Not called by another proc.. return setIF @@NestLevel = 1Select blah blah..... A bit of planning goes a long way....DavidMTomorrow is the same day as Today was the day before. |
|
|
|
|
|
|
|
|
Stored procedures