Post reply

Two questions in mind (for genralized approach)

  • May 20, 2011 at 9:40 am

    #1327771

    muthukkumaran (5/20/2011)

    Ninja's_RGR'us (5/20/2011)

    GilaMonster (5/20/2011)

    Ninja's_RGR'us (5/20/2011)

    I don't know why but I was under the impression that there was a free ebook version of 1 on his books.

    There is. See the Books item in the menu to the left.

    Thanks, both of ya.

    And no gail doesn't have multiple personalities. :w00t:

    😀

    Ninja may i know you really name.

    Yes but I'd have to kill you next. :hehe:

  • May 20, 2011 at 11:53 pm

    #1328070

    🙂 hmmm

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • May 21, 2011 at 1:38 am

    #1328074

    Don't need to kill anyone guys.... 😉

    We are already short of SQL experts.....

  • May 21, 2011 at 11:08 am

    #1328116

    Ninja's_RGR'us (5/18/2011)

    Select into could cause recompiles and slow you down.

    True enough and I do realize you said "could" but just to be sure... The blinding speed of a SELECT/INTO (even if it doesn't qualify as "minimally logged) will frequently make up for the recompile time and the recompile may be just what the doctor ordered for additional speed in other parts of the query. An example of this is that one of the "fixes" for a bad execution plan when a Table Variable is involved is to do a statement level recompile so that SQL Server knows that there's more than 1 row in the Table Variable.

    In the batch world, recompiles can be comparatively insignificant hits on when compared to performance gains they bring to large scale stored procedures.

    --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 24, 2011 at 8:12 am

    #1329089

    Unfortunately that brings me to the same point where I started. In a complex stored procedure, what should be the thumb rule for implementing temporary data [records upto 800].

  • May 24, 2011 at 8:18 am

    #1329092

    sqlnaive (5/24/2011)

    Unfortunately that brings me to the same point where I started. In a complex stored procedure, what should be the thumb rule for implementing temporary data [records upto 800].

    I personnally start with temp table and insert into. Never had issues with that (which is not the case the table variable).

    So to sum up. it DEPENDS!.

  • May 24, 2011 at 10:21 am

    #1329217

    sqlnaive (5/24/2011)

    Unfortunately that brings me to the same point where I started. In a complex stored procedure, what should be the thumb rule for implementing temporary data [records upto 800].

    That's the whole point... unless you're really sensitive to how easity it should be to troublshoot and repair in the future, there is no thumb rule. Both have advantages and disadvantages which should be evalutated for each use.

    --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 3, 2011 at 10:55 am

    #1333815

    Jeff Moden (5/24/2011)

    sqlnaive (5/24/2011)

    Unfortunately that brings me to the same point where I started. In a complex stored procedure, what should be the thumb rule for implementing temporary data [records upto 800].

    That's the whole point... unless you're really sensitive to how easity it should be to troublshoot and repair in the future, there is no thumb rule. Both have advantages and disadvantages which should be evalutated for each use.

    With some notable exceptions, I've found that SELECT INTO is usually much faster than CREATE TABLE/INSERT INTO when dealing with temp tables. I once bench marked both in a stored procedure that created a report record set for box office totals by country and film release in the movie industry. The CREATE TABLE/INSERT INTO ran from 1.2 to 1.7 seconds each time. The SELECT INTO ran right around 300 ms each time. I've bench marked other procedures and found somewhat similar, if not quite so dramatic, differences.

    As far as indexing a temp table I've found that the overhead of creating the index is more than the index gives you back in performance if the number of rows in the temp table is just a few hundred. More than that and the index can give better performance.

    Todd Fifield

  • June 3, 2011 at 4:06 pm

    #1333969

    tfifield (6/3/2011)

    Jeff Moden (5/24/2011)

    sqlnaive (5/24/2011)

    Unfortunately that brings me to the same point where I started. In a complex stored procedure, what should be the thumb rule for implementing temporary data [records upto 800].

    That's the whole point... unless you're really sensitive to how easity it should be to troublshoot and repair in the future, there is no thumb rule. Both have advantages and disadvantages which should be evalutated for each use.

    With some notable exceptions, I've found that SELECT INTO is usually much faster than CREATE TABLE/INSERT INTO when dealing with temp tables. I once bench marked both in a stored procedure that created a report record set for box office totals by country and film release in the movie industry. The CREATE TABLE/INSERT INTO ran from 1.2 to 1.7 seconds each time. The SELECT INTO ran right around 300 ms each time. I've bench marked other procedures and found somewhat similar, if not quite so dramatic, differences.

    As far as indexing a temp table I've found that the overhead of creating the index is more than the index gives you back in performance if the number of rows in the temp table is just a few hundred. More than that and the index can give better performance.

    Todd Fifield

    I've found the same thing to be true so often that I don't even bother with Table Variables anymore. I just didn't want to sound like a Temp Table Zealot and wanted to give the OP the opportunity to make a decision based on his own world. If I could use Temp Tables in UDF's, I'd likely never use table variables. SELECT/INTO is one of my best friends for the very reasons you state.

    --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 3, 2011 at 5:39 pm

    #1333989

    sqlnaive (5/24/2011)

    Unfortunately that brings me to the same point where I started. In a complex stored procedure, what should be the thumb rule for implementing temporary data [records upto 800].

    The same rule of thumb that always applies: It depends...:-D

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 10 posts - 16 through 24 (of 24 total)

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