Other important SQL tuning hints | Practices to be avoided

Retrieve only the information that is needed

• Select only the columns that you need, not all the columns in a table.

• Never use the SELECT * FROM table. If the column order changes or columns are removed or added to a table, then your results will be unpredictable.

• If a large result set is expected, restrict the number of rows returned with the FETCH FIRST n ROWS clause.

• If a small result set is the expectation, use OPTIMIZE FOR n ROWS to help optimization.

• If the FETCH FIRST n ROWS clause is used, consider also using the OPTIMIZE FOR statement.

• In GUIs that allow search criteria to be entered by the user, consider forcing a minimal number of characters to be entered for pattern matching. (This will avoid problems where a user requests product information for all items that contain the letter a in the description.)

• Avoid the use of a leading wildcard in predicates, since this will require a table scan.