SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Joe Celko / Stairway to Database Design - STEP 5: Procedures / Latest Posts

RE: Stairway to Database Design - STEP 5: Procedures

hakim.ali — Wed, 11 May 2011 08:27:55 GMT

"The rules of thumb for T-SQL are not to write any procedure over 50 lines"Really? Doesn't this depend on the situation, on the application being developed? There are so many arguments for coding business logic in stored procedures to take advantage of query optimization, allow more security, reduce network traffic etc, not to speak of the power of the database server to process large volumes of data instead of passing them on to another layer. I have worked at places where very complex business rules were very well handled in T-SQL stored procedures running many thousands of lines long. I have coded such procedures, and it would be a nightmare to pass all that data to another layer to process. Can't agree with this statement.Hakim Ali

RE: Stairway to Database Design - STEP 5: Procedures

RichB — Tue, 31 Aug 2010 04:55:45 GMT

I agree with you David - ObjectVerb all the way. In my case its 3,500 - of which about 2000 are Get.... SSMS is a bit rubbish for filtering that, and code completion just blows.

RE: Stairway to Database Design - STEP 5: Procedures

david_wendelken — Thu, 26 Aug 2010 14:45:12 GMT

Hi Joe,I had the pleasure of meeting you in Atlanta more years ago than either of us can remember well. I disagree with your naming convention on procedures of {Verb}{Object}.It does not scale well in a large system using the default behaviour of most IDEs.You end up with 100 Add{Object} procedures followed by 100 Get{Object} followed by 99 Update{Object} procedures.Very rarely do I ask myself, "How many things can I Update?" More often, I ask myself, "Now that I have to work on this {Object}, what can I do with it?"I prefer a naming convention of {Object}{Verb}.That way, all the things that I can do with a given {Object} naturally sort together in the IDE.Oracle Packages (may the MS development team that adds packages to T-SQL be blessed all the days of their lives) are a great way to achieve this logical grouping while also reducing the number of items in the list in the IDE.

RE: Stairway to Database Design - STEP 5: Procedures

jcrawf02 — Thu, 26 Aug 2010 07:43:42 GMT

[quote][b]DEK46656 (8/25/2010)[/b][hr][quote][b]pschmidt 87697 (8/25/2010)[/b][hr]I think you may have missed the point I was trying to make. Regardless of if the client/app iteration is the same or if the batch gets submitted in one step, it [b]DOES[/b] make difference if you make 25 singleton operations or build a 25 id CSV. If you take the time to compare the two concepts, one call with a CSV parameter will win over singleton operations for speed especially if there are a large amount of values in the CSV.I don't mind the idea of a staging table. It would work very well for some situations.[/quote]I don’t do much with the "web to database" side of things, so my experience is a little weak here, however, from what little I have done in it...The dump of data between a database table (or equivalent) and a web page typically has some data structure that passes between the two. The selection / de-selection of a message ID could update a staging table as you go. Admittedly that could seem a little bit abusive on active server type execution (some event driven activity driving the single parameter procedure listed earlier). However, it does provide "state" to an environment that is inherently stateless. That could be considered a requirement to maintain accurate data integrity for a "process" of presenting the messages to the end user.One of the reasons that I've changed my approach to database design comes from recognition of the logic behind why Codd (and ANSI) put the rules in place. If you consider why the rules were defined the way they were, you find that they prevent the ability of the problem to occur to begin with.Consider the CSV parameter you are working with. Have you defined a limit to the number that may be passed to the procedure at one time? You are probably limited to an 8000 character limit, but "in theory" you could go over that.Can that number (of parameters) be over-ridden, generating something equivalent to a "buffer overflow"? Also, a CSV is inherently a string, though you are passing in an array of integers (potential data type issues). Could this also (potentially) open up the procedure to a "SQL injection" attack?Now consider the idea of a single parameter passing an integer at a time. It helps prevent the injection attack because if it's not an integer, its fails the procedure call. The procedure could also perform testing to confirm that it is valid in reference to the user asking for it (if you have a 2 parameter procedure that includes the user/owner of the message ID being passed). A lot of business rules can be enforce very easily if you accept a parameter at a time.One of the realizations I've had in my attempts to follow rules like this (you could call them best practices, but really these fall back to Codd and ANSI efforts) is that they might (seem to) increase the size of the code, or increase the layers of complexity. However, they always seem to increase the integrity, and reliability, of the system, as well as its ability to adapt to change.[/quote]Ok, but how would they (in this case) affect performance?

RE: Stairway to Database Design - STEP 5: Procedures

DEK46656 — Wed, 25 Aug 2010 13:30:25 GMT

[quote][b]pschmidt 87697 (8/25/2010)[/b][hr]I think you may have missed the point I was trying to make. Regardless of if the client/app iteration is the same or if the batch gets submitted in one step, it [b]DOES[/b] make difference if you make 25 singleton operations or build a 25 id CSV. If you take the time to compare the two concepts, one call with a CSV parameter will win over singleton operations for speed especially if there are a large amount of values in the CSV.I don't mind the idea of a staging table. It would work very well for some situations.[/quote]I don’t do much with the "web to database" side of things, so my experience is a little weak here, however, from what little I have done in it...The dump of data between a database table (or equivalent) and a web page typically has some data structure that passes between the two. The selection / de-selection of a message ID could update a staging table as you go. Admittedly that could seem a little bit abusive on active server type execution (some event driven activity driving the single parameter procedure listed earlier). However, it does provide "state" to an environment that is inherently stateless. That could be considered a requirement to maintain accurate data integrity for a "process" of presenting the messages to the end user.One of the reasons that I've changed my approach to database design comes from recognition of the logic behind why Codd (and ANSI) put the rules in place. If you consider why the rules were defined the way they were, you find that they prevent the ability of the problem to occur to begin with.Consider the CSV parameter you are working with. Have you defined a limit to the number that may be passed to the procedure at one time? You are probably limited to an 8000 character limit, but "in theory" you could go over that.Can that number (of parameters) be over-ridden, generating something equivalent to a "buffer overflow"? Also, a CSV is inherently a string, though you are passing in an array of integers (potential data type issues). Could this also (potentially) open up the procedure to a "SQL injection" attack?Now consider the idea of a single parameter passing an integer at a time. It helps prevent the injection attack because if it's not an integer, its fails the procedure call. The procedure could also perform testing to confirm that it is valid in reference to the user asking for it (if you have a 2 parameter procedure that includes the user/owner of the message ID being passed). A lot of business rules can be enforce very easily if you accept a parameter at a time.One of the realizations I've had in my attempts to follow rules like this (you could call them best practices, but really these fall back to Codd and ANSI efforts) is that they might (seem to) increase the size of the code, or increase the layers of complexity. However, they always seem to increase the integrity, and reliability, of the system, as well as its ability to adapt to change.

RE: Stairway to Database Design - STEP 5: Procedures

pschmidt 87697 — Wed, 25 Aug 2010 12:40:39 GMT

[quote][b]Mike Dougherty-384281 (8/25/2010)[/b][hr]I'm not using a language that abstracts away the heavy lifting of the conversation with the SQL server, so it makes no difference to me whether I construct a command batch with 25 singleton operations or build a 25 id CSV - the client/app iteration is the same. The batch gets submitted in one step either way. I like the DIY approach but your tools (language, paradigm, etc.) might not provide the same flexibility.[/quote]I think you may have missed the point I was trying to make. Regardless of if the client/app iteration is the same or if the batch gets submitted in one step, it [b]DOES[/b] make difference if you make 25 singleton operations or build a 25 id CSV. If you take the time to compare the two concepts, one call with a CSV parameter will win over singleton operations for speed especially if there are a large amount of values in the CSV.I don't mind the idea of a staging table. It would work very well for some situations.

RE: Stairway to Database Design - STEP 5: Procedures

Mike Dougherty-384281 — Wed, 25 Aug 2010 10:20:22 GMT

instead of: verbNouns 1,2,3or:verbNoun 1verbNoun 2verbNoun 3you could do: ("A" is a key for the stage)stageVerbNoun 1, AstageVerbNoun 2, AstageVerbNoun 3, AverbStagedNoun AThe benefit of the staged&keyed solution is that you've decoupled the preparation of the staging from the commitment of work. This scales well to allow many concurrent users to add to a batch of work yet defer the batch to an off-peak process. Perhaps email is a bad example to illustrate the value of this approach. One example we use with this technique is to hook operations on data via a trigger that inserts the PK into an audit table for later examination. The examination is too costly to perform inside the trigger. The candidate list is much easier to retrieve than it would be to find a "needs audit" field in a multi-million-row table. After the audit is complete the table rows remain to account when the audit was performed.I'm not using a language that abstracts away the heavy lifting of the conversation with the SQL server, so it makes no difference to me whether I construct a command batch with 25 singleton operations or build a 25 id CSV - the client/app iteration is the same. The batch gets submitted in one step either way. I like the DIY approach but your tools (language, paradigm, etc.) might not provide the same flexibility.Loosely coupled modules are also much easier to modify in project maintenance too. When I've had the pleasure of working on a properly encapsulated and loosely coupled code it reminds me why I love programming. However, "properly encapsulated" and "loosely coupled" code also suggest that the original author was so good that I won't have much opportunity (or reason) to do maintenance on their work. :)

RE: Stairway to Database Design - STEP 5: Procedures

pschmidt 87697 — Wed, 25 Aug 2010 09:36:02 GMT

[quote][b]DEK46656 (8/25/2010)[/b][hr][quote][b]pschmidt 87697 (8/25/2010)[/b][hr]For instance, if you have a web based email system that lists emails gives the user the option to select the emails they want to delete and then click a delete button, you can simply pass in one call the all the ids of the emails from the UI in a delimited string to one stored proc. Example: "proc_delete_emails @userid = 1, @email_ids = '2,5,8,9,23,46,57,89,99,100,110,112,113,115,117,119,122,124,127,129'"[/quote]I would suggest that the selection of emails be stored as a set, into some form of staging table containing the email ID and the user / session. Then the stored procedure would be called, passing as a parameter the user / session. The procedure would then execute the delete on the emails associated to that user.I've used CSV in the past, but have been moving away from it. I've been trying to improve my data structures and code. CSV can be considered "against the rules" as defined by Codd (Rule 1 I believe).[/quote]I see where you're going with this, but to get the list of email_ids into a staging table from the applications data access layer without using a CSV parameter would still take 20 stored procedure calls to insert them into the staging table. Would I not be right back where I started from as far as not wanting to make 20 calls to the database? In the procedures I have right now, I take the CSV values and parse them into a table variable. This takes the place of the staging table you suggested. Then I delete based on a join to the table variable.

RE: Stairway to Database Design - STEP 5: Procedures

SQLRNNR — Wed, 25 Aug 2010 09:07:18 GMT

[quote]The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) and not to use PRINT.[/quote]I find that to be a tough and lofty goal.Otherwise, interesting article / series. I will be looking for the remaining articles.

RE: Stairway to Database Design - STEP 5: Procedures

DEK46656 — Wed, 25 Aug 2010 08:28:42 GMT

[quote]The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) and not to use PRINT.[/quote]Joe, can you elaborate on this? I do a lot of work in data warehousing, and end up with ETL stored procedures that run into a couple of hundred lines easily. I typically start with a template that I created, which contains logical sections; test parameters, check existing data, logging activity, and error handling. I then add code to it to address the specifics related to the table I'm loading (facts), along with any dependant tables (dimensions).BTW: I do have separate procedures to address the dependant tables, so I have reasonable degree of cohesion (in that regard).

RE: Stairway to Database Design - STEP 5: Procedures

DEK46656 — Wed, 25 Aug 2010 08:17:28 GMT

[quote][b]pschmidt 87697 (8/25/2010)[/b][hr]For instance, if you have a web based email system that lists emails gives the user the option to select the emails they want to delete and then click a delete button, you can simply pass in one call the all the ids of the emails from the UI in a delimited string to one stored proc. Example: "proc_delete_emails @userid = 1, @email_ids = '2,5,8,9,23,46,57,89,99,100,110,112,113,115,117,119,122,124,127,129'"[/quote]I would suggest that the selection of emails be stored as a set, into some form of staging table containing the email ID and the user / session. Then the stored procedure would be called, passing as a parameter the user / session. The procedure would then execute the delete on the emails associated to that user.I've used CSV in the past, but have been moving away from it. I've been trying to improve my data structures and code. CSV can be considered "against the rules" as defined by Codd (Rule 1 I believe).

RE: Stairway to Database Design - STEP 5: Procedures

Mike Palecek — Wed, 25 Aug 2010 06:27:26 GMT

[quote]"You will see new SQL programmers try to pass arguments as XML or as CSV (Comma Separated Values) list strings. They have added the overhead of a parser (the XML parser or whatever code they wrote for splitting up the CSV string) and endangered their data integrity. SQL Server can handle over 2,000 arguments, which is more than enough power for any realistic situation."[/quote]I think there are some situations where this is more appropriate. Since SQL Server does not support an array as a parameter or a parameter array XML or a CSV string is an easy way to get an unknown number of items into a stored procedure.An example of something I recently worked on, taking a bill of materials from a web site and return a built up cost for the finished part. I do not know the number of parts that will be used when I am writing the procedure, that is only known at runtime. Using XML I am able to build a table variable and do a join to a cost table, no matter how many items are used.

RE: Stairway to Database Design - STEP 5: Procedures

pschmidt 87697 — Wed, 25 Aug 2010 05:44:30 GMT

[i]"You will see new SQL programmers try to pass arguments as XML or as CSV (Comma Separated Values) list strings. They have added the overhead of a parser (the XML parser or whatever code they wrote for splitting up the CSV string) and endangered their data integrity. SQL Server can handle over 2,000 arguments, which is more than enough power for any realistic situation."[/i]Please elaborate on this. I use CSV parameters all the time for web based application to cut down on the number of times the procedure has to be called. For instance, if you have a web based email system that lists emails gives the user the option to select the emails they want to delete and then click a delete button, you can simply pass in one call the all the ids of the emails from the UI in a delimited string to one stored proc. Example: "proc_delete_emails @userid = 1, @email_ids = '2,5,8,9,23,46,57,89,99,100,110,112,113,115,117,119,122,124,127,129'"I find that the overhead of SQL parsing through the CSV string to be far lower than the overhead of having the data access layer call the stored procedure 20 times.How does this "endanger" my data integrity?

RE: Stairway to Database Design - STEP 5: Procedures

andreas.eriksson — Wed, 25 Aug 2010 00:55:02 GMT

I think the example of named parameters is wrong.EXEC Test @in_test_date = 789; -- returns (2010-01-01, 789.00000)Should it not be @in_test_decimal ?

Stairway to Database Design - STEP 5: Procedures

CELKO — Tue, 24 Aug 2010 22:41:27 GMT

Comments posted to this topic are about the item [B]Stairway to Database Design - STEP 5: Procedures[/B]