SQL Server Performance

why update one row is so slow?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by gavin, Aug 10, 2007.

  1. gavin New Member

    A table has 200000 rows.When I update one row with primary key, it cost 0.2s. I checked the excute plan, and found something can not be understood.
    In the excute plan. the clusted index update cost 1%, but before clusted index update there is a nested loops(inner join).seems constant scan->compute scalar then clusted index seek.The clusted index seek cost 99%. It reads 2159 pages. I found the order of where clause in the clusted index seek is different from the order of primary key. Why???Maybe that is the reason that result of full scan.
    Because I use the primary key to update, I think the clusted index update is enough. What is the use of constant scan and compute scalar?
    Hope get some help from you. Thanks.
  2. thomas New Member

    Is the clustered index on the primary key? It may help if you post the table creation script plus the indexes and your update statement.
  3. Adriaan New Member

    Gavin, please do not double-post.
  4. gavin New Member

    I have poseted the table scripts and stmt text.
    [quote user="Adriaan"]
    Gavin, please do not double-post.
  5. Adriaan New Member

  6. gavin New Member

    When I update a row, The PK itself is not changed. Is that the problem?
  7. Adriaan New Member

    If you have a query that does a clustered index seek, then SQL Server is able to use what is actually the most efficient technique it has at its disposal.
    The execution plan assigns an extremely high percentage to this highly effective technique in the execution plan, meaning that the other bits and pieces that the server needs to do to run the query actually take up very little time and effort.
    Your query cannot be improved.[;)]
    Note that one-row queries are not truly indicative for performance.
    I should add that if you have a multi-column clustered index, and the order of the columns is poorly chosen, then a clustered index seek may be a less-than-ideal technique for your query.
  8. Adriaan New Member

    I keep forgetting to put in the most obvious comment: updates cost time - they're a relatively slow operation, just like deletes.
  9. gavin New Member

    When I update by statement(sql literal) rather than prepared statement.It cost less than 0.001s.
    It seems that the sqlserver query optimizer did the bad choice when executing by prepared statement.
    And too bad! Only update one row ,but choose full table scan!
    Is it a bug?
  10. Adriaan New Member

    Sorry for not reading closely enough before - you mention "The clusted index seek cost 99%. It reads 2159 pages." - and I missed the last bit, because with a proper index you should get at any single row with a lot less reads. Looks like you do not have the appropriate index for the filter values that you're dealing with.
    So check the clustered index on your table t_risk_scenario_critical_resource_path - is it on an identity column, or is it a composite index?
    If it is a composite index, then see which is the most critical of the columns that you're filtering for (ProjectID, ProductVersion, RiskID, DutiesID, ResourcePathID). If there is one main FK, to which you've added other columns to make the child PK, then the FK probably needs to be first - the rest must be ordered left to right going from the column with the most repeating values, to the one with the least repeating values.
    If the clustered index is on an identity column, then check the available indexes on the same columns, with the same logic.
  11. gavin New Member

    Adriaan :
    Thanks very much for your reply.Thanks very much for your help.
    But I still have my question.The primary key for the table is a composite key. And sqlserver will automatically create a clustered index on the primary key. See the update sql I mentioned, the filter cloums are just the primary key. So should I create another index? I mean the sqlserver's query optimizer should know this and find that row directly. But in fact a full scan is executed ( I checked the total pages of this table and the total pages read while executing update, thay are the same).
    The problem happens only in case of using preparedstatement(JDBC) to execute update. When I try turn to use statement(JDBC) to execute update, everything is OK. Execute update are very fast, the execute plan is simple( only one row) and only read 3 pages. That puzzled me. It seems the sqlserver query optimizer do not support prepared statament very well. And obviously use statement can get better performance than prepared statement while prepared statement was suggested in lot of books and articals I have read.
  12. Adriaan New Member

    Think of a dictionary where each entry is ordered not by the initial character, but by the second character. So if you're looking for aBsolute, you'll have to look under B. And for aSsortment, you have to look under S. This is not a very helpful index if you're trying to find all words that start with the letter A - there are no shortcuts, and you have to look through the entire dictionary. Then again, you might just get lucky and find your word early on.
    A PK is not always created as a clustered index. If you do not mention clustered or non-clustered when you define the table, and there is no other index on the table defined as clustered, then the PK is by default a clustered index. If you create the PK as non-clustered, or if you already have another clustered index on the table, then the PK is not clustered.
    No idea why using a JDBC driver would change any of this behaviour.
  13. Expansion New Member

    I don't know much about prepared queries or the table structure in this case.
    But I can imagine as the primary key is composite, the query has to use two parameters. Maybe the JDBC prepared statement is using a cached queryplan for an older parameter value for which a nonclustered index on one column was rather selective.
  14. satya Moderator

Share This Page