October 29, 2015 at 12:37 pm
Perhaps my brain just isn't working lately but I'm having problems contemplating this while loop. I understand that while 1=1 creates an infinite loop and that the if @@rowcount = 0 then terminates the loop. But I am totally confounded as to how the query knows to iterate through all the records in the table. If you run it to completion, it only processes 29,000 of 49,000 records. I am not convinced that it is starting at the first record and cycling through every single record.
I am having trouble breaking this down to troubleshoot it and I am not any closer to solving the riddle. Any help is appreciated!
DECLARE @KEYIDVALUE varchar(30) = ''
DECLARE @BASECODE varchar(10)
DECLARE @ASSETUID varchar(15)
DECLARE @CATCODE varchar(10)
DECLARE @COMPONENTNO varchar(3)
DECLARE @QUALRATING INT
DECLARE @MISSIONRATING INT
DECLARE @CURRENTDATE varchar(8)
-- Iterate over all rows
WHILE (1=1)
BEGIN
-- Get next row
SELECT TOP 1 @KEYIDVALUE = Asset_UID + CatCode,
@BASECODE = BaseCode,
@ASSETUID = Asset_UID,
@CATCODE = CatCode,
@COMPONENTNO = Component_No,
@QUALRATING = Qual_ColorRating,
@MISSIONRATING = MSN_ColorRating,
@CURRENTDATE = DATEPART(YYYY, GETDATE()) * 10000 + DATEPART(MM, GETDATE()) * 100 + DATEPART(DD, GETDATE())
FROM mytable
WHERE Asset_UID + CatCode > @KEYIDVALUE
ORDER BY Asset_UID + CatCode
-- Exit loop if no more rows
IF @@ROWCOUNT = 0 BREAK;
print @AssetUID + @CatCode + ' Begin Time: ' + convert(varchar(15),convert(time,getdate()))
EXEC mystorecprocedure @BASECODE,@ASSETUID,@CATCODE,@COMPONENTNO,@QUALRATING,@MISSIONRATING,0,@CURRENTDATE;
print @AssetUID + @CatCode + ' End Time: ' + convert(varchar(15),convert(time,getdate()))
END
Any insight is appreciated! The print statements were my attempt to troubleshoot each record.
October 29, 2015 at 12:47 pm
Also, the ASSET_UID and CATCODE fields are varchar... so wouldn't trying to do a greater than on a varchar field not really work properly?
October 29, 2015 at 1:09 pm
This is one of those examples where people think that a while loop is a better option than a cursor, but it very possibly isn't.
The while loop gets the first row of the table ordered by the composite key, assigns the values to the variables and executes the procedure. In the next iteration it does the same query but excludes the key used previously. Each iteration will exclude more rows. If Asset_UID + CatCode generate duplicate values, you'll miss rows. Also, there will be a full table scan for each iteration of the loop.
A well defined cursor would be a better option in this case as it won't have to go through the table each time.
The best option will be to generate a procedure that doesn't need to work one row at a time.
October 29, 2015 at 1:14 pm
Strings can be compared and the ones that come first in alphabetical order are lower than the ones that come last.
Example:
CREATE TABLE #Sample(
myString varchar(1));
INSERT INTO #Sample
VALUES('A'),
('B'),
('C'),
('D'),
('E'),
('F'),
('G'),
('H');
DECLARE @KEYIDVALUE varchar(30) = ''
-- Iterate over all rows
WHILE (1=1)
BEGIN
-- Get next row
SELECT TOP 1 @KEYIDVALUE = myString
FROM #Sample
WHERE myString > @KEYIDVALUE
ORDER BY myString
-- Exit loop if no more rows
IF @@ROWCOUNT = 0 BREAK;
PRINT @KEYIDVALUE;
END
DROP TABLE #Sample
October 29, 2015 at 1:21 pm
Thanks for the reply! Yea I do not like this method one bit and I really hate the way it is coded. I am a bit at a loss as to why the code was written to have to execute every single record into a stored procedure one by one.
I will try redoing it with a cursor.
October 29, 2015 at 2:27 pm
You might look into the stored procedure that is called and see if it can be modified to handle more than one row at a time. You might be able to merge the procedures. Unfortunately you'll lose that oh-so-useful logging of the begin and end time for each row.
October 29, 2015 at 5:22 pm
DECLARE @KEYIDVALUE varchar(30) = ''
DECLARE @BASECODE varchar(10)
DECLARE @ASSETUID varchar(15)
DECLARE @CATCODE varchar(10)
DECLARE @COMPONENTNO varchar(3)
DECLARE @QUALRATING INT
DECLARE @MISSIONRATING INT
DECLARE @CURRENTDATE varchar(8)
DECLARE cursor_assets CURSOR LOCAL FAST_FORWARD FOR
SELECT Asset_UID + CatCode,
BaseCode,
Asset_UID,
CatCode,
Component_No,
Qual_ColorRating,
MSN_ColorRating
FROM mytable
ORDER BY Asset_UID + CatCode
OPEN cursor_assets
-- Iterate over all rows
WHILE (1=1)
BEGIN
-- Get next row
FETCH NEXT FROM cursor_assets INTO @KEYIDVALUE, @BASECODE, @ASSETUID, @CATCODE, @COMPONENTNO, @QUALRATING, @MISSIONRATING;
-- Exit loop if no more rows
IF @@FETCH_STATUS <> 0
IF @@FETCH_STATUS = -1
BREAK
ELSE
CONTINUE
SET @CURRENTDATE = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
print @AssetUID + @CatCode + ' Begin Time: ' + convert(varchar(15),convert(time,getdate()))
EXEC mystorecprocedure @BASECODE,@ASSETUID,@CATCODE,@COMPONENTNO,@QUALRATING,@MISSIONRATING,0,@CURRENTDATE;
print @AssetUID + @CatCode + ' End Time: ' + convert(varchar(15),convert(time,getdate()))
END
DEALLOCATE cursor_assets
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply