Interpreting the Execution Plan

Relationship Between Query, Statement, and Plan

This example of a simple join between two MBOs or tables illustrates the relationship between the SUPQuery/Query, generated SQL statement, and the execution plan. The column names may be different from the attribute names, depending on the version of the client SDK used. The table with the suffix “_os” stores the original state of the MBO and eliminates duplicate rows if the object is in pending state.

The SUPQuery:

Query query = new Query(); 
query.select("p.PRODUCTID, p.TYPE, t.DESCRIPTION");     
query.from("PRODUCTS", "p"); 
query.join("PRODUCTSDESCRIPTION","t","t.PRODUCTID","p.PRODUCTID");

The SQL statement generates:

select p."a",p."b",t."c"
from "slowquery_1_0_productsdescription" t,"slowquery_1_0_products" p
where p."a"= t."a"
 and (t."pending" = 1 or not exists (select 1 from "slowquery_1_0_productsdescription_os" t_os where t."d" = t_os."d"))
 and (p."pending" = 1 or not exists (select 1 from "slowquery_1_0_products_os" p_os where p."p" = p_os."p"))

Execution Plan

According to the plan below, the join is implemented using a table scan of the "productsdescription" MBO. It attempts to look up a row for each row on the "products" MBO using the index:

slowquery_1_0_products_findByPrimaryKeyIndex

The filter and subquery operations work together to limit the number of rows returned by the scan to consist only of the pending (if there is one) or download state. Read-only MBOs do not require filtering since there is only one state – download.

Note: The column names that appear in the SQL query are short names. The mapping between the attribute name and the short name is in the generated code. In the internal directory of the generated code, there is a source file that embeds the metadata information for each MBO. For example, in iOS, the file is in the form <Package><MBO>MetaData.m. For previous releases, the metadata information is generated only if you select the Generate Metadata option during code generation.

join
     [
      filter[scan(slowquery_1_0_productsdescription)],
      filter[index-scan(slowquery_1_0_products,
                          slowquery_1_0_products_findByPrimaryKeyIndex)]
     ]
 sub-query[index-scan(slowquery_1_0_products_os,primary)]
 sub-query[index-scan(slowquery_1_0_productsdescription_os,primary)]

Table Scans

Small table scans can be quite efficient; however, avoid large table scans as much as possible due to the linear cost. For attributes in a WHERE clause, avoid a table scan by adding appropriate indexes. UltraLite uses direct page scans when it is more efficient to access information directly from the database page, however, such results are returned without order. The object query must use ORDER BY to guarantee ordering of result sets.

Joins

Join operations are common in a relational database. However, too many joins within a query, especially queries involving tables with many records, are very expensive to perform. Because of the limited resources associated with the device database, dividing a query into two or more subqueries provides better performance. Since the database engine is single threaded, allowing another application thread to process the result of a subquery while the application thread in the database engine works on another query in parallel may provide better performance in a multicore environment. This might involve additional work for the application developer, but may also allow complex queries to execute with reasonable performance.

Also, for a join operation, the query optimizer does not use table size information or gather statistics to decide which table to scan. The table order in the SQL statement (derived from SUPQuery/Query) determines the table to scan. Therefore if you are joining a very large table with a small one, ordering is important to limit the amount of work the engine must do.

Temporary Tables

Avoid using temporary tables, if possible, since they are fairly expensive. There is a large discrepancy between read and write speed for flash-based memory and storage. When using UltraLite databases with temporary tables:

In general, the optimizer always tries to avoid creating temporary tables to return query results, because the entire temporary table must be populated before the first row can be returned. If an index exists, the optimizer tries to use the index first and creates a temporary table only as a last resort.
A temporary table is used by an access plan to store data during its execution in a transient or temporary worktable. This table exists only while the access plan is being executed. Generally, temporary tables are used when intermediate results do not fit in the available memory, such as when:
- Subqueries must be evaluated early in the access plan
- Data in a temporary table is held for only a single connection only
- A query contains an ORDER BY on a column other than an index
- A query contains a GROUP BY on a column other than an index
It is difficult to anticipate whether an index you have created avoids the necessity for a temporary table. Therefore, always check the plans for a query to ensure that the indexes you have created are actually being used by the UltraLite query optimizer.
You can avoid using temporary tables by using an index for the columns used in the ORDER BY or GROUP BY clauses.

Indexes

The optimizer looks at query requirements and checks if there are any indexes it can use to improve performance. If there is not, then the optimizer uses either a temporary table or a direct page scan instead. Therefore, you may need to experiment with your indexes, and frequently check generated execution plans to ensure that you are:

Not maintaining indexes that are not being used by the optimizer
Minimizing the number of temporary tables being created

The Object API uses the surrogate key scheme; the surrogate key is part of a composite primary key with the pending flag. A second index is created for the business key of the MBO, designated by xxxx_findByPrimaryKeyIndex. These are all the indexes for each MBO that are updatable. For read-only MBOs, the primary key consists of only the surrogate key. If the query to be optimized requires additional indexes, the MBO developer must create such indexes from the Object Queries tab in SAP Mobile WorkSpace.

Create the index by creating an appropriate object query. The new object query results in a small amount of generated code that does not impact application performance. For example, if you create an index for the attribute “zip”, you need only create an object query using “zip” in the WHERE clause.

While a new object query does not impact performance, an additional index may slow down synchronization and update/insert operations. Do not create indexes to prepare for future upgrades that may or may not materialize. Even for a read-only MBO, additional indexes may slow synchronization. If MBO data changes infrequently and is limited in size, then the only concern is initial synchronization time. Faster query performance and synchronization speed is a trade-off. Indexes are an important tuning tool but must be used with caution: they are important for large tables due to the cost of table scans; however, redundant indexes slow down insert/update/delete operations, but provide no benefit.

Materialized Views

For complex queries involving large reference tables that change infrequently, it may be beneficial to construct a materialized view to have results return within user experience limits. You can construct such views using a local MBO, and maintain them in the application code. The change log facility notifies the application of changes to the data set after synchronization completes. Based on the changed/new MBO instance, the application code can update the materialized view. As a result, the query can leverage the materialized view without a join operation and spread materialized view maintenance out over time. SAP does not recommend this approach for volatile data, as maintenance processing is complex and time consuming. Additionally, the most recent view is delayed until processing completes so the user may see only partially updated materialized views.