Optimize simple update on table

Question

Hi All,

I am running following query in a stored proceudre and it is taking 30 milliseconds to execute. Can anyone help me out to optimize this query:

Table Definition is:

Create Table Customer
(
   CustID int not null auto_increment,
   CustProdID int,
   TimeStamp DateTime,
   primary key(CustID)
);

Update Customer
Set TimeStamp = InputTimeStamp (stored procedure's Input)
Where CustID = InputCustID (stored procedure's Input)
and CustProdID = InputCustProdID; (stored procedure's Input);

Also besides primary index this table has simple index on custprodID column.

Thanks in advance. Regards, Manasi

Answer 1

I'm not sure there's really much you could change here while maintaining the same functionality; it's a very basic query.

Answer 2

My guess is that the time isn't spent in the stored procedure but in the code which calls it (plus the network overhead, etc).

Answer 3

How are you measuring the 30ms? If by running the procedure once, the result will be swamped by overheads of running the test. Put it in a loop and do it say 1000 times, then divide the result by 1000, what do you get then?

Answer 4

When you say 30 ms, is that cpu time or overall performance time? Do you know how many records are being updated (I am guessing a small number based on the example, but not sure). Also, does the table have a trigger on it, so more work is being done than meets the eye...

I agree, this is a simple query, so not a lot to optimize it. You might want to put a clustered index on the two fields, but I don't know if that is appropriate for your application.

Answer 5

You could try and make use of a leftmost prefix index on (CustID, CustProdId).