Improve Object Query Performance

Investigate and optimize object query performance for Native Object API applications on iOS, Windows Mobile, BlackBerry, and Android platforms.

While most simple queries run with reasonable performance, complex queries require a good understanding of the underlying database schema derived from the MBO data model, as is the case in a client/server computing environment where the DBA is often consulted to develop optimized stored procedures or perform database tuning. The differences with mobile application development are that:

The developer is the DBA of the device database. A device database engine does not have the complex optimization strategies nor does it keep statistics that an enterprise database server does.
Resources on a device are limited, and heavy or complex queries often result in significant performance implications. For example, hash joins are not supported on the Ultralite database due to potentially large memory requirements for less capable devices.

The Query API is used on Windows Mobile, BlackBerry, and Android platforms, while SUPQuery is used by iOS. Mobile application developers can use these APIs to query the on device database and return data in the form of a result set.

The SQL statement is generated based on the Query/SUPQuery object constructed by the application and allows the Object API to inject conditions to deal with the object states necessary for offline computing. When an object is modified, there is a pending state (current), a download state (server), and the original state (before first modification). The pending and download states reside in the MBO table. The extra conditions in the SQL statement enable the return of either the pending state (if there is one) or the download state. No query optimization is performed by the Object API during the generation of the SQL statement. See either Query and Related Classes or SUPQuery and Related Classes in the Developer Guide: yourPlatform Object API Applications.

Examining the Database Schema and Data Size
Use Sybase® Central from the SAP Mobile Platform installation to connect to and examine the device's database file. The UltraLite database udb file is compatible across device platforms.
Obtaining the SQL Statement Generated From the Query
Set enableTrace in the client code to trace the SQL statement.
Showing the Execution Plan
Use dbisql to read the execution plan of the code that you are tracing.
Interpreting the Execution Plan
Interpret the execution plan and, based on the results, optimize object queries.

Parent topic: Object Queries