June 2, 2008 at 2:27 pm
Quick question relating to avoiding mixing of DDL and DML, if I'm DECLAREing or SETing variables, which realm am I in? Seems like it should follow table CREATE / INSERT rules, which would put DECLARE in DDL and SET in DML.
True?
- of course the fact that it makes sense to me almost guarantees the opposite . . .:hehe:
Thx.
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
June 2, 2008 at 2:50 pm
Actually, I believe that DECLARE and SET are both part of the procedural language.
You could think of them as DDL & DML though, if that is helpful.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 3, 2008 at 4:50 am
If I'm guessing right, you're trying to optimize for stored procedure recompiles.
If that is the case these aren't DDL (ie they don't CUD database objects).
Generally I've seen all DDL at the top of a sproc and then the rest of the DML and procedural stuff below.
Some people I've worked with think it's best to declare your variables at the top of the proc with your temp tables, but that's more of a stylistic thing, IMO than a performance optimizer
June 3, 2008 at 6:33 am
Thanks guys, appreciate the clarification. That's exactly what I was looking for.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
June 3, 2008 at 10:07 am
James Raddock (6/3/2008)
... Some people I've worked with think it's best to declare your variables at the top of the proc with your temp tables, but that's more of a stylistic thing, IMO than a performance optimizer
I've read before from authoritative sources that if you're creating temp objects in a proc that it's always best to put them at the top of your code for performance reasons.
(Also, personally, I think it's best from a clear logic POV to have everything declared at top. I try to keep my procs organized that way.)
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply