Updating a SQL Server record in a large table

65 pts.
Tags:
INSERT statement
SQL 2005
SQL Database
SQL Server
We are using SQL Server 2005 and I have an SQL DB on it with 270,876 records in it.

There is a PRIMARY KEY defined on the first field called RecordID (Identity field).

The index name is PK_ListOfFiles_as_of_3-7-2011.

If I execute the following command from the Query Window on the server it takes a really long time to update the record (about 5 minutes).

UPDATE [ListOfFiles as of 3-7-2011] SET [DateDeleted] = '3-14-2011' WHERE [RecordID] = 3565

Even specifying the INDEX does not improve the performance:

UPDATE [ListOfFiles as of 3-7-2011] SET [DateDeleted] = '3-14-2011' FROM [ListOfFiles as of 3-7-2011] WITH (INDEX([PK_ListOfFiles_as_of_3-7-2011])) WHERE [RecordID] = 3565

The 'SELECT' command takes less than a second:

SELECT * FROM [ListOfFiles as of 3-7-2011] WHERE ([Pathname] = 'BETH HD RESTORED-DriveBethWedding Photos') AND ([Filename] = '1 (24).jpg') AND ([FirstName] = 'FName') AND ([LastName] = 'LName')

Do you have any idea how I can improve the performance of this UPDATE?



Software/Hardware used:
SQL Server 2005

Answer Wiki

Thanks. We'll let you know when a new response is added.

Have you looked at the execution plan? maybe there is a problem with the index.
Since you specified RecordID in the WHERE clausule SQL Server is using the PK index, regardless if you are specifying it or not.

Discuss This Question: 14  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Yes, looking at the execution plan for both queries should be your first step. Since the UPDATE and SELECT operations are using a different set of conditions, they are most likely using a different access path. On a table that size, even a full table scan should be fast, so maybe as Mario suggested, there is a problem with the index, and it needs to be reorganized or rebuilt. Take a look at this BOL article: Reorganizing and Rebuilding Indexes
    70,220 pointsBadges:
    report
  • BDW
    I have created the index both manually and via the following SQL statement: CREATE UNIQUE INDEX [PK__ListOfFiles_as_of_3-7-2011] ON [ListOfFiles as of 3-7-2011] (RecordID) INCLUDE (FirstName, LastName, Pathname, Filename)" In the case of 'manual' it was created after the table was populated. For testing purposes I created it first then populated the table. The result is the same either way. If it helps then the index is not 'clustered' (due to the INCLUDE statement).
    65 pointsBadges:
    report
  • BDW
    Additional information from the statement: UPDATE [ListOfFiles as of 3-7-2011] SET [DateDeleted] = '3/9/2011' WHERE RecordID = 3655 Query cost - T-SQL UPDATE - 0% Table Update - 75% Compute Scalar - 0% Index Seek - 25%
    65 pointsBadges:
    report
  • Denny Cherry
    You need to look as the cause of the wait in the sys.dm_os_wait_stats DMV. This will tell you why the query is running slowly. Odds are there is another operation which is blocking it.
    66,360 pointsBadges:
    report
  • BDW
    I searched all of the drives on the SQL server but I could not locate this file. Which folder is it in?
    65 pointsBadges:
    report
  • carlosdl
    That is not an external file, but a view.
    70,220 pointsBadges:
    report
  • BDW
    As you know there are all types of statistics in that table (in mine there are 201 different values in the 'wait_types' field). What should I be looking at to narrow down what is occurring?
    65 pointsBadges:
    report
  • BDW
    I found this great T-SQL statement out on the web (http://www.mssqltips.com/tip.asp?tip=1949) and it returns this: wait_type wait_time_s pct ------------- ----------------- ---- CKPACKET 9712.25 30.2 ASYNC_NETWORK_IO 4068.80 12.65 BACKUPBUFFER 2975.05 9.25 BACKUPTHREAD 2473.78 7.69 ASYNC_IO_COMPLETION 2288.27 7.11 LCK_M_IX 2150.63 6.69 SOS_SCHEDULER_YIELD 1803.67 5.61 WRITELOG 1399.16 4.35 LCK_M_S 477.38 1.48 Does this help?
    65 pointsBadges:
    report
  • Dk74
    isn't it index is getting rebuild after every update statement!
    10 pointsBadges:
    report
  • BDW
    So, how do I prevent this?
    65 pointsBadges:
    report
  • Denny Cherry
    dk74, Indexes are not rebuilt after every row change. The values within the existing index structer are updated with the new values. BDW, Based on the output from the query which you posted, your post wate type is CXPACKET which means that you are using parallel execution plans. This means that there are queries which are running which are very expensive and probably need to have indexes added to fix that. The next wait type is ASYNC_NETWORK_IO which tells you that your network is slow, and the SQL Server is waiting for data to transfer across the network. The next to are releated to backups which were running at the time. The next (ASYNC_IO_COMPLETION) one means that you were waiting for the disk drives. The LCK_M_IX shows that you are waiting to take a data modification lock. The SOS_SCHEDULER_YIELD means that an expensive query is being told to wait while less expensive queries run, this can usually be fixed by an index. Waiting for WRITELOG means that you are waiting for writes to happen to the transaction log. Either your storage is to slow, or your transaction log is very heavily fragmented. LCK_M_S is a shared data modification lock. Run your query again, and while it is running look at the waits view where the session_id column equals the spid of the session which is running the update statement. This will tell you why that query is running slow.
    66,360 pointsBadges:
    report
  • Kccrosser
    One other thing to check - is there a TRIGGER on the table? If an update is causing a trigger to fire, the trigger code may be the cause of the delay. Anomalous performance on what should be very fast transactions (like an update using the primary key) usually means there is something else in the system that is affecting the transaction.
    3,830 pointsBadges:
    report
  • TomLiotta
    I found this great T-SQL statement out on the web... Maybe this is more for my understanding than anything else; but as I read the SQL query in the article and interpret the output results, it seems to me that some 9 hours worth of time is being covered. Surely those stats aren't relevant only to the query that's showing the problem, are they? Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Ah, maybe that's the reason Mrdenny wrote "...look at the waits view where the session_id column equals the spid of the session which is running the update statement. " Never mind. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following