Menu
How can you determine if the performance gained on a SELECT by indexing a column will outweigh the performance loss on an INSERT in the same table? Is there a "tipping-point" in the size of the table when the index does more harm than good?
I have table in SQL Server 2008 with 2-3 million rows at any given time. Every time an insert is done on the table, a lookup is also done on the same table using two of its columns. I'm trying to determine if it would be beneficial to add indexes to the two columns used in the lookup.
983
If you update a table, the system has to maintain those indexes that are on the columns being updated. So having a lot of indexes can speed up select statements, but slow down inserts, updates, and deletes.
Then yes an index on customer_id will definitely increase the speed since it will find the row to update much quicker. But for improvement, if you have columns (id,customer_id,balance) and customer_id is unique and id is just an auto incremented column get rid of the id column and make customer_id the primary key.
SQL index is considered as one of the most important factors in the SQL Server performance tuning field. It helps in speeding up the queries by providing swift access to the requested data, called index seek operation, instead of scanning the whole table to retrieve a few records.
The number of indexes on a table is the most dominant factor for insert performance. The more indexes a table has, the slower the execution becomes.
Like everything else SQL-related, it depends:
The only real way to know is to benchmark it. Put the index(es) in place and do frequent monitoring, or run a trace.
116
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
