MSSQL fast update

Question

I have Sql-query ike this:

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

Where {0} - it's a sting of id's, for example:

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

And this string can have couple thousands Id.

How I can do this query more easier, because this query using 100% CPU.

I want to know some alternates.

An interesting read: http://www.sommarskog.se/arrays-in-sql-2005.html — Bridge, Nov 09 '12 at 11:05

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

If your IDs are a comma separated list then you could use a 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

edited May 23 '17 at 12:04

Community

1
1

answered Nov 09 '12 at 11:15

Kaf

33,101
7
58
78

So, best way to do it, like stored procedure? – Smit Nov 12 '12 at 08:20
Yes, you have to do the split function first. – Kaf Nov 12 '12 at 09:17
Can you explain how to do SplitFunction? – Smit Nov 12 '12 at 09:52
Found this article (http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str), start reading... – Smit Nov 12 '12 at 09:57
I got it. I did SplitFunction and tried to use my program. I run my program, program finished, and was no UPDATE result. After that I repeat all steps in Management Studio, and all working. Why it's not working from program? – Smit Nov 12 '12 at 11:23
If your stored procedure call works from management studio, that means it works, problem could be in your program. Check it. may be wrong parameters. – Kaf Nov 12 '12 at 11:34
Hm...I can't see any mistakes in my program, maybe some mistake in statement? Can you check this code please? http://pastebin.com/EBYs0K7c – Smit Nov 12 '12 at 11:47
Change `@IdList varchar` to `@IdList varchar(max)` – Kaf Nov 12 '12 at 11:54
Thank you! That's was a problem! Little bit later I'll check performance. – Smit Nov 12 '12 at 12:02

score 1 · Answer 2 · answered Nov 09 '12 at 11:05

You can create a temp table containing these ids, then UPDATE with JOIN to this table. Something like:

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

score 1 · Answer 3 · answered Nov 09 '12 at 11:23

As far i surfed net, IN shows slow performance... i would suggest to use JOIN of two tables... where 1st table will be the table which is to be updated and the second one will be the temporary table which holds all the Id values... Then ur UPDATE query will look like UPDATE tProfile tp INNER JOIN IdTable it ON tp.Id = it.Id SET LastActivity=@DateTimeNow WHERE UserId=@UserId

MSSQL fast update

3 Answers3