Post reply

How do I know when to use temp tables/table variables?

  • December 2, 2010 at 7:03 pm

    #1258181

    Grant Fritchey (12/2/2010)

    Craig Farrell (12/2/2010)

    SQLNW (12/2/2010)

    Thank you Wayne, I am thinking why do we use temp table in the first place? As soon as you see a problem how you decide you need to use a temp table? Is it like when we need to make changes to a record set /values and not affect the actual data in the actual table, and return the results of the temp table..

    Am I right ? and what kind of real life examples justify the usage of temp table. Please let me know. Thanks.

    There's a few places I'll use it.

    1) I have a very small resultant data set from a portion of a query, but the optimizer isn't realizing that quickly enough for my tastes, and is slowing down my query. I'll drop the data to a temp table (where it'll seek for that data properly, to get it), then bring the temp table in with an index or two on it that matches the rest of the involved tables to speed the process.

    2) If I'm doing a quirky/serial update and I need to remap clustered indexing, or I'm dealing with multiple tables to get my result set.

    3) Repeated use of a subquery. Sometimes you need to pull, say, a list of aggregations out of a database and then do a few things with the results. Instead of rerunning the query repeatedly, drop it to a #tmp so you only have to run it once and then constantly re-use the result set.

    Ooh. I like #3. Good idea & well defined.

    Yes indeed. And don't forget that about 99% of CTEs are essentially a predefined subquery, so repeated calls to them qualifies also. (Now a recursive CTE may be different, but still watch out for them!)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 3, 2010 at 7:06 am

    #1258389

    Thank you Craig. As Wayne said, very well defined. thanks.

  • December 3, 2010 at 7:10 am

    #1258392

    Thank you Grant. It was a totally new perspective with oracle/SQL crossover. thanks,

  • December 3, 2010 at 7:14 am

    #1258398

    Thanks so much Wayne for replying. I am reading more about CTEs now. thanks

  • December 5, 2010 at 12:29 pm

    #1258830

    Have a look to this link :

    http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

  • December 5, 2010 at 2:27 pm

    #1258838

    sandippani (12/5/2010)

    Have a look to this link :

    http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx%5B/quote%5D

    However note that the first 'fact' stated in that article is incorrect. Changes to table variables are logged. They don't participate in user transactions, but they're still logged.

    http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

    The second 'fact' is also incorrect in SQL 2005 and later. Use of temp tables does not automatically result in plan that's recompiled (and it certainly does not result in a plan that isn't cached at all). Even if it does, SQL 2005 and above has statement-level recompile and only the affected statements recompile, not the entire procedure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 5, 2010 at 3:36 pm

    #1258847

    GilaMonster (12/5/2010)

    sandippani (12/5/2010)

    Have a look to this link :

    http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx%5B/quote%5D

    However note that the first 'fact' stated in that article is incorrect. Changes to table variables are logged. They don't participate in user transactions, but they're still logged.

    http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

    The second 'fact' is also incorrect in SQL 2005 and later. Use of temp tables does not automatically result in plan that's recompiled (and it certainly does not result in a plan that isn't cached at all). Even if it does, SQL 2005 and above has statement-level recompile and only the affected statements recompile, not the entire procedure.

    I agree. Further and being a bit critical, I just don't trust people in the area of performance savy when they build a test table using a WHILE loop. 😛

    --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)

  • December 5, 2010 at 4:04 pm

    #1258851

    Just as a couple of a side-bars...

    I've found that way too many people worry way too much about when they should use a Temp Table vs a Table Variable. I normally use Temp Tables for just one reason... they're easier to troubleshoot both during development and in the future because their content persists after a run or partial run in SSMS.

    The other thing is that, while a lot of people worry about the differences and possible performance impact between the two, they typically neglect much lower hanging fruit. For example, the fellow that wrote that one article at the link previously cited in this thread... If he'll use a WHILE loop to create a simple test table, he'll use a loop for a heck of a lot more when he shouldn't. In most cases, converting loops to set based code (it's not that hard folks and becomes second nature with just a bit of practice) and making SARGable predicates in joins and WHERE clauses will return much more performance gain than the differences between types of temporary data storage.

    With the idea of maintainability at heart and performance very close on the heels of that (it's usually a dead-heat in my mind), there are only 3 places where I'll use a Table Variable...

    1. If (for some reason) I can't write an iTVF and must resort to an mlTVF, I'll use a table variable and that's only because SQL Server won't let me use a Temp Table in a function. Heh... that's if and only if I can't get away with some higher performance inline code to begin with. 😉

    2. When I don't want a possible rollback to affect my ProcLog system (ie: logging in general).

    3. IF and only IF I've proven that a Temp Table is significantly slower or causes recompiles in some very "high-hit" C.R.U.D. (think thousand of hits in very short periods of times). If I'm doing some batch processing on a couple bazillion rows of data, I actually welcome the occasional recompile so I don't actually have to worry about parameter sniffing and the like. It fact, sometimes THAT works out rather well with high-hit C.R.U.D., as well.

    --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)

  • December 5, 2010 at 9:37 pm

    #1258877

    GilaMonster (12/5/2010)

    sandippani (12/5/2010)

    Have a look to this link :

    http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx%5B/quote%5D

    However note that the first 'fact' stated in that article is incorrect. Changes to table variables are logged. They don't participate in user transactions, but they're still logged.

    http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

    The second 'fact' is also incorrect in SQL 2005 and later. Use of temp tables does not automatically result in plan that's recompiled (and it certainly does not result in a plan that isn't cached at all). Even if it does, SQL 2005 and above has statement-level recompile and only the affected statements recompile, not the entire procedure.

    Two other 'facts' that are wrong

    1. In SQL 2005 and later: table variables CAN be used in an insert/exec statement.

    2. Table variables CAN be used in inner stored procedures or exec statements - IF they are created/populated in those procedures / statements. If the table variable is created in a parent procedure, it won't be visible in those procedures/statements (Note that I think that this is what the author meant, but it was just worded as they aren't visible in them without specifying where it was created which does make a difference.)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 13, 2010 at 7:59 am

    #1261717

    thanks for your reply sandippani.

  • December 13, 2010 at 8:01 am

    #1261718

    hi everyone ,request your help urgently.

    Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure

    thanks for your help.

  • December 13, 2010 at 9:41 am

    #1261797

    Junglee_George (12/2/2010)

    Hi

    Please have a look into this article also

    http://www.mssqltips.com/tip.asp?tip=1556

    Thanks

    Rather a dangerous article with false statements like

    # Table variables can not have Non-Clustered Indexes

    and

    # You can not create constraints in table variables

    which some people might be fooled into believing.

    Tom

  • December 14, 2010 at 7:27 am

    #1262206

    SQLNW (12/13/2010)

    hi everyone ,request your help urgently.

    Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure

    thanks for your help.

    That's a new problem. Please start a new post and include some better information to go on. For example, we can't do a thing to help unless you post the code that created the problem. 😉

    --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)

  • December 14, 2010 at 7:31 am

    #1262210

    Tom.Thomson (12/13/2010)

    Junglee_George (12/2/2010)

    Hi

    Please have a look into this article also

    http://www.mssqltips.com/tip.asp?tip=1556

    Thanks

    Rather a dangerous article with false statements like

    # Table variables can not have Non-Clustered Indexes

    and

    # You can not create constraints in table variables

    which some people might be fooled into believing.

    I'm not sure I'd call them dangerous (by themselves, they won't cause any damage, I think) but they sure are false. Heh... it's proof positive that the internet is STILL a well paved on-ramp to a dirt road. 😉

    --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)

  • December 14, 2010 at 7:32 am

    #1262213

    Jeff Moden (12/14/2010)

    SQLNW (12/13/2010)

    hi everyone ,request your help urgently.

    Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure

    thanks for your help.

    That's a new problem. Please start a new post and include some better information to go on. For example, we can't do a thing to help unless you post the code that created the problem. 😉

    http://www.sqlservercentral.com/Forums/Topic1033821-8-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 39 total)

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