loops

I agree. There are instances where a While Loop will blow the doors off of set based solutions but such instances are a bit like finding hen's teeth... unless you still think that Yield signs are Yellow. 😉

Ok... here's my rather long winded, high velocity Pork Chop reply...

http://ask.sqlservercentral.com/questions/75404/strip-all-but-alpha-chars-out-of-a-string?page=1#76191

The "Pork Chop" reference comes from a much older post that I've actually lost track of. Some manager was whining about how to control one of his "developers" who was riding rough-shod over company programming standards and violating most good practices, as well. To make a really long story shorter, I tried coaching the manager and every suggestion I offered was met with the equivalent of "I can't... I don't actually have a spine" type of reply. I got fed up and ended the thread with a suggestion something like the following...

You need to talk with the "developer" one-on-one. Take him out to dinner to get his attention... a nice "Pork Chop" dinner. Tie him to the chair and feed him the Pork Chops... at point-blank range with a "Wrist Rocket".

In case you don't get that reference, a "Wrist Rocket" is a type of very, very powerful sling shot. 😉

BWAA-HAAA!!!! I didn't see THAT before. Now THAT's some funny stuff! Well done, Lutz! 😀

looking forward to that already Jeff

Uh oh, duck everybody :w00t:

USE tempdb

GO

Declare @TotalRows int, @minRow int

CREATE TABLE #Temp

( Id int identity(1,1),

Queue_stub uniqueidentifier

)

INSERT INTO #temp

SELECT Queue_Stub

FROM <table_name> WHERE condition

SET @TotalRows=@@Rowcount

SET @minRow=1

--Using loops

DECLARE @queue_stub uniqueidentifier

SET @queue_stub = ''

WHILE @minRow <=@TotalRows

BEGIN

SET @queue_stub = ''

SELECT @queue_stub = queue_Stub FROM #temp with (NOLOCK) WHERE id = @minRow

SELECT @message_body = queue_message

FROM <table_name>

WHERE queue_stub = @queue_stub

IF (@queue_stub is not null and @should_playback = 1 )

BEGIN

EXEC function

@queue_stub = @queue_stub,

@command = @message_body,

@isDebug = 0

END

SET @minRow= @minRow +1

END

I did it yesterday, Perry. See the following...

http://ask.sqlservercentral.com/questions/75404/strip-all-but-alpha-chars-out-of-a-string?page=1#76191

You'll be able to tell, right off, that I was a little ticked at the prevailing attitude of a supposedly intelligent poster. I just couldn't believe the words coming out of his "mouth" especially since he forgot to include a Clustered Index on the Tally Table and his test rig is, ummm... let's say, politely say "Wholly less than adequate".

Thanks for posting your solution but we still don't know what your function does nor why it requires RBAR. Any chance of you posting the code for the function?

well thats a pretty long SP for this....

it basically processes the stuck elements in the queue.

confidential types soorrryyyyy 🙁

@jeff,

I understand the relucatance to use loops in order to do set-based processing, but lets say you had a table which had a million records in it, and which was currently being used in production. Let's say you were investigating a bug which had been lurking about for months, and you finally found the solution.

Now, let's say the solution required you to update a batch of records from the table, based on the results of a query.

You could write the query as an UPDATE

SET [FIELDS] = [VALUES] WHERE [ROWS] = [FILTERS]. Correct me if I'm wrong though, but doing this would cause the rows being updated to all be locked until the update was finished.

Any other updates that are being done on those records would be locked out until the whole transaction was finished. Same with any SELECT statements that were not written with NOLOCK hints.

If this update was somewhat intensive, and took an hour or so to process, lets say because the query required to find those records was an expensive one, would you not agree that in this case a row-by-row update would be superior, as, despite the fact that it may take much longer to complete, it only locks one record at a time?

Although I don't speak for Jeff in this matter, I would say "it depends" (to coin a cliche`). If the query to identify rows to be updated was really expensive, it might be a good idea to use that query to populate a temp table with the keys of the rows in question. Then the update itself would be independent of the expensive query. It might also be a good idea to run the update during a maintenance window (if available) when concurrent activity would be minimized.

But even if we all agree that the superior solution in your example is to minimize locking, rather than to minimize query speed, that isn't the point. Nobody is arguing that procedural solutions are always evil, just that set-based solutions are generally faster and usually preferable. Do exceptions exist? Of course they do. The problem is when someone looks at an exception and tries to turn it into the general rule. Lots of procedural-thinking developers choose to use a while loop because it is a familiar construct, and because they don't understand the performance implications.

Yes, there are cases where a developer might be doing a little ad-hoc exploration and the runtime of a while loop doesn't justify the additional development time it would take to come up with a set based solution. But if that developer never gets in the habit of non-procedural thinking, he or she is more likely to use procedural solutions when they are inappropriate.

One of the great things about SSC is that you often see competing solutions being tested, until one proves itself to be faster than the others. Set based solutions are usually the winners.

Probably not and, no, it wouldn't be a problem. If it takes a while to find the rows to update, I darned sure wouldn't accomplish that task during the update. And, no... even though I've had to update many table with millions of rows, it's never taken an hour.

You are correct... well, partially. Every system has a "tipping point" for updates. For the larger systems, it's usually more than a couple of million rows before a system loses its mind. So, yes, I'll sometimes use a loop to do updates... 500,000 or a million rows at a time. 😉 I'll also tell you that, for such voluminous updates, I normally don't have to worry about any contention because I'll normally snap a TABLOCKX on the table for the second or two I'm in it. 😉 It may take 30 minutes waiting for the TABLOCKX to take effect, but it's just sitting there... waiting for the right moment to pounce and then WHAM! Lock the table, do my thing, and get out... usually in less than a second depending on what needs to be done.

I've also a similar thing for large volume deletes...

And, now that I look back, I see Dixie responded in a similar fashion. 😀