INSERT-ing to a table if the data does not already exist - how do indexes perform in this scenario?

Question

When you are in the position of inserting data into a table if the data does not already exist, what is the best way to go with indexes?

I'm specifically thinking of SQL Server here, but I assume the situation would be the same on other platforms.

On the one hand you are inserting new data into the table that will need to be added to the index at some performance cost. On the other hand you are reading the table to see if the data already exists, so the index will help performance.

My theory is that having an index in this situation would probably be better than to not have one as (from my limited experience) I have always found unindexed tables to perform terribly and insertions to indexed tables to be 'a bit slower' (obviously the type of index will have an impact on this).

My simple testing also suggests that indexing the table (a clustered index on 2 of the 7 columns) does improve performance, but then the table I tested on already contained 30M rows to start with (40-50M more are added by the insert).

I was just wondering if anyone can offer any insight into this scenario to help my learning.

Answer 1

A clustered index IS the table - so if the rows you are adding are already ordered there is not much work to do. A non-clustered index is an extra structure that you will have to maintain - so Inserts, Updates & Deletes will all have an overhead, the more indexes the higher the overhead. Generally, there are some selects for every table (otherwise what is the point of storing the data?), The balance between the improved SELECT performance versus the maintenance overhead can really only be determined for your particular workload - so measure twice & cut once!