SQL While loop

I have a table with a 1 billion plus records, having fields timeID and UID , RID and some more.
There are multiple UID values associated with Each TimeID. Time ID starts with 164.

I want to run update on all records in smaller subsets to improve update efficiency. so I am doing a while loop such that it updates all records for one UID  for each time id. I have set timeID in the outer loop and UID in the inner loop. To be able to verify process, I am inserting timeID and UID in a table.

When I run this code, it runs all UID (-99, -88, 26 to 53) with time ID 164, which is good, but I am expecting this for all timeID from 164 to 1000. Please check my code and suggest why it is not showing me any other timeID except 164.


DECLARE @counter INT
DECLARE @min_count INT
DECLARE @max_count INT
DECLARE @sub_counter INT
DECLARE @Sub_min_count INT
DECLARE @sub_max_count INT

Truncate table dbo.Status_Update_myTable

--timeID
-- Query the table to find the starting timeID
SET @min_count = (SELECT Min(timeID) FROM   myTable)
PRINT 'Min timeID = '  + Cast(@min_count AS VARCHAR(8))

-- Query the table again to find the last Time ID
SET @max_count = (SELECT Max(timeID) FROM   myTable)
PRINT 'Max timeID = '  + Cast(@max_count AS VARCHAR(8))
---------------------------------------------------------------------
--UID
-- Query the table to find the starting UID
SET @Sub_min_count = -99 
PRINT 'Min UID = ' + Cast(@Sub_min_count AS VARCHAR(8))

-- Query the table again to find the last UID
SET @sub_max_count = (SELECT max(UID) FROM   myTable)
PRINT 'Max UID = ' + Cast(@sub_max_count AS VARCHAR(8))
--=====================================================================


-- Set Loop Control Variable Values
-- Set initial value (Min timeID Value - 1)
SET @counter = @min_count - 1
SET @sub_counter = @Sub_min_count -1

print @counter
print @sub_counter 


WHILE @counter < @max_count + 1 --Loop until counter greater than max Time ID
  BEGIN
      IF @counter = 212
        SET @counter = 999
      ELSE
        SET @counter = @counter + 1 --Increment Counter
      PRINT 'Updating timeID: '
            + Cast(@counter AS VARCHAR(8))

      WHILE @sub_counter < @sub_max_count + 1 --Loop until Sub counter greater than max UID
        BEGIN
            
            SET @sub_counter = @sub_counter + 1 --Increment Counter
            
            
            PRINT 'Updating UID: '
                  + Cast(@sub_counter AS VARCHAR(8))

            ---------------------------------------------------------
            -- Sample logic
            ---------------------------------------------------------
           --update statement here

            Select @counter,@sub_counter 
            INSERT INTO dbo.Status_Update_myTable
            (timeId, UID)
            SELECT @counter,@sub_counter  
            
            if    @sub_counter = -99 Set @sub_counter = -89 
            if    @sub_counter = -88 Set @sub_counter = 25         
                         
           ------------------------------------------------------------
           -- END logic
           ------------------------------------------------------------
        END  
                 
           
  END 

Open in new window

patd1Asked:
Who is Participating?
 
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
You only need to reset inner loop counter to make it working for all the timeid .
because fot second time when your second loop is executing your condition  

WHILE @sub_counter < @sub_max_count + 1 --Loop until Sub counter greater than max UID

is not satisfied. because your @sub_counter is not reset. I have pasted the code with modification.
Check the same and let us know.

Find the Modification by searching "Modified" word.

------------------ Script -------------------------



DECLARE @counter INT
DECLARE @min_count INT
DECLARE @max_count INT
DECLARE @sub_counter INT
declare @sub_min_count int
declare @sub_max_count int

Truncate table dbo.Status_Update_myTable

--timeID
-- Query the table to find the starting timeID
SET @min_count = (SELECT Min(timeID) FROM   myTable)
PRINT 'Min timeID = '  + Cast(@min_count AS VARCHAR(8))

-- Query the table again to find the last Time ID
SET @max_count = (SELECT Max(timeID) FROM   myTable)
PRINT 'Max timeID = '  + Cast(@max_count AS VARCHAR(8))
---------------------------------------------------------------------
--UID
-- Query the table to find the starting UID
SET @Sub_min_count = -99
PRINT 'Min UID = ' + Cast(@Sub_min_count AS VARCHAR(8))

-- Query the table again to find the last UID
SET @sub_max_count = (SELECT max(UID) FROM   myTable)
PRINT 'Max UID = ' + Cast(@sub_max_count AS VARCHAR(8))
--=====================================================================


-- Set Loop Control Variable Values
-- Set initial value (Min timeID Value - 1)
SET @counter = @min_count - 1

---- Modified
--SET @sub_counter = @Sub_min_count -1

print @counter
print @sub_counter

---

WHILE @counter < @max_count + 1 --Loop until counter greater than max Time ID
  BEGIN
      IF @counter = 212
        SET @counter = 999
      ELSE
        SET @counter = @counter + 1 --Increment Counter
      PRINT 'Updating timeID: '
            + Cast(@counter AS VARCHAR(8))

            
            --- Modified reset Inner counter Here. So that Second timne belove loop will be executed.
            SET @sub_counter = @Sub_min_count -1
      WHILE @sub_counter < @sub_max_count + 1 --Loop until Sub counter greater than max UID
        BEGIN
           
            SET @sub_counter = @sub_counter + 1 --Increment Counter
           
           
            PRINT 'Updating UID: '
                  + Cast(@sub_counter AS VARCHAR(8))

            ---------------------------------------------------------
            -- Sample logic
            ---------------------------------------------------------
           --update statement here

            Select @counter,@sub_counter
            INSERT INTO dbo.Status_Update_myTable
            (timeId, UID)
            SELECT @counter,@sub_counter  
           
            if    @sub_counter = -99 Set @sub_counter = -89
            if    @sub_counter = -88 Set @sub_counter = 25        
                         
           ------------------------------------------------------------
           -- END logic
           ------------------------------------------------------------
        END  
               
           
  END
0
 
arnoldCommented:
What are you looking to update, you can use a cursor that will get you the columns in the way you want?

What are the changes you hope to do.  Provide a sample data set and what the changes you are looking to make.
0
 
SguzekCommented:
First you need to define precisely what you mean by 'update efficiency '?  What do you want to achieve precisely?

If you count on faster update it is not going to happen I'm afraid.

The reason is quite simple - before even starting your update loop you already have 3 SELECT  MAX/MIN statements, which may be very expensive if you don't have indexes on UID and TimeID columns.

Then running update in a loop you will force system to scan the table (or seek the index with further lookups) for subset of rows to be updated - as many times as loop turns.

At the end of the process all the data will have to be moved from  disk to server memory and back to disk, and all changes will be written to transaction log - no matter if you fire one update, or have many updates run in a loop.
0
 
patd1Author Commented:
Thank you. That fixed it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.