Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to Database Design - STEP 5: Procedures Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2010 10:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Comments posted to this topic are about the item Stairway to Database Design - STEP 5: Procedures

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #974621
Posted Wednesday, August 25, 2010 12:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 5:16 AM
Points: 2, Visits: 24
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 ?

Post #974658
Posted Wednesday, August 25, 2010 5:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:13 AM
Points: 3, Visits: 123
"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."

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?
Post #974762
Posted Wednesday, August 25, 2010 6:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:26 AM
Points: 845, Visits: 713
"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 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.
Post #974797
Posted Wednesday, August 25, 2010 8:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 20, 2014 9:41 AM
Points: 353, Visits: 423
pschmidt 87697 (8/25/2010)
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 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).


Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
Post #974886
Posted Wednesday, August 25, 2010 8:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 20, 2014 9:41 AM
Points: 353, Visits: 423
The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) and not to use PRINT.

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).


Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
Post #974891
Posted Wednesday, August 25, 2010 9:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 20,467, Visits: 14,102
The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) and not to use PRINT.


I find that to be a tough and lofty goal.

Otherwise, interesting article / series. I will be looking for the remaining articles.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #974933
Posted Wednesday, August 25, 2010 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:13 AM
Points: 3, Visits: 123
DEK46656 (8/25/2010)
pschmidt 87697 (8/25/2010)
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 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).


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.
Post #974976
Posted Wednesday, August 25, 2010 10:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:13 AM
Points: 257, Visits: 901
instead of:
verbNouns 1,2,3
or:
verbNoun 1
verbNoun 2
verbNoun 3
you could do: ("A" is a key for the stage)
stageVerbNoun 1, A
stageVerbNoun 2, A
stageVerbNoun 3, A
verbStagedNoun A

The 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. :)
Post #975010
Posted Wednesday, August 25, 2010 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:13 AM
Points: 3, Visits: 123
Mike Dougherty-384281 (8/25/2010)


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.


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 DOES 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.
Post #975105
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse