MSSQL Quick Update

Question

I have an Sql request, for example:

UPDATE tProfile SET LastActivity=@DateTimeNow WHERE UserId=@UserId AND Id IN ({0}) 

Where {0} is the id bite, for example:

 UPDATE tProfile SET LastActivity=@DateTimeNow WHERE UserId=@UserId AND Id IN (1155,1684,41368) 

And this line can contain a couple of thousand Id.

How can I make this request easier because this request uses a 100% processor.

I want to know several alternatives.

Answer 1

If your identifiers are a comma-delimited list, you can use the split function (UDF) to insert them into a table variable and then update.

 DECLARE @IdTable TABLE (Id INT) INSERT INTO @IdTable SELECT Id FROM dbo.SplitFunction(@IdList,',') UPDATE p SET p.LastActivity = @DateTimeNow FROM tProfile p INNER JOIN @IdTable i ON p.Id = i.Id WHERE p.UserId = @UserId 

Answer 2

You can create a temporary table containing these identifiers, and then UPDATE using JOIN to this table. Sort of:

 UPDATE t1 SET t1.LastActivity=@DateTimeNow FROM tProfile t1 INNER JOIN ( SELECT 1115 Id UNION ALL SELECT 1684 UNION ALL SELECT 41368 ... ) t2 ON t1.UserId = t2.Id 

Answer 3

While I am browsing the network, IN is showing slow performance ... I would suggest using a JOIN from two tables ... where the 1st table will be the table that needs to be updated, and the second will be the temporary table, contains all the Id values ​​... Then an UPDATE ur query will look like this: UPDATE tProfile tp INNER JOIN IdTable it ON tp.Id = it.Id SET LastActivity=@DateTimeNow WHERE UserId=@UserId