November 29, 2022 at 11:03 am
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 ?
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
November 29, 2022 at 3:00 pm
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
November 29, 2022 at 4:04 pm
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!
November 29, 2022 at 10:43 pm
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 ?
- 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
Change is inevitable... Change for the better is not.
November 30, 2022 at 4:56 am
well , there must be thumb of rule , i think that can be answered.
November 30, 2022 at 4:57 am
well , there must be some thumb of rule , i think that can be answered.
November 30, 2022 at 9:18 am
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.
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 ?
- 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2022 at 9:44 am
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?
November 30, 2022 at 10:13 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2022 at 2:30 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply