Menu
I am maintaining a pretty sizable application and database and am noticing some poor database performance in a few of our stored procedures.
I always hear that "adding an index" can be done to help performance. I am certainly no DBA, and I do not understand what indexes are, why they help, and how to create them.
I basically need an indexes 101.
Can anyone give me resources so that I can learn?
821
Indexing makes columns faster to query by creating pointers to where data is stored within a database. Imagine you want to find a piece of information that is within a large database. To get this information out of the database the computer will look through every row until it finds it.
Indexes. The query optimizer uses indexes to speed up the execution of a query. The optimizer uses existing indexes or creates temporary indexes to generate an execution plan when preparing a SELECT, INSERT SELECT, UPDATE, or DELETE statement. An index is a map of keys to row locations in a table.
An index is a database structure that you can use to improve the performance of database activity. A database table can have one or more indexes associated with it. An index is defined by a field expression that you specify when you create the index. Typically, the field expression is a single field name, like EMP_ID.
As a rule of thumb, indexes should be on any fields that you use in joins or where clauses (if they have enough different values to make using an index worthwhile, field with only a few possible values doesn't benefit from an index which is why it is pointless to try to index a bit field).
If your structure has formally created primary keys (which it should, I never create a table without a primary key), those are by definition indexed becasue a primary key is required to have a unique index on it. People often forget that they have to index the foreign keys becasue an index is not automatically created when you set up the foreign key relationsship. Since the purpose of a foreign key is to give you a field to join on, most foreign keys should probably be indexed.
Indexes once created need to be maintained. If you have a lot of data change activity, they can get fragmented and slow performance and need to be refreshed. Read in Books online about indexes. You can also find the syntax for the create index statement there.
Indexes are a balancing act, every index you add usually will add time to data inserts, updates and deletes but can potentially speed up selects and joins in complex inserts, updates and deletes. There is no one formula for what are the best indexes although the rule of thumb above is a good place to start.
50
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
