Proper Use of SQL Indexes.
Proper indexing of your SQL tables can greatly improve performance. I have seen queries literally go from taking an hour, or more, on very large tables, to completing in well under a minute.
The reason is, that if you are selecting by a field that is not indexed, the SQL server may need to read the entire table to determine what records to select. This applies to joins, in particular. If you are joining TABLEA to TABLEB, and the field in TABLEB that you are using for your join condition is not indexed, the number of logical records that need to be evaluated can be huge!
By indexing a table, when the indexed column is the target of the WHERE condition, or the JOIN field, your SQL server uses the index to find and evaluate only the records that it needs. If your WHERE condition results in, say, ten records out of a table of a million records, the SQL server only needs to access those ten records, not the entire table. The savings in processing is obvious.
As with most things, there is a trade off with indexing, however. As the indexes need to be kept up-to-date when records are inserted, updated, and deleted, the index tables must be updated when the main record is. So, indexing on every column is not the way to go.
Rather, evaluate your slow queries, and see which would benefit from indexes. In particular, look at your very large tables, and see what columns are referenced by these slow queries in the WHERE clause, and in the JOIN clause. These are the columns that are the prime candidates for being indexed. Either individually, or, as an index may be multi-column, in combination. In other words, if the query has a WHERE clause referencing the STORE_NO column, and the INVOICE_DATE column for example, adding an index on both together (STORE_NO,INVOICE_DATE), would be a major candidate.
There are some tools, such as the SQL Performance Dashboard, that can be a great aid to evaluating the need for indexes. That will be covered in a future blog.
Dave.