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.