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 un-indexed tables to perform terribly and insertions to indexed tables to be 'a bit slower' (obviously the type of index will 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. Thanks