Updating Access From SQL, Speed Issues??

[jrapp]

Hey everybody. I have a stored procedure that runs every 10 minutes and updates a few fields from SQL to Access via a linked server. The problem is, it takes about 15 seconds for each row to update, and at times there can be thousands of rows, meaning it takes a very long time to update. Here's my stored procedure:

[tt]
CREATE PROCEDURE proc_UpdateItems AS

DECLARE
@StatusID int,
@ID nvarchar(50),
@StartDate datetime,
@EndDate datetime,
@ScheduleDate datetime,
@Fee money,
@S2aID int,
@UpdLeft int

--Check to see how many updates remain
SELECT @UpdLeft=COUNT(*) FROM Items WHERE Uploaded=0

--Start the loop
WHILE @UpdLeft>0
BEGIN
--Grab an ID
SELECT @S2aID=(SELECT TOP 1 S2aID FROM Items WHERE Uploaded=0)

--Pull out the info to update
SELECT @StatusID=StatusID,
@ID=ID,
@StartDate=StartDate,
@EndDate=EndDate,
@ScheduleDate=ScheduleDate,
@Fee=Fee
FROM Items WHERE S2aID=@S2aID

UPDATE ACCESS_SAP...Items SET
StatusID=@StatusID,
ID=@ID,
StartDate=@StartDate,
EndDate=@EndDate,
ScheduleDate=NULL,
Fee=@Fee
WHERE ID=@S2aID

INSERT INTO ItemsUpdateBackup(S2aID,
StatusID,
ID,
StartDate,
EndDate,
ScheduleDate,
Fee) VALUES(
@S2aID,
@StatusID,
@ID,
@StartDate,
@EndDate,
@ScheduleDate,
@Fee)

UPDATE Items SET Uploaded=1 WHERE S2aID=@S2aID

SELECT @UpdLeft=COUNT(*) FROM Items WHERE Uploaded=0
END
GO
[/tt]

Thanks everybody!

 

[SQLSister]

Never ever under any circumstances use a loop to go through records and update them one at time!

Use a set-based update instead. REad in booksonline about how to do an update referencing another table.

Questions about posting. See faq183-874