SQL Server Performance

improve performance of an update query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by pl800, Sep 1, 2011.

  1. pl800 New Member

    How can I improve performance of an Update Query?

    The query is as follows (with table and column names changed):

    UPDATE MyTable SET MyTime = 'Aug 25 2011 9:00AM' WHERE My_id = 'ABC1234' AND MyTime IS NULL

    The table has a few million records, and the query is updating a few thousand. The query completes in about 10s. The number of logical reads is in tens of thousands.
    I’ve added an index on MyTime, MyId hoping that the read part of the update will be much quicker.
    I think it now is, but the number of reads is still high and now the query causes an index update as well (so still a slow query).
    Note: SQL 2005 here, no filtered indexes.

    The query is executed about once per hour. So it takes data from HD instead of RAM every time.

    How can I improve the performance of the update query?

    Many Thanks.
  2. preethi Member


    Welcome to Forums.
    Few Questions...
    1. What is the clustered key of the table?
    2. What is the average row size of the table?
    3. You said that you have an index on MyTime +MyID. Is it non clustered? Is the order correct?
    4. I believe the data type of MyTime is datetime. What is the data type of My_id. Is it varchar? What is the defined maximum length and average length of this column?
    5. Can you get the execution plan and posted it here? Do you see Index seek/index scan operation with the Key lookup? in the far right position? Or Is it a Clustered Index Scan?
    6. On the statistics of the index, what is the average density you see (e.g. results of DBCC SHOW_STATISTICS ('SalesLT.SalesOrderDetail', IX_SalesOrderDetail_ProductID) WITH DENSITY_VECTOR)
    I hope that your answers may help me and others to give a better answer.
  3. pl800 New Member

    1.What is the clustered key of the table?
    None, but adding one on an additional int identity column did not speed up the query.

    2.What is the average row size of the table?
    494 bytes

    3.You said that you have an index on MyTime +MyID. Is it non clustered? Is the order correct?
    Yes, non-clustered, yes, the order is correct.

    4.I believe the data type of MyTime is datetime. What is the data type of My_id. Is it varchar? What is the defined maximum length and average length of this column?
    Varchar(8)

    5.Can you get the execution plan and posted it here? Do you see Index seek/index scan operation with the Key lookup? in the far right position? Or Is it a Clustered Index Scan?
    attached, note: index 1 is the one on My_Time, My_ID, index 2 is non-clustered unique on Name, My_Time, My_ID. I see a non-clustered index seek in the far right. No lookup.

    6.On the statistics of the index, what is the average density you see (e.g. results of DBCC SHOW_STATISTICS ('SalesLT.SalesOrderDetail', IX_SalesOrderDetail_ProductID) WITH DENSITY_VECTOR)
    All density Average Length Columns
    0.003246753 2.032282 My_Time
    0.0008149959 10.03228 My_Time, My_ID

    Thanks.
  4. pl800 New Member

  5. preethi Member

    I think you can consider these changes and see whether it improves the performance.
    1. Change the index to clustered index. (IF it is unique, you can make it primary key clustered.
    2. Consider you need My_ID as variable length column char(8) may fit better in this case.
    3. Please re-evaluate the order of the index. I feel that My_ID + My_Time may be a better option.
    Hope it helps.
    pl800 likes this.
  6. pl800 New Member

    Preethiviraj,

    Number 1 from your answer pointed me in the right direction. The solution is to have a clustered index in which My_Time and My_ID are the first two columns, and not have any other index. I've used the existing unique index: changed it to clustered and re-arranged the columns. I've dropped the other index. The result is a much simpler execution plan with only 1 step: clustered index update, and a strong reduction in logical reads.

    Thanks so much for your help!
  7. preethi Member

    You are welcome. Glad I was able to help!

Share This Page