COMMON SQL PROBLEMS

COMMON SQL PROBLEMS - 1

NOT USING AN INDEX:

 In most cases, an index should be used for each table in the query. Generally, when an index isn’t used the entire table is scanned. This is bad :>(
 Know what tables are indexed and how. Understand how/when indexes are used. [More slides on this.]
 Understand how certain predicate constructions prevent use of an index. [More slides on this.]
 Use ‘showplan’ to confirm expectations about index usage.
 If an apparently obvious index was not used…understand why.

COMMON SQL PROBLEMS - 2
 Avoid joining too many tables.
 Much depends on the indexes used and the efficiency of those indexes.
 Max is about 6-7 tables on IRF2, but only if joined properly on narrow clustered index keys.
 Avoid joining more than 3 really big tables (> 10^6 rows)
 Avoid excessively complex predicate
 It is easy to write predicates that prevent proper index usage. Avoid this. [More slides on this.]
 Avoid combining two or more special predicate statements like ‘GROUP BY’ with an aggregate and/or ‘SORT’ and/or ‘COMPUTE BY’ and/or ‘HAVING’ et cetera.
 Avoid multiple OR operators in predicates.
 Avoid more than one subquery in a predicate.

COMMON SQL PROBLEMS - 3
 Some properly formed queries legitimately ask the RDBMS server to do a lot of work and may take time to execute.
 Know the size of the objects in the query and try to understand how much work is being requested.
 Start queries off more simply with fewer tables and/or more simple or more restrictive predicates to develop a performance baseline.

COMMON SQL PROBLEMS - 4
 When using cursors, acquire a large stock of garlic, crucifixes and wooden stakes.
 Use cursors ONLY when absolutely necessary. There are always unpredictable performance consequences to the use of cursors.
 Never use cursors when ‘set SQL’ will suffice.
 If cursors must be used, be attentive to transaction blocking issues.
 Some cursors operations require specific types of indexes to support them (like unique or clustered).
 Keep it simple…never use cursors.

By
Don Madden & Jerome Roa
NSIT/CSI