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: Yesterday @ 6:20 PM
Points: 1,945, Visits: 3,067
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: Friday, September 12, 2014 1:43 PM
Points: 3, Visits: 138
"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: Friday, October 10, 2014 1:07 PM
Points: 845, Visits: 736
"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: Wednesday, October 15, 2014 7:27 AM
Points: 387, Visits: 435
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: Wednesday, October 15, 2014 7:27 AM
Points: 387, Visits: 435
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 17,812, Visits: 15,739
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
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: Friday, September 12, 2014 1:43 PM
Points: 3, Visits: 138
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: Friday, October 17, 2014 1:59 PM
Points: 262, Visits: 918
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: Friday, September 12, 2014 1:43 PM
Points: 3, Visits: 138
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