StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><b><Edit> (9/28/2011)</b><br/> My answer performs basically the same way as Thomas' solution (Aug 6 '10). I missed it when I posted my answer because it he uses an actual CURSOR so I thought to myself "bad" because of the # of records involved. However, when I reread his answer just now I realize that the WAY he uses the cursor is actually "good". Very clever. I just voted up his answer and will probably use his approach in the future. If you don't understand why, take a look at it again. If you still can't see it, post a comment on this answer and I will come back and try to explain in detail. I decided to leave my answer because someone may have a DBA who refuses to let them use an actual CURSOR regardless of how "good" it is. :-)<br/> <b></Edit></b></p> <p>I realize that this question is a year old but I recently had a similar situation. I was trying to do "bulk" updates to a large table with a join to a different table, also fairly large. The problem was that the join was resulting in so many "joined records" that it took too long to process and could have led to contention problems. Since this was a one-time update I came up with the following "hack." I created a WHILE LOOP that went through the table to be updated and picked 50,000 records to update at a time. It looked something like this:</p> <pre><code>DECLARE @RecId bigint DECLARE @NumRecs bigint SET @NumRecs = (SELECT MAX(Id) FROM [TableToUpdate]) SET @RecId = 1 WHILE @RecId < @NumRecs BEGIN UPDATE [TableToUpdate] SET UpdatedOn = GETDATE(), SomeColumn = t2.[ColumnInTable2] FROM [TableToUpdate] t INNER JOIN [Table2] t2 ON t2.Name = t.DBAName AND ISNULL(t.PhoneNumber,'') = t2.PhoneNumber AND ISNULL(t.FaxNumber, '') = t2.FaxNumber LEFT JOIN [Address] d ON d.AddressId = t.DbaAddressId AND ISNULL(d.Address1,'') = t2.DBAAddress1 AND ISNULL(d.[State],'') = t2.DBAState AND ISNULL(d.PostalCode,'') = t2.DBAPostalCode WHERE t.Id BETWEEN @RecId AND (@RecId + 49999) SET @RecId = @RecId + 50000 END </code></pre> <p>Nothing fancy but it got the job done. Because it was only processing 50,000 records at a time, any locks that got created were short lived. Also, the optimizer realized that it did not have to do the entire table so it did a better job of picking an execution plan.</p> <p><b><Edit> (9/28/2011)</b><br/> There is a HUGE caveat to the suggestion that has been mentioned here more than once and is posted all over the place around the web regarding copying the "good" records to a different table, doing a TRUNCATE (or DROP and reCREATE, or DROP and rename) and then repopulating the table.</p> <p>You cannot do this if the table is the PK table in a PK-FK relationship (or other CONSTRAINT). Granted, you could DROP the relationship, do the clean up, and re-establish the relationship, but you would have to clean up the FK table, too. You can do that BEFORE re-establishing the relationship, which means more "down-time", or you can choose to not ENFORCE the CONSTRAINT on creation and clean up afterwards. I guess you could also clean up the FK table BEFORE you clean up the PK table. Bottom line is that you have to explicitly clean up the FK table, one way or the other.</p> <p>My answer is a hybrid SET-based/quasi-CURSOR process. Another benefit of this method is that if the PK-FK relationship is setup to CASCADE DELETES you don't have to do the clean up I mention above because the server will take care of it for you. If your company/DBA discourage cascading deletes, you can ask that it be enabled only while this process is running and then disabled when it is finished. Depending on the permission levels of the account that runs the clean up, the ALTER statements to enable/disable cascading deletes can be tacked onto the beginning and the end of the SQL statement. <b></Edit></b></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload