15

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute.

While tuning my database to improve performance, I keep coming across this situation that seems to contradict that rule logically for me, and nowhere can I find anyone say or explain in any way otherwise.

In SQL Server, and I believe/presume most other DBMS, your indexes are created based on specific columns you specify. Inserts and deletes will always affect an entire row, so there is no way they will not affect the index, but updates seem a bit more unique, they can specifically affect only certain columns.

If I have columns that are not included on any index and I update them, are they slowed down just because I have an index on other columns in that table?

As an example, say in my User table I have one or two indexes, the primary key which is an Identity/Auto Increment column, and possibly another on some foreign key column.
If I update a column without the index directly on it, like say their phone number or address, is this update slowed down because I have indexes on this table on other columns in either situation? The columns I am updating are not in indexes, so logically, the indexes should not be updated, shouldn't they? If anything, I would think they are sped up if i use the indexes in the WHERE clause.

  • so there is no way they will not affect the index except for filtered indexes... – usr Jan 7 '16 at 23:23
  • I think of the non-covered, non-clustered index as containing pointers to records (typically in the table's clustered index leaf nodes). I would think that one situation to cause a slow-down during an UPDATE (of a not-included attribute) might be a situation where the UPDATE caused the record to move within the clustered index. I'm still not sure if a movement would cause the pointer to change, OR if the pointer is simply a KEY value into the clustered index, in which case the possible location update wouldn't matter because the system would just do a KEY lookup to get the record value. – Jmoney38 Aug 24 '17 at 13:14
5

You are correct that updating an non-indexed column will not cause changes to the indexes. In a simple case, there would also be no overall impact on the table.

If a query can use the Index to look up data, it may speed up the lookup, but the exact behavior (depending on your SQL brand) may differ from other brands of SQL. (I use Microsoft SQL Server primarily.)

Of course, updating a column with a significantly greater volume of data could cause some moving of rows to different pages, et cetera.

  • 1
    SQL Server is mentioned in the OP, I added a tag, so I think you can assume SQL Server – Tom V Jan 5 '16 at 19:54
10

For a relatively fast modern system the addition of a single index to an OLTP table will probably be virtually undetectable from a performance standpoint for the vast majority of systems. That said, you should not create needless indexes, and you probably should not create single-column indexes for every column in a table.

You are correct in the assumption that for many queries the presence of useful indexes will result in a very noticeable speed improvement.

Although your question appears to be around performance, there are several other potential issues around adding indexes, including but not limited to:

  1. Time required to create the index may result in blocking while the index is added to the table. The lock is very short lived, and most likely won't create a big problem.

  2. Index changes result in execution plans being invalidated for any plans that reference the underlying table. When those execution plans are recompiled, performance may change negatively for some queries.

  3. Index modifications may result in queries returning errors where none were previously returned. Take the case of a filtered index that was used to return dates contained in a varchar field; if the filter eliminated any rows that weren't dates, and that filter is subsequently changed, queries that relied upon that index may now fail when attempting to convert non-date data.

  4. A new index may cause the order of execution to change resulting in possible deadlocks occurring where non occurred before.

  • "The code path required for an update when the index is not going to be affected still needs to be evaluated" this is not true. The compilation/optimization phase will know very well what indexes need to be updated, if any, and will create the plan accordingly. An UPDATE statement that does not modify (declare in the SET list) columns in an index (including INCLUDE and clustered key columns) will not have to update that index, and the execution phase will not even touch it. DELETE and INSERT obviously touch all columns (logically) and have to update all indexes. – Remus Rusanu Jan 5 '16 at 20:49
  • @RemusRusanu but won't it need to be evaluated if the index could be used to locate the rows that need to be updated? – Tom V Jan 5 '16 at 20:53
  • @RemusRusanu - I suppose once the QO has compiled a plan, no more CPU is needed; however to get the plan compiled it certainly needs to do that. If plans are getting compiled frequently it might make a very slight difference. – Max Vernon Jan 5 '16 at 21:58
  • @TomV using the index to locate the delete/update candidate row(s) is an entirely different topic. If that's the case, the advantages of locating the rows via an index should overwhelm any index maintenance cost issues. – Remus Rusanu Jan 6 '16 at 8:43
  • @MaxVernon I would argue that there is no valid scenario of frequent recompilations of DML (UPDATE). I buy some cases for valid (unavoidable?) recompilations for ad-hoc querying. But DML? What kind of app could create ad-hoc, unique UPDATE statements? Frequent recompilations with DML cry out loud "Parameterize me". – Remus Rusanu Jan 6 '16 at 8:46
-2

If the update operation targets a non-indexed column of fixed size (like an integer), it shouldn't be slow generally speaking, but compared to a select statement, the update has to eventually be written on the slow disk as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.