sql-server


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.

Related Links

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
Informatica powercenter
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]

Categories

HOME
ssas-2012
admin-on-rest
wsdl
visual-studio-2013
formal-verification
parse-server
branch.io
spring-cloud-contract
proguard
codeblocks
subquery
ng-admin
octopus-deploy
cortex-a
postmessage
ejs
redux-observable
arraylist
ada
internet-explorer-8
backpropagation
uiautomator
ghost-inspector
kitematic
spring-data-neo4j
cortex-m3
shapes
google-drive-realtime-api
threshold
color-scheme
forecasting
socketpair
haskell-pipes
active-model-serializers
react-native-router-flux
microdata
android-maps-v2
homekit
lftp
crop
jdbi
cppunit
amd
openweathermap
nsjsonserialization
mouseclick-event
azureportal
dwarf
openh264
ticker
modelattribute
in-memory-database
mercury
python-hypothesis
sqldatareader
gulp-typescript
rsqlite
c64
gameanalytics
try-finally
uiprogressview
kendo-combobox
fill
handlebars.java
cmocka
void
oai
ipojo
vine
leadtools-sdk
digits
crash-dumps
bridge
cpu-cores
jcr-sql2
kallithea
tcpreplay
csplit
alpha-transparency
voldemort
magic-numbers
winrt-httpclient
healthvault
bsod
facebook-chat
sim900
cilk-plus
layered
entity-framework-4.1
surveyor-gem
fortran77
database-permissions
process-monitor
code-conversion
sequelpro
git-filter-branch
isolatedstorage
text-services-framework
supersized
flexicious
selectonemenu
galaxy-tab
printing-web-page
adobe-contribute
gethashcode
sitemappath
pci-bus
graph-layout
soft-keyboard
hardware-infrastructure
dotproject

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App