0

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

1
  • Your example mentioned you already have 30M rows. The question was if you are inserting into an empty table. Can be two different answers. – SqlWorldWide Apr 9 at 17:12
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!

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.