Performance Tuning – Good For Developers – Part 1

Performance Tuning – Part 1

1. Indexes improve select performance.

2. While writing queries few things must be considered – Use operators =,>,<,>=,<=,like,between. 3. Replace the BETWEEN with >= and <= operators because BETWEEN in turn converted to mentioned oprators. So with we can reduce one step.

4. Don’t use functions in the WHERE clause. For e.g. select col1 from Table1 where upper(col1) = col2 — If we are having index created on columns col1 and col2 then because of function query engine will not use the index in select operation.

5. Similarly don’t use the mathematical expression in WHERE clause. For e.g. select col1 from Table1 where (col1 * 3) = col2

6. One of the most common pitfall is mismatch in datatype on both side of join. for e.g. select col1 from table1 where col1=col2 — if datatype of col1 and col2 are different then it reduces the performance of query.