By default, the driver requests the default optimization class. Most SQL statements are adequately optimized using the default optimization class, but you may want to use a different optimization class to improve performance. To specify an optimization class, set the CurrentQueryOptimization property.
In general, the lower optimization classes are better for simple or short-running SQL statements. The higher optimization classes consider more alternative query plans, which may benefit complex or long-running SQL statements, but they can incur significantly more compilation time, particularly if the SQL statement accesses multiple database tables. Benchmarks can help you determine if a better query plan has been generated for a SQL statement.
Consider the following general guidelines when choosing an optimization class:
Start by using the default optimization class (class 5).
If you want to try another optimization class other than the default, try class 1, 2, or 3 first.
Use optimization class 1 or 2 if you have multiple tables with multiple join predicates on the same column, and if compilation time is a concern.
Use a lower optimization class (0 or 1) for SQL statements that have short run times. These SQL statements often have the following characteristics:
◦ Access a single table or only a few tables
◦ Fetch a single row or only a few rows
◦ Use fully qualified and unique indexes
Use a higher optimization class (3, 5, or 7) for SQL statements that have longer run times of more than 30 seconds.
Complex SQL statements may require different amounts of optimization to select the optimal query plan. Consider using higher optimization classes for SQL statements that have the following characteristics:
◦ Access large tables
◦ Contain multiple nested queries or joins
◦ Contain multiple set operators (UNION, for example)
◦ Return multiple rows that match the search criteria
◦ Contain GROUP BY and HAVING clauses
◦ Contain nested table expressions
Use class 9 only if you have extraordinary optimization requirements for a SQL statement.