should atemp table be used from looping even when single table is used in a loop

  • hi,

    We have a master table, single table which I loop thought, to do some task, the master table is very rarely changed.

    So while looping through , I put some records in a temp table and do the looping ?

    1. Will it help in any case, or can worsen it in any case? (pls consider change in master is quite less and basically new inserts are there)

    2) and the main question is, should it be good practice to always make a temp table for looping, even if I have one single table to loop through.

    3) pls consider loops in our case are quite small as far as the number of records are concerned and even the master table is also small,

    4) loops are small, but master table can grow to a million.

    yours sincerly

     

     

     

     

  • The textbooks say writing your own loops in relational database code is something to be avoided.  Are you sure it's necessary to write loops?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • There is no single answer to your question as it all "Depends". Without further information, any response will be pure guesswork!

    😎

    To Steve's good point, you are working with set-based data, there should be no need for looping as you have set-based operations available that are much more efficient than any kind of user's loops!

  • rajemessage 14195 wrote:

    hi,

    We have a master table, single table which I loop thought, to do some task, the master table is very rarely changed.

    So while looping through , I put some records in a temp table and do the looping ?

    1. Will it help in any case, or can worsen it in any case? (pls consider change in master is quite less and basically new inserts are there)

    2) and the main question is, should it be good practice to always make a temp table for looping, even if I have one single table to loop through.

    3) pls consider loops in our case are quite small as far as the number of records are concerned and even the master table is also small,

    4) loops are small, but master table can grow to a million.

    yours sincerly

    Without knowing more about the process the you evoke for every iteration, it's impossible to answer this question.

    You also say that "the master table is very rarely changed" in your first breath and and then you say "but master table can grow to a million" in the next.  Which is true?

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

  • well , there must be thumb of rule  , i think that can be answered.

  • well , there must be some thumb of rule  , i think that can be answered.

  • I very much agree with the answers you've already received. SQL isn't meant to be a "looping" language. It's designed to work on the concepts of sets. Yes, there are certainly exceptions, but the general rule of thumb is, avoid loops where you can. That said, I'll take a stab at your specific questions.

    rajemessage 14195 wrote:

    hi,

    We have a master table, single table which I loop thought, to do some task, the master table is very rarely changed.

    So while looping through , I put some records in a temp table and do the looping ?

    1. Will it help in any case, or can worsen it in any case? (pls consider change in master is quite less and basically new inserts are there)

    There is unlikely to be a single definitive answer. Testing is your friend. In addition to trying it with a temporary table, also try it with a table variable. Depending on how you're processing the data, specifically if you're doing JOINs or searches that need statistics to perform well, you may find one mechanism or the other to see good performance.

    2) and the main question is, should it be good practice to always make a temp table for looping, even if I have one single table to loop through.

    Always? No. Under most circumstances, avoid the loop. After that, sometimes using a cursor is better. Sometimes, table variables or temp tables. Testing and evaluation is necessary because every scenario is different and the query optimizer will make different choices. There just is NOT an "always" beyond, always test.

    3) pls consider loops in our case are quite small as far as the number of records are concerned and even the master table is also small,

    Small loops are less painful than big ones, but I'd still avoid them where I could.

    4) loops are small, but master table can grow to a million.

    yours sincerly

    As was already asked, which is it, small or large?

    A common approach to dealing with larger data sets can be to use temp tables (or table variables) to load them up in a series of queries, then join & filter on the temp tables, divide & conquer. However, usually while doing this, it's set based operations to fill the temp tables and then set based operations to combine them.

    I hope this helps some. Still, the other answers are dead on.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • The situation is, the table is small, we do not have indexes or relevant stats, and we want to loop, and there are no joins.

    In this situation what would be better, loop on table or should we put table in table variable then loop through?

     

     

  • Again, we're talking theoretical. I don't have a specific answer I can give you on this question. Either could work. Either could work well. Testing is your best friend. This is especially true because, as I already said, change the variables a little bit, and the answer will flip.

    A small table, no searching of any kind, no joins, chances are high, a table variable will work better. However, I'd still test it. Testing is how you know, not what some goofball (hi, my name is Grant) on the internet says.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • rajemessage 14195 wrote:

    well , there must be some thumb of rule  , i think that can be answered.

    There is no "Rule of Thumb" here because there are two "Rules of Thumb" in conflict here... "Touch data only once" and "Divide'n'Conquer".  So the correct answer is, indeed, "It Depends".

    In the absence of substantial real data to test with, I'll create a million row test table and test it.  You should do the same because it's all going to depend on the data and the process you use to look up the table in your "master" table, etc, etc.

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

Viewing 10 posts - 1 through 9 (of 9 total)

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