SQL indexes

Mike McCormack

The SQL system I admin is for a lab and has only one user (or SSRS automation) querying at a time. Its updated continuously by an application via ODBC.

SO, when creating an index on a table, Don stated that the index gets updated whenever the indexed column is updated
.
Tell me if I have this right then.

I have ~100 "ItemID" fields, I am collecting a data value every 5 minutes for each of these ItemID's.
At the end of the month I query against only one of these ItemID's selecting all the values that are between a certain range. The result gives me a timestamp for all the values and using SSRS I create a trend and reports for this data.

There are no indexes at this time.

Since I only run this query once a month, even though the query takes several minutes to run, creating an index for ItemID would not be a good idea as every 5 minutes the index would be updated. The Query might be faster but the continuous load on the CPU would not be a fair tradeoff?

I get that each situation is unique and I would need to look at the system performance with the index created and not and look for a difference between the two options, but overall, is that what you were trying to convey?

Don Pezet

Mike,

You are definitely right in that each situation is different. What you described is correct: an index must be updated whenever its underlying data is changed. SQL can be tuned to make this immediate, run at time intervals, or run whenever a certain amount of changes have occurred. That is referred to as the "cost" of maintaining an index.

If the data doesn't change often, then it may still be worth it to have the index even if you aren't querying against it very often. However, you are updating your data pretty frequently so the cost of maintaining the index would exceed the savings on the one query. So you probably won't want an index in your scenario.

Now, here's where it gets tricky. It still might be worth it to do an index. If you have a requirement that the query complete in under 30 seconds, and an index allows it, then it is worth the CPU and I/O throughout the month to speed up your one query at the end of the month.

I hope that helps,

Don Pezet
Host, ITProTV

Mike McCormack

Thanks for the reply, there is no "requirement" to complete the query in any time frame, it runs in the middle of the night. But when troubleshooting, that long delay is a real pain to wait for and made me wonder about indexes. Glad to know I was correct in my observation, sad that there is no way to speed this up. Alas I'm hourly so I got that going for me.... :)