How Do I Affect the Database Optimizer Behavior? (Magic xpa 4.x)

Normally when working with an SQL database, the database optimizer decides how to parse the statement and what index to use. Under most circumstances, the optimizer makes good decisions and the search is as efficient as possible.

However, built into the database optimizer is the ability to override the optimizer’s behavior. One does this by sending hints in the SQL statement. The exact coding of the hint will vary depending on what SQL engine you are using.

Magic xpa provides three different levels where you can code SQL hints.

• Database: You can code the hint at the database level, and the hint will be included in every Select statement for that database.

• Table: You can code the hint at the table level, and the hint will be included in every Select statement that is sent for that table.

• Index: You can code the hint at the index level, and the hint will be sent for every Select statement that is sent for that table and index.

At each of these levels, you will find the Hint property on the property sheet's (Alt+Enter) SQL tab.

In addition, if you are using Direct SQL in a task, you can code a hint there.