Update statement: use the in clause or run simple update 500 times

Question

Update statement: use the in clause or run simple update 500 times

chandrika5

SSCrazy

Points: 2734
More actions
August 7, 2015 at 2:47 pm

#310749

I have to do updates via web app. Stuck with time out:
Update mytable
set col1= 'some value',
col2 = 'some other value',
where Id in ( ,,,,,,,, ......)
The Id is primary key, Big Int not null and is Identity.
The updates are in batches of 500 IDs from the web application. Instead of running the update statement 500 times, I created an IN clause and do the update.
Now this is timing out.
What are my optimum options ?
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

chandrika5 (8/7/2015)
I have to do updates via web app. Stuck with time out:
Update mytable
set col1= 'some value',
col2 = 'some other value',
where Id in ( ,,,,,,,, ......)
The Id is primary key, Big Int not null and is Identity.
The updates are in batches of 500 IDs from the web application. Instead of running the update statement 500 times, I created an IN clause and do the update.
Now this is timing out.
What are my optimum options ?
Thanks,

Pretty sparse on any actual details here. The list of other options can get pretty lengthy here. The first thought I have is to use a table valued parameter. Or maybe there is some other logic that would let you use a join instead of sending in 500 rows. Or maybe....well who knows?!?!?!?

If you can provide some actual details we can provide some actual help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

x SSC-Insane Points: 23660 More actions · Answer 2

My first step would be to try the sql statement in sql studio in a test environment (hopefully the same environment you're developing your web app with 😉 and see how long it actually takes. If it seems unreasonable then try to troubleshoot the query. If on the other hand, it seems to run pretty well, try to isolate the differences in the circumstances that your web app introduces into this statement execution as compared to sql studio.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

chandrika5 (8/7/2015)
I have to do updates via web app. Stuck with time out:
Update mytable
set col1= 'some value',
col2 = 'some other value',
where Id in ( ,,,,,,,, ......)
The Id is primary key, Big Int not null and is Identity.
The updates are in batches of 500 IDs from the web application. Instead of running the update statement 500 times, I created an IN clause and do the update.
Now this is timing out.
What are my optimum options ?
Thanks,

What is the web application doing where it needs to update 500 or more rows? How did you determine what the content list of the IN clause is?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

Look at the query plan, and see if it is scanning the clustered index or doing keyed lookups by joining to a table of constants. If it's a scan, let us know, and we can re-work the code to force lookups instead.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".