Clever Geek Handbook

To index or not to index

I have a database that I specifically used to log user actions. The database contains several small tables dedicated to specific types of activities. This data is rarely viewed, but the number of table rows begins to increase by several million. I didn't notice much slowdown, but I want to know if the table will be indexed for lookup, which will hinder or help the performance of inserts. Inserts are done all the time, but searches are not done as often and the tables just keep growing.

Should I create indexes on these tables? Why or why not?

0


source to share


4 answers


It all depends on your empirical research. Take your copy of the database to a different environment and run the profiler during search and insert with and without indexes. Measure the performance and see what helps. :)



+4


source


Instead of indexes, I think you should think about the absence of indexes on the table you are inserting rows into, and then replicate the tables (and possibly apply indexes) for use specifically for queries.



+3


source


As Ray says , it all depends on the situation and the only way to tell is to try it under load.

From a theoretical point of view: yes, adding indexes to a table slows down the inserts, because the DBMS must maintain all indexes with every insert. But will you notice? Will the observed performance matter? Probably no. Indexes are usually stored in B + Tree structures that can be inserted in O (log n), which is not bad, not to mention all the disk caching, etc. So the only way to know for sure is to try it both ways and see what the difference is.

+2


source


I'm not an expert in Sql Server, but I worked with a Microsoft Senior Engineer on the performance of one of our systems. According to him, the way MSSS finds a page to insert a new line is by "Scanning free space" ... scanning every page that looks for a page with space to insert a row.

If you add a clustered index to a table, you are forcing the data to move to one specific location. Let's say you add an autonomic column to this table and make it a clustered index. Now MSSS does not scan a free block, it knows that 1000 MUST comes right after 999; so it does index walk a.

I would give it a shot. Don't try 4 or 5 million lines for too long.

0


source






More articles:


All Articles