UPDATE slow when setting column to NULL
I have a SQL Server 2008 table with 80,000 rows and am executing the following query: UPDATE dbo.TableName WITH (ROWLOCK) SET HelloWorldID = NULL WHERE HelloWorldID = #helloWorldID HelloWorldID is an int and the #helloWorldID parameter is also int. The query is taking too long and I'd like to optimize it. I created a nonclustered index on HelloWorldID but it didn't matter. I may have to redesign this...maybe put the HelloWorldID on another table that links it to the TableName table?
Since the command you're waiting on is DELETE I have to guess that there is a trigger on dbo.TableName and that it is performing additional work that you do not expect. Or perhaps some CASCADE option that is affecting other tables that have triggers on them.
It all depends on how much rows will be updated by this query. If you're updating a lot of rows, say 30% of the table, then the index will actually slow down the query (as index will be updated along with the table, and it won't help with filtering the rows for update). Also ROWLOCK will slow it down, because the engine will issue a separate lock for each row (as opposed to pagelocks that would occur normally). Try removing the index and running this update using WITH(TABLOCK) just to see what happens.
I get this problem sometimes. Your query is dependent upon simultaneously getting a write-lock on every row in the table meeting the conditions of the WHERE-Clause . Depending on your needs for full 'ACID', you could do something like this: SELECT getdate() -- force ##rowcount=1 while ##rowcount > 0 UPDATE TOP (1000) dbo.TableName SET HelloWorldID = NULL WHERE HelloWorldID = #helloWorldID This will do the update is smaller chunks, and help overcome locking issues. But remember, this-method gives up on doing this-query as a single-transaction. You will need to tune the 1000 to a value that is right for your server.
SQL Agent Job to run .bat with gsutil rsync
Tool/script to clone SQL Server database with reduced rights (without backup functionality)?
SQL Server function with parameters
Bulk Load Data Conversion Error - Can't Find Answer
when i exec select on linux with odbc, hang on '__select_nocancel'
The system cannot find the file specified on Production server
An Error when trying to connect to SQL Server
Temporary table not created from dynamic query execution
How to process the data in the table, which is frequently inserted
Combine two columns and input the result in a different columns using SQL server
SQL Query to accept time difference
How to change my T-SQL query to overwrite a csv file rather than append data to it?
How to join multiple columns with only one foreign key using linq
Deleting millions on records while using log shipping - SQL Server
Restricting decimal places [duplicate]