Post reply

Temporary Stored Procedures

  • June 27, 2013 at 4:49 pm

    #1628162

    Mr.DiGi (6/27/2013)

    Well, this is dafuq of day... :w00t:

    How so?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 27, 2013 at 4:57 pm

    #1628164

    Solomon Rutzky (6/27/2013)

    Global Temporary Stored Procedures are very helpful when testing updated code in Production, prior to releasing that code. Assuming that it is SELECT only, you can do A / B testing in Production (where ideally we cannot just create objects willy-nilly) to make sure performance and/or logic fixes actually work with the real data set(s). By using Global Temp Procs you can easily open multiple sessions and have one tab for the CREATE / ALTER ##Proc (for easy edits to the code while testing), one tab for running the ##Proc, and another tab for running the current Production code.

    By jove, I believe you've found a good use for TSPs, Solomon. Frequently, Development and QA environments don't have the same volume of data that Production has. If you're careful to make sure there are no server-killing accidental many-to-many joins as well as taking other common sense precautions associated with "developing or testing in production", this is a great way to do a little testing and performance checking prior to a release. I'd strongly recommend that Developers make sure the DBA knows so that (s)he can quickly respond if one of the TSPs under test drag the server down but this sounds like the only good use for TSPs. Thanks for the post!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 1, 2013 at 9:03 am

    #1628742

    Jeff Moden (6/27/2013)

    theboyholty (6/27/2013)

    That code runs like lighting.

    Maybe compared to the original code but with all of the RBAR you have in the code you posted, "lightning" isn't the word I was thinking of.

    Now if you look carefully, Jeff, you'll see that I said the code runs like 'lighting', no mention of lightning. I was referring to those strip lights that can take about a minute to get going.

  • July 19, 2013 at 5:08 am

    #1633935

    Solomon Rutzky (6/27/2013)

    cschlieve (6/25/2013)

    How would you use them. I cannot think of a case where this would be useful?

    Global Temporary Stored Procedures are very helpful when testing updated code in Production, prior to releasing that code. Assuming that it is SELECT only, you can do A / B testing in Production (where ideally we cannot just create objects willy-nilly) to make sure performance and/or logic fixes actually work with the real data set(s). By using Global Temp Procs you can easily open multiple sessions and have one tab for the CREATE / ALTER ##Proc (for easy edits to the code while testing), one tab for running the ##Proc, and another tab for running the current Production code.

    Take care,

    Solomon...

    Edit:

    I forgot to mention that in order to get a true apples-to-apples comparison on new vs current code, you also need to make a copy of the current code as a Global Temporary Stored Procedure. You now have two Global Temp Procs to EXEC and the execution of the "current" code temp proc will neither impact, nor be impacted by, the actual current code proc (i.e. cached query plan).

    since hearing about Temporary Procs, i too was having trouble working out why you would use them.

    This is the best example that i've seen that describes a reason to use them

  • May 8, 2015 at 7:24 am

    #1796562

    Solomon Rutzky (6/27/2013)

    cschlieve (6/25/2013)

    How would you use them. I cannot think of a case where this would be useful?

    Global Temporary Stored Procedures are very helpful when testing updated code in Production, prior to releasing that code. Assuming that it is SELECT only, you can do A / B testing in Production (where ideally we cannot just create objects willy-nilly) to make sure performance and/or logic fixes actually work with the real data set(s). By using Global Temp Procs you can easily open multiple sessions and have one tab for the CREATE / ALTER ##Proc (for easy edits to the code while testing), one tab for running the ##Proc, and another tab for running the current Production code.

    Take care,

    Solomon...

    Edit:

    I forgot to mention that in order to get a true apples-to-apples comparison on new vs current code, you also need to make a copy of the current code as a Global Temporary Stored Procedure. You now have two Global Temp Procs to EXEC and the execution of the "current" code temp proc will neither impact, nor be impacted by, the actual current code proc (i.e. cached query plan).

    Well, it has been a couple of years since this topic appeared. Since then Kendra Little posted this nice little video regarding the performance of a stored procedure vs. a query parameterized with local variables: http://www.brentozar.com/archive/2015/03/local-variable-vs-stored-procedure/

    [/url]

    The conclusion is that if you want to see a "real" query plan using production database statistics without deploying the procedure to production then a temp procedure is the only way to go. You can't use a query parameterized with variables. This gives further weight to Solomon's point made waaay back in 2013.

    Curt

  • May 8, 2015 at 7:39 am

    #1796575

    Interesting article. I learned something new this morning.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 8, 2015 at 8:45 am

    #1796602

    Excellent Post,

    We are going to consider it in our SSIS development group.

    Hank Freeman
    Senior SQL Server DBA / Data & Solutions Architect
    hfreeman@msn.com
    678-414-0090 (Personal Cell)

  • May 8, 2015 at 9:09 am

    #1796615

    @JeffM:

    I can give you an example but I'll put a disclaimer right up front: it's not a great example or even a great reason to do it, but here's my sad story (I'll keep it short)

    Once upon a time I set up a really cool inventory/SQL Server data gathering tool that helped me keep track of all my server installations. It worked great but the pivotal component was a "DBA" database on each of the monitored servers. Everything was driven by a handful of stored procedures and a SQL Agent job. The information was timely, complete, completely controllable and I built up a fan base of the resulting reports. Life was good.

    Then we installed a large number of servers dedicated to a particular product. "Can you run your inventory stuff on the new servers?" management smiled and asked. "Sure!" I replied confidently. "Just let me set up my DBA database on each one and we'll start getting repo..."

    You'd think I'd suggested switching to Oracle. Management stopped smiling and suddenly I was staring down a very angry mob.

    "You can NOT put any non-product-related database on these servers!!!" (Note the three exclamation points)

    So.

    I had 'known good' stored procedures to do everything I needed to do, but needed a database to put them in. Hmmm. TempDB is a database, right?

    Yeah, setting it up was a bit of a pain in the elbow (hey, it's a body part and this is a "G" rated site, right?) but the job creates the procs in tempdb, runs them, stores the data in a global temp table and the procs go away. Pay no attention to the man behind the curtain, kids.

    The moral of the story is: I'm a DBA. I don't like to reinvent the wheel and I cheat. Call it ...creativity...

  • May 8, 2015 at 9:53 am

    #1796633

    I use temp procedures for debugging purposes (for reports only, they don't modify data) at our customer sites. I just copy the object definition, modify the name, and I'm ready to go. I can add "diagnostic" queries, PRINT statements, GOTOs to branch around sections of no interest, etc.

    Besides ad hoc modifications, another major benefit is I don't worry about accidentally dropping or replacing their compiled version, and when I'm done I leave no objects behind.

    ~ Jeff

  • May 9, 2015 at 3:54 pm

    #1796821

    One of many SQL Servers features that is hardly ever used because alternative methods are more suitable.

  • May 9, 2015 at 4:10 pm

    #1796822

    sanjarani (5/9/2015)

    One of many SQL Servers features that is hardly ever used because alternative methods are more suitable.

    Please explain your "alternative methods" and how they are "more suitable".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 10, 2015 at 1:37 am

    #1796833

    Jeff Moden (6/27/2013)

    This does an "old fashioned unpivot" of the data (CBAC but still better than RBAR)

    Could you please explain the CBAC acronym used in this context? Thanks in advance!

  • May 10, 2015 at 8:06 am

    #1796842

    radek.celuch (5/10/2015)

    Jeff Moden (6/27/2013)

    This does an "old fashioned unpivot" of the data (CBAC but still better than RBAR)

    Could you please explain the CBAC acronym used in this context? Thanks in advance!

    Apologies... "Column By Agonizing Column".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 10, 2015 at 8:25 am

    #1796843

    Your Name Here (5/8/2015)

    @JeffM:

    I can give you an example but I'll put a disclaimer right up front: it's not a great example or even a great reason to do it, but here's my sad story (I'll keep it short)

    Once upon a time I set up a really cool inventory/SQL Server data gathering tool that helped me keep track of all my server installations. It worked great but the pivotal component was a "DBA" database on each of the monitored servers. Everything was driven by a handful of stored procedures and a SQL Agent job. The information was timely, complete, completely controllable and I built up a fan base of the resulting reports. Life was good.

    Then we installed a large number of servers dedicated to a particular product. "Can you run your inventory stuff on the new servers?" management smiled and asked. "Sure!" I replied confidently. "Just let me set up my DBA database on each one and we'll start getting repo..."

    You'd think I'd suggested switching to Oracle. Management stopped smiling and suddenly I was staring down a very angry mob.

    "You can NOT put any non-product-related database on these servers!!!" (Note the three exclamation points)

    So.

    I had 'known good' stored procedures to do everything I needed to do, but needed a database to put them in. Hmmm. TempDB is a database, right?

    Yeah, setting it up was a bit of a pain in the elbow (hey, it's a body part and this is a "G" rated site, right?) but the job creates the procs in tempdb, runs them, stores the data in a global temp table and the procs go away. Pay no attention to the man behind the curtain, kids.

    The moral of the story is: I'm a DBA. I don't like to reinvent the wheel and I cheat. Call it ...creativity...

    Heh... Awesome. I absolutely love it. Great story of thinking outside the box. Thanks for that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 11, 2015 at 11:38 am

    #1797049

    Didn't know about this. Thank you.

Viewing 15 posts - 46 through 60 (of 62 total)

You must be logged in to reply to this topic. Login to reply