Faster/Better Processing for SQL While Loop I have a list of about 55k Accounts that I am cleaning up.  95% of them are from US/Canada which is what I'm most concerned with.  I am going through record by record and will perform various checks and fill in or correct City, State, Zip, Country.  I have a table with every City, State, and Zip for US and Canada that I check against.  For this part, maybe 20% will be processed after grabbing the info.

I am doing a kind of proof of concept right now, and it's taking forever.  I just hit the 1hr mark with 24,500 (Less than 7 records/sec)  .  I didn't expect this to take so long.  Next will be running a similar process on the Contacts, and there are over 400k contacts.  I'm afraid how long that will take!!

Is there a better way to do this to decrease the time taken?

Here is the code right now:

-- MS SQL Server 15.0.2070.41
ALTER PROCEDURE [dbo].[SetLocationInfo]
AS
BEGIN

DECLARE
    @maxRecords int = 0,
    @cnt        int = 1,
    @city       varchar(255),
    @state      varchar(255),
    @zip        varchar(255),
    @country    varchar(255)


SELECT TOP(1) @maxRecords = id2 FROM Account ORDER BY Id2 DESC

-- BEGIN LOOP
WHILE @cnt &lt;= @maxRecords
BEGIN
    -- Resets Variables
    SET @city = NULL
    SET @state = NULL
    SET @zip = NULL
    SET @country = NULL

    -- Pulls BILLING Address Info from Record
    SELECT
        @city = [BillingCity],
        @state = [BillingState],
        @zip = [BillingPostalCode],
        @country = [BillingCountry]
      FROM [Account] WHERE Id2 = @cnt

    -- Searches for and Sets BILLING Country
    IF (@country IS NULL) OR ((@country != 'United States') AND (@country != 'Canada'))
    BEGIN
        SELECT TOP(1) @country = Country FROM [CityStateInfo] 
          WHERE 
          (City = @city AND State_abbr = @state) OR 
          (City = @city AND Zip = LEFT(@zip,5)) OR
          (Zip = LEFT(@zip,5))

        IF @country IS NOT NULL
            UPDATE Account SET [BillingCountry] = @country WHERE Id2 = @cnt
    END


    -- Resets Variables
    SET @city = NULL
    SET @state = NULL
    SET @zip = NULL
    SET @country = NULL

    -- Pulls SHIPPING Address Info from Record
    SELECT
        @city = [ShippingCity],
        @state = [ShippingState],
        @zip = [ShippingPostalCode],
        @country = [ShippingCountry]
      FROM [Account] WHERE Id2 = @cnt

    -- Searches for and Sets SHIPPING Country
    IF (@country IS NULL) OR ((@country != 'United States') AND (@country != 'Canada'))
    BEGIN
        SELECT TOP(1) @country = Country FROM [CityStateInfo] 
          WHERE 
          (City = @city AND State_abbr = @state) OR 
          (City = @city AND Zip = LEFT(@zip,5)) OR
          (Zip = LEFT(@zip,5))

        IF @country IS NOT NULL
        BEGIN
            PRINT CAST(@cnt AS varchar(10)) + ': ' + @country
            UPDATE Account SET [ShippingCountry] = @country WHERE Id2 = @cnt
        END
    END


    -- Increments Counter
    SET @cnt = @cnt + 1
END
-- END LOOP

END
Please update your questions with the relevant tags, although it is obvious you are using SQL Server here.

If you run an execution plan in your query window, you will be able to see which specific bits are taking a lot time and whether SQL Server is using any indexes etc.

It would be good if you gave a data sample and the table structure. Here are some ideas though.

Is there a reason why you are using a WHILE loop instead of a cursor ? The idea here is that you start with ID 1 and increment until you reach the max ID but there must probably be gaps in your table... the approach is possibly wasteful if you are trying to update rows that don't even exist.

Rather than work on the table Account directly (which may be in use or subject to locks), I would rather work on a partial copy using a temporary table.

Step 1: create a temporary table with all the records from Account you want to fix, matching some criteria eg country empty

SELECT *
INTO #Account
FROM Account
WHERE &lt;add your criteria here&gt;


Step 2: perform all your operations on that temporary table #Account

Step 3: update the original table based on the temporary table (UPDATE FROM):

UPDATE Account
SET
    Account.ShippingCountry = #Account.ShippingCountry,
    Account.BillingCountry = #Account.BillingCountry,
    ...
FROM #Account
WHERE
  #Account.id2 = Account.id2


Here I am assuming that id2 is indeed an identity column that uniquely identifies each record. If it isn't, but instead is some kind of unindexed integer value, then each UPDATE requires a full table scan and there is a performance hit here. Again, can't tell without knowing the table structure for sure.

If all this takes too much time, you can process the jobs in small chunks, for example pull 1000 records at a time using SELECT TOP 1000, and repeat until the table is cleaned.

In short, updating a large table can be very slow if you cannot rely on some index, using an appropriate WHERE clause.

Important remark regarding the temporary table approach: doing SELECT INTO creates a copy of the data but does not include the complete table structure, that is indexes, constraints. That mean the id column will be an ordinary integer and not a unique, indexed field. So I would add the index manually on that field to speed the UPDATE FROM.