Geek Answers Handbook

SQL Server 2008 Indexes - Query Performance Improvement vs. INSERT / UPDATE Loss

How to determine if the performance obtained in SELECT indexing a column outweighs the performance loss by 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 a table in SQL Server 2008 with 2-3 million rows at any given time. Each time an insert is performed in a table, a search is also performed in one table using its two columns. I am trying to determine if it would be useful to add indexes to the two columns used in the search.

+7
source share
3 answers

Like everything related to SQL, it depends on :

  • What fields are they? Varchar? Int? Datetime?
  • Are there other tables in the table?
  • Do you need to include additional fields?
  • What is a clustered index?
  • How many rows are inserted / deleted in a transaction?

The only real way to find out is to compare it. Put the index in place and do frequent monitoring or run tracing.

+4
source

It depends on your workload and your requirements. Sometimes data is downloaded once and read millions of times, but sometimes not all downloaded data is ever read.

Sometimes reading or writing must be done at a specific time.

0
source

case 1: If the table is static and is queried to a large extent (for example: the table of elements in the Cart application), then the indexes in the corresponding fields are very useful.

case 2: If the table is very dynamic and many queries are not executed daily (for example, the log tables used for audit purposes), then the indexes slow down the write.

If the above two cases are boundary cases, then to create indexes or build indexes in a table depends on in which case the table above in the conflict is most suitable.

If you do not leave it until the decision of the consultant to configure the request. Good luck.

0
source

More articles:


All Articles