I'm working in Sql Server 2005. I have an event log table that tracks user actions, and I want to make sure that inserts into the table are as fast as possible. Currently the table doesn't have any indexes. Does adding a single non-clustered index slow down inserts at all? Or is it only clustered indexes that slow down inserts? Or should I just add a clustered index and not worry about it?
+3
A:
Indexes, clustered or non-clustered,will always slow down inserts as SQL has to maintain both the table and index. This slowdown is in an "absolute" sense and you may not notice it. I would add whatever indexes are necessary to retrieve your data.
Josef Richberg
2010-05-24 21:20:30
Assuming that retrieval is more important than speed of writes... let's say your capturing data from a UDP stream and any slowdown in writing means a loss of packet capture... but you'll only need to query in the invent of contact from ET...
Stephanie Page
2010-08-10 23:23:17
A:
Yes, any index will take a little bit of time to keep up to date when doing INSERT, UPDATE, DELETE operations. The more indices you have, the more time we're talking about.
But ultimately it depends on what's more important to you - good query performance (then add indices as needed), or good insert performance (then have as few indices as possible).
Which operation do you perform more often??
marc_s
2010-05-25 05:05:56