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?
SQL Server 2005
March 16, 2011 1:42 PM
March 22, 2011 10:09 PM