Query Builder: Improving Query Performance

Business Objects Query Builder is a free application provided with Business Objects Enterprise XI (BOXI). It is uses the BOE SDK to provide a simple web user interface for submitting queries against the CMS InfoStore and returning their results.

Query Performance Concerns: Indexed Properties

As you begin to experience the wonders of Query Builder you will find that certain queries run quickly and other run very slowly, or not at all. It is important to remember that you are still querying a database and certain portions of that database have been indexed for improved performance in Business Objects. Among the lucky indexed properties you should find the following. For improved performance use these in your query’s selection criteria (WHERE clause):

SI_CUID
SI_GUID
SI_HIDDEN_OBJECT
SI_ID
SI_INSTANCE_OBJECT
SI_KIND
SI_NAME
SI_NAMEDUSER
SI_NEXTRUNTIME
SI_OWNERID
SI_PARENTID
SI_PLUGIN_OBJECT
SI_RECURRING
SI_RUID
SI_RUNNABLE_OBJECT
SI_SCHEDULE_STATUS
SI_UPDATE_TS
SI_INSTANCE

Optimizing Query Builder Queries

Query performance is improved by using indexed properties in your selection criteria (WHERE clause); however, the order of these can additionally optimize your query’s performance. All selection criteria with indexed properties should be placed first in the WHERE clause. The BusinessObjects Enterprise CMS InfoStore, and therefore Query Builder, processes queries from top to bottom and left to right. Therefore the selection criteria should also be ordered from the most restrictive to the least restrictive. For example, SI_NAME = ‘My Report’ should be place before SI_KIND = ‘WebI’ in the query. It may not be the prettiest query but it will be the fastest.

For example:

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
si_name = '' AND
si_kind = 'WebI' AND
si_instance = 0

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.