November 27, 2016 at 3:29 am
hi,
i use loops sparigly , but when ever i use them , i see following pionts like
1) i am reading same table mulitpule times so by making temp table i can redure the locking blocking .
2) and at the same time any one can update insert . also.
3) some time i have to join tables and read it into temp table , so i save and read only one table at the time of looping
so is it good to make temp table all the time.
yours sincerely
November 27, 2016 at 6:26 am
rajemessage 14195 (11/27/2016)
hi,i use loops sparigly , but when ever i use them , i see following pionts like
1) i am reading same table mulitpule times so by making temp table i can redure the locking blocking .
2) and at the same time any one can update insert . also.
3) some time i have to join tables and read it into temp table , so i save and read only one table at the time of looping
so is it good to make temp table all the time.
yours sincerely
Temporary tables are a useful tool but as with any other tool, it should only be used when it fits, hence the answer to your question is no.
😎
Further, the tempdb is a shared resource and should be used sparingly, especially on busy servers.
November 27, 2016 at 9:28 am
rajemessage 14195 (11/27/2016)
hi,i use loops sparigly , but when ever i use them , i see following pionts like
1) i am reading same table mulitpule times so by making temp table i can redure the locking blocking .
2) and at the same time any one can update insert . also.
3) some time i have to join tables and read it into temp table , so i save and read only one table at the time of looping
so is it good to make temp table all the time.
yours sincerely
What would be more interesting is to find out what the processes are where you think you need a loop. Not all loops are bad but a lot of people resort to them because they give up a bit too early on set based processes or aren't even aware that a process can be done in a set based fashion.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2016 at 2:41 am
i have taught my team to use set based operation strictly, but there are cases where ,we are forced.
so specific to loop: i wanted to know that , should we use temp table,even in single table looping also
like if i have to loop single table , should i make temp table , to reduce lock and block.
November 30, 2016 at 2:59 am
I would say no, although it does, of course, depend on what your loop is actually doing. If you use a correctly configured cursor then the result set through which you loop should be held in memory, which will minimise blocking.
John
November 30, 2016 at 3:13 am
Got your point. You will use loops sparingly. But whenever using loop is it a good practice to use temp tables instead of the actual table. is that your query?
It is better not to generalize it and put it as a standard. As Jeff already mentioned each case is different. Being inclined to set based coding is good always. But temp tables for loops? sometimes OK sometimes not. It would depends on what isolation you need on your data. If you don't want to consider the latest updates on those rows, then OK. Else you might want to put some locks there.
November 30, 2016 at 9:00 am
rajemessage 14195 (11/30/2016)
i have taught my team to use set based operation strictly,...
Excellent. You're a man after my own heart.
rajemessage 14195 (11/30/2016)
... but there are cases where ,we are forced.
That's what I'm talking about. A lot of people think that a given process simply can't be done without an explicit loop or a bit of "Hidden RBAR". It's not because they're bad programmers. It just that they sometimes don't know how. For example, is it possible to write a query against a million node hierarchy that will return the total sales for each person AND the total sales for each level of the "downline" for that person in less than a minute or will you need a loop for it? Most people end up writing a loop for such a thing and it can totally be avoided fairly easily but only if you know how.
Having gotten that off my chest, let's get to your question of...
so specific to loop: i wanted to know that , should we use temp table,even in single table looping also
like if i have to loop single table , should i make temp table , to reduce lock and block.
That's a 'Tried'n'True' method of 'Divide'n'Conquer' and frequently has the side benefits of moving the problem to high-speed memory (things in TempDB start there and only spill to disk if they get big enough) as well as allowing the data to persist during troubleshooting efforts. It's one of the reasons I don't use cursors. It's true that a static FIREHOSE cursor runs just as fast as a well written WHILE loop/Temp Table combination and can actually be easier to write but are more difficult to troubleshoot because you can't see the underlying data without jumping through the DEBUG hullabaloo. It also has the action of possibly acting as a simplification or pre-aggregation of data greatly reducing the needs for joins that would otherwise possibly cause a many-to-many race condition requiring the use of (ugh!) DISTINCT to uniquify the output.
But, again, "It Depends" and you simply need to try it. Populating the Temp Table and, perhaps, Indexing it may take longer that you might expect but, at the same time, I've done things like reducing a server crippling 45 minute run to a 3 second burp by using Temp Tables as you suggest.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply