Source :
https://synametrics.com/SynametricsWebApp/WPTop10Tips.jsp
SQL optimizer heavily depends on indexes defined for a particular table. Indexes are double-edged sword: no
index will degrade performance of your SELECT statements and too many indexes will slow down your DML
(INSERT, UPDATE, and DELETE) queries. Therefore, it is important to have a right balance of index on tables.
Besides the number of indexes, fields that are involved and their order is also very important.
Tip 1 - Avoid functions on RHS of the operator
Often developers use functions or method with their SQL queries. Consider the following example.
select *
from Customer
where YEAR(AccountCreatedOn) == 2005
and MONTH(AccountCreatedOn) = 6
Note that even though AccountCreatedOn has an index, the above query changes the where clause such a way that
this index cannot be used anymore.
Rewriting the query in the following way will increase the performance tremendously.
Select *
From Customer
Where AccountCreatedOn between '6/1/2005'
Note: Why indexes have a negative impact on DML queries - When an index is created, the data for indexed columns is stored on the disk. When new rows of data are inserted
into the table or the values in the indexed columns are changed, the database may have to reorganize the storage
of the data to make room for the new rows. This reorganization can take additional toll on DML queries. However, if
you expect new rows on a regular basis in any table, you can specify the expected growth for an index.
Tip - Drop indexes before inserting data
Consider dropping the indexes on a table before loading a large batch of data. This makes the
insert statement run faster. Once the inserts are completed, you can recreate the index again.
If you are inserting thousands of rows in an online system, use a temporary table to load data.
Ensure that this temporary table does not have any index. Since moving data from one table to another is
much faster than loading from an external source, you can now drop indexes on your primary table, move
data from temporary to final table, and finally recreate the indexes.
