Poorly performing UPDATE statement
-
We have a simple UPDATE statement that is really chewing up CPU time (~8000 in profiler) The duration is also between (2000 - 4000 ms). Here are two versions of the statement:
exec sp_executesql N'UPDATE RX SET Expired = @Expired,
Last_Visit = @LastVisit WHERE PersonID = @PersonID AND CustID = @CustID',
N'@Expired datetime,@LastVisit datetime,@PersonID nvarchar(4000),@CustID nvarchar(4000)',
@Expired = 'Feb 8 2004 12:00:00:000AM', @LastVisit = 'Feb 8 2003 12:00:00:000AM',
@PersonID = N'02142496', @CustID = N'02035376'
AND 2nd version (cleaned up)
UPDATE RX SET Expired = 'Feb 8 2004 12:00:00:000AM',
Last_Visit = 'Feb 8 2003 12:00:00:000AM' WHERE PersonID = '02142496' AND CustID = '02035376'
Now some clarification. PERSONID is the Primary key for the table and has a Clustered index on it. Custid also has an index on it as well.
Not quite sure why it is taking such a long time. Any ideas on how to speed this thing up? Thanks for all of your help!
Adrian Porter
Adrian Porter -
Have the indexes been rebuilt recently? Stats updated? How many other indexes on the table? Does the table have triggers?
Andy
-
Wow, the site co-owner! Some great personal service here!
To answer your questions: First, we perform DBCC DBREINDEX twice a month, it was done on Tuesday night. Second, statistics are updated three times a week. Third, there are five indexes on the table, which has 6.6 million rows. And finally, there are no triggers on the table.Thanks again Andy, I'm constantly impressed with this site's attentiveness from the owners.
Adrian Porter
Adrian Porter -
Nah, Andy's not attentive he is just trying to catch me.
First is there an INDEX on Expired or Last_visit and how many records does this update?
Next have you tried looking at the execution plan to make sure is doing what would be expected?
Ex.
SET SHOWPLAN_TEXT ON
GO
UPDATE RX SET Expired = 'Feb 8 2004 12:00:00:000AM',
Last_Visit = 'Feb 8 2003 12:00:00:000AM' WHERE PersonID = '02142496' AND CustID = '02035376'
GO
SET SHOWPLAN_OFF
GO
Then look for things like table scans and wrong index choices. I would assume PersonID would be used but if CustID is more unique in total values then it may be choosen instead.
Check those first and post the execution plan here if need help with it.
-
Hey, I was ahead for a long time!
Thanks for the feedback. We try to stay involved every day. Used to be we'd comment on almost every question posted, volume grew to the point that we couldnt keep that up, so now we jump in if its interesting, still needs an answer, or looks to be off track somehow. Most weeks Steve and I will have an article up too. Hope you visit often and continue to find value.
Andy
-
Antares and Andy,
Here's the Execution Plan:
|--Clustered Index Update(OBJECT:([800Contacts].[dbo].[RX].[PK__RX__63D8CE75]), SET:([RX].[expired]=[Expr1004], [RX].[LAST_VISIT]=[Expr1005]))
|--Top(1)
|--Compute Scalar(DEFINE:([Expr1004]=Convert([@1]), [Expr1005]=Convert([@2])))
|--Index Seek(OBJECT:([800Contacts].[dbo].[RX].[IX_CUSTID]), SEEK:([RX].[CUSTID]=[@4] AND [RX].[PERSONID]=[@3]) ORDERED FORWARD)
To answer your questions, only one row is updated since PERSONID is the PK. Expired and Last_Visit do not have indexes. Since PERSONID is the PK and has a clustered index on it, would it be of benefit to remove the custid reference? Expired and Last_visit are also datetime fields if that is of any assistance.
Thanks again for your help!
Adrian Porter
Adrian Porter -
quote:
Since PERSONID is the PK and has a clustered index on it, would it be of benefit to remove the custid reference?If it is a PK then the value is unique. Therefore you can only find one match so yes, at least try without the CustID and see if that helps.
It should when looking here.
quote:
|--Index Seek(OBJECT:([800Contacts].[dbo].[RX].[IX_CUSTID]), SEEK:([RX].[CUSTID]=[@4] AND [RX].[PERSONID]=[@3]) ORDERED FORWARD)Looks like it used this index IX_CUSTID and performed a seek instead of looking at PersonIDs clustered index.
One question there though, query engine is supposed to pick the index with the highest density. Does this index containt both CustID and PersonID with PersonID first per chance? If so you reall don't need to do that as CustID index will contain pointer info to the PersonID clustered index with the values there are relative and it seems to be causing the wrong index to be used.
-
Nope, IX_CUSTID is only on Custid. I'll have the programmers remove the custid reference and see if that will help anything. I'll post the results on Monday.
Adrian Porter
Adrian Porter -
We tried removing the custid reference which did not make much of a difference. I ended up putting it into a Stored Procedure and it dropped off the charts. I was really impressed with how much of a difference it made on such a simple UPDATE statement.
Adrian Porter
Adrian Porter -
Compiling can take a long time. It is indeed wise to place fixed or parametrized data-handling queries in a stored procedure.
I wonder if compiling time reduces when reindexing the Master database.
To answer your questions: First, we perform DBCC DBREINDEX twice a month, it was done on Tuesday night. Second, statistics are updated three times a week. Third, there are five indexes on the table, which has 6.6 million rows. And finally, there are no triggers on the table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply