iAnywhere.com - Query optimization

Query optimization

There are many different ways for UltraLite to execute any query. Each distinct way of executing a query is called a plan. In UltraLite a plan is defined mainly by the order in which tables are accessed and by whether each table is searched using an index or by scanning the rows directly. For some queries, there can be an orders of magnitude difference in execution times between efficient and inefficient plans.

UltraLite includes a query optimizer: an internal component of the UltraLite runtime that inspects alternative plans and attempts to select an efficient one. The primary goal of optimization in UltraLite is to choose indexes so that data can be accessed in an efficient order. The optimizer attempts to avoid the use of temporary tables to store intermediate results and attempts to ensure that only the pertinent subset of a table is accessed when a query joins two tables.

UltraLite optimizes queries automatically. The main area in which you can tune execution time is by creating indexes in your database that UltraLite can exploit as it optimizes queries.

Inspecting query plans

As a development aid, you can use the UltraLite Interactive SQL to display the plan that summarizes how a prepared statement is to be executed. The plan is displayed on a tab in the bottom pane of the utility. You can choose whether to display a plan graphically or in a text format.

For example, the statement

SELECT I.inv_no, I.name, T.quantity, T.prod_no
FROM Invoice I, Transactions T
WHERE I.inv_no = T.inv_no

might produce the following plan:

join[scan(Invoice,0),index-scan(Transactions,1)]

The plan indicates that the join operation is accomplished by reading all rows from the Invoice table (following index[0]) and then using the index[1] from the Transaction table to read only the row whose inv_no column matches.

For more information about UltraLite Interactive SQL, see The UltraLite Interactive SQL utility.

Overriding the optimizer

To be usable on small devices, query optimization in UltraLite is not as extensive as that carried out in Adaptive Server Anywhere. You can override the table order it selects by adding the OPTION (FORCE ORDER) clause to a query, which forces UltraLite to access the tables in the order they appear in the query. This option is not recommended for general use. If performance is slow, a better approach is usually to create appropriate indexes to speed up execution.