1

I have 4000 records in a temp table with one column. The column is a customerid that I need to update a record for. As of right now the only thing that comes to mind is running an exec statement for all 4000 records. Is there a more efficient way to do this without the use of a cursor?

2
  • First - stored procedures can access temp tables that already exist when the procedure runs, in case that's the concern. That said, I'm not clear on what your actual problem is. Do you have to apply multiple commands against each customerid? Could you move through multiple temp tables or update statements to accomplish the same thing? Do you have to update the records in a particular order? What's stopping you from doing this in a set-based way? More detail about the specifics would make good ideas more likely.
    – RDFozz
    Jun 2, 2017 at 22:20
  • 3
    An exec statement that does what? Why do you have to treat each row individually? Anyway, if you must process each row separately, it doesn't really matter if you use a cursor or while loop or any other one-row-at-a-time construct, it's going to perform poorly at any decent scale/concurrency. Jun 2, 2017 at 22:44

2 Answers 2

1

Why do it RBAR style? Since all you want is to update a a table based in another one you can just do it by using a join.

update updt
set updt.field = 'value'
from dbo.TargetUpdatableTable updt
join #MyTempTable mtt on mtt.ID = updt.ID

This way you can update your table but only those ids are in the temp table.

1
  • I ended up using a cursor. But this is the most concise and obvious thing I wish I would've thought of. Feel dumb I didn't think of this. Thanks for your input! Jun 8, 2017 at 21:04
2

If you need to improve the performance of your code it probably makes sense to focus on converting the stored procedure into a set-based operation. For example, instead of issuing 4000 individual UPDATE queries you could issue one UPDATE that acts on 4000 rows. Changing from a cursor to some other row by row method benefit is very unlikely to speed up your code.

If you truly need to loop over all of the rows in a table just use a cursor. That's what they're designed for. This is especially true when you're doing with a small number of rows in the table like 4000. The cursor won't take a meaningful amount of time as long as you don't increase the number of rows or have many concurrent processes.

Test procedure and data:

CREATE OR ALTER PROCEDURE dbo.DO_NOTHING (@input_id INT)
AS
BEGIN
    DECLARE @i INT;
END;

GO

CREATE TABLE #t (ID INT);

INSERT INTO #t
SELECT TOP 2000 number 
FROM master..spt_values

UNION ALL

SELECT TOP 2000 number 
FROM master..spt_values;

Here's the code that calls the procedure for each row in the temp table:

DECLARE @id INTEGER;
DECLARE @rows_processed INTEGER = 0;
DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR 
SELECT id
from #t;

PRINT 'Start time = ' + CAST(SYSDATETIME() AS VARCHAR(30));
OPEN cur;  

FETCH NEXT FROM cur INTO @id;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    EXEC dbo.DO_NOTHING @id;

    FETCH NEXT FROM cur INTO @id; 
    SET @rows_processed = @rows_processed + 1;
END;
CLOSE cur;  
DEALLOCATE cur;  
PRINT 'Rows processed = ' + CAST(@rows_processed AS VARCHAR(5));
PRINT 'End time = ' + CAST(SYSDATETIME() AS VARCHAR(30));

The output on my machine:

Start time = 2017-06-02 22:39:23.2709937

Rows processed = 4000

End time = 2017-06-02 22:39:23.3255551

The code ran in 0.05 seconds.

1
  • you can pass the temptable as Table to stored proc,depending on what your proc does Jun 5, 2017 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.