UltraLite Database User's Guide
  Dynamic SQL

Optimization of SELECT statements

The primary goal of optimization is choose indexes so that data can be accessed in a convenient order ( GROUP BY and ORDER BY ) thus avoiding temporary tables or to access only the pertinent subset of a table when joining two tables. As a development aid, you can use the GetPlan method to obtain a character string (usually called a plan) that summarizes how a prepared statement is to be executed. Thus, 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

produces a plan

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

The plan indicates that the join operation will be 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.

In order to be usable on small devices, the optimization is not as extensive as that carried out in Adaptive Server Anywhere. The optimizer attempts to find sub-expressions in which a column (occurring by itself) is compared with expressions that involve only constants or columns from tables that occurred earlier in the FROM clause. Sometimes, you can rewrite a SELECT statement into a form that is equivalent but which executes more quickly. Often, this is known as syntax-directed optimization.