Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

update sql and can't view record

Status
Not open for further replies.

jepatte

Programmer
Jul 4, 2005
75
US
I sometimes do a mass update in Query Analyzer.
ie. Update t1 Set f1 = 'Y' WHERE f1 = 'Yes'
If this is a very large dataset and can take 3 or 4 minutes, my web application that reads this same table can't view a single record while this update is going on.

It will timeout if I just want to query a single record in this table based on the primary key. Is there some way to run that update and allow others to view records while the update is occuring?
 
If uncommitted reads won't screw up web app business logic then try NOLOCK hint on SELECT statement.

Otherwise - speed up UPDATE. :)

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up
[banghead]
 
If no lock doesn't do it for you, try:
running smaller batches (there's an FAQ on how to do this)
looking at the update code to see if it can be tuned (for instance are you updating every record when you don't need to? Maybe adding a where clause could help.
Look at indexing.
Scheduling the updates during non-peak hours

"NOTHING is more important in a database than integrity." ESquared
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top