SQL - slow While loop

Question

I have the following T-SQL code that is either adding or updating 1 record at a time to a temp table. Does anyone have any suggestions to speed this process up?

DECLARE @Total AS INT

SELECT @Total = count(AgentsID) from #TempAgentsConcat

DECLARE @counter AS INT
SET @counter = 1

DECLARE @CurrentVal AS NVARCHAR(1024)
DECLARE @RowCount AS INT
DECLARE @OBJ_ID AS INT

while (@counter <= @Total)
  begin
    SELECT @OBJ_ID = Id FROM #TempAgentsConcat WHERE AgentsId = @counter
    SELECT @CurrentVal = SVRMachine FROM #TempAgentsConcat WHERE ID = @OBJ_ID
    IF EXISTS (SELECT * FROM #TempEndpoints WHERE ID = @OBJ_ID)
        BEGIN
            UPDATE #TempEndpoints SET SVRMachine = @CurrentVal WHERE ID = @OBJ_ID
        END
    ELSE
        BEGIN
            INSERT INTO #TempEndpoints (SVRMachine, IPPort, ID)
            VALUES (@CurrentVal, NULL, @OBJ_ID)
        END
    --END

    SET @counter = @counter + 1
  end

The main solution is to not use a loop here. This should be set based. That update in here looks a little suspect to me anyway because it is updating the table but you seem to not have any order in your rows so you don't even know which row it is getting. I can help you make this a set based approach but you need to provide some details. Here is a great place to start. spaghettidba.com/2015/04/24/… — Sean Lange
– Sean Lange, Commented Aug 20, 2018 at 16:27
Let's talk about potential flaws. Currently your logic assumes that AgentsID is unique in your 1st temp table. Is that valid? What happens when it is not? Your logic also assumes that the values of AgentsID are sequential without gaps starting with 1. That looks even more suspicious. — SMor
– SMor, Commented Aug 20, 2018 at 16:53

Vinit · Accepted Answer · 2018-08-20 18:09:17Z

1

It looks like, you are trying to merge one table into other. First lets talk of couple of issues in your query- 1. Avoid using loops unless it's extremely necessary. 2. You are assigning two different variables by reading same row in 2 queries. You can do this in single query like

SELECT @OBJ_ID = Id,@CurrentVal = SVRMachine FROM #TempAgentsConcat WHERE AgentsId = @counter

instead of 2 queries

SELECT @OBJ_ID = Id FROM #TempAgentsConcat WHERE AgentsId = @counter
SELECT @CurrentVal = SVRMachine FROM #TempAgentsConcat WHERE ID = @OBJ_ID

Let's rewrite the query without using loops. The answer by @Cetin is one of the solutions. Your requirement looks classic example of merging tables, so you may use SQL MERGE (SQL server 2008 and above). You can read more about MERGE, here, checkout the example 'C'.

Using MERGE, your query will look like below.

MERGE INTO #TempEndpoints AS Target  
USING (SELECT SVRMachine , Id from #TempAgentsConcat)  
       AS Source (SVRMachine, ID)  
ON Target.ID = Source.ID  
WHEN MATCHED THEN  
  UPDATE SET SVRMachine = Source.SVRMachine  
WHEN NOT MATCHED BY TARGET THEN  
  INSERT (ID, IPPort, SVRMachine) VALUES (Id, NULL,SVRMachine)

answered Aug 20, 2018 at 18:09

Vinit

2,6271 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy F. Over a year ago

Once I switch to your merge suggestion, I no longer needed the first query which was used as a counter for the loop.

Cetin Basoz · Accepted Answer · 2018-08-20 16:33:41Z

0

Why would you use a lot of variables and loop. SQL server (any SQL series database as well) works best with sets, rather than loops:

UPDATE #TempEndpoints
SET SVRMachine = ac.SVRMachine
FROM #TempAgentsConcat ac
WHERE #TempEndpoints.Id = ac.ID;

INSERT INTO #TempEndpoints
(
    SVRMachine,
    ID
)
SELECT SVRMachine,
       ID
FROM #TempAgentsConcat ac
WHERE NOT EXISTS
(
    SELECT * FROM #TempEndpoints ep WHERE ep.ID = ac.ID
);

answered Aug 20, 2018 at 16:33

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Collectives™ on Stack Overflow

SQL - slow While loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related