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 Wednesday, August 25, 2010 1:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:38 AM
Points: 377, Visits: 432
pschmidt 87697 (8/25/2010)

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.

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.


Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
Post #975137
Posted Thursday, August 26, 2010 7:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 1,326, Visits: 19,275
DEK46656 (8/25/2010)
pschmidt 87697 (8/25/2010)

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.

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.
Ok, but how would they (in this case) affect performance?


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #975638
Posted Thursday, August 26, 2010 2:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 19, 2013 2:00 PM
Points: 183, Visits: 479
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.
Post #975951
Posted Tuesday, August 31, 2010 4:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:01 AM
Points: 1,070, Visits: 906
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.


Post #977824
Posted Wednesday, May 11, 2011 8:27 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:36 AM
Points: 537, Visits: 808
"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


Hakim Ali
www.sqlzen.com
Post #1107001
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse