Performance considerations

Getting the best query performance depends on how you use filters and wildcard characters.

Follow these recommendations in your queries.

Make your filters highly restrictive

A restrictive filter matches only a small portion of the total number of rows for a component type. Using restrictive filters generally leads to better execution plans for your database queries.

Example: In the earlier example, the filter on MyJournal Organization attribute is a restrictive filter because only 1,000 components, out of a total of 1,000,000 components, have Organization = “IBM”.

Minimize the use of wildcard characters in your queries

Minimizing the use of wildcard characters in your queries is always a good idea. Specifically, because application of each filter generally involves adding extra conditions and joins to your queries, the combination of this extra complexity can be significant when you use wildcard characters. Because a wildcard refers to all of a user's active views in the system, your query can get large as the number of filtered views increases. Whenever possible, use a specific component type view instead of a wildcard.

Example: If you know that in your data model SIG items link only to Journals or Books, use the specific names of those component type views in your query instead of the wildcard character to indicate the target of link traversal. For example, to find all items that are linked from special interest groups named "XML", rewrite your query as follows:

Sub-optimal: “/MySIG [@Title = “XML”]/OUTBOUNDLINK/@TARGETITEMREF => *”
Optimal: “/MySIG [@Title = “XML”]/OUTBOUNDLINK/@TARGETITEMREF => MyJournal UNION /MySIG [@Title = “XML”]/OUTBOUNDLINK/@TARGETITEMREF => MyBook”

Avoid defining too many filters in your system

The more filtered views you have in the system, the more complex your final database queries become when you use wildcards. If you decide to use filters, define these filters only on the views that really require such filtering or avoid by using wildcards.

Example: The simple query “/* [@ITEMID = “myItemID”]” to retrieve an item with a specific ITEMID can result in a complex final SQL query if you have, for example, 30 root item type views in the system with 25 of them having filters defined on them. For each filter, extra conditions and joins must be added to your database query. If you know, for example, that your item belongs to either a Journal or a SIG item type, rewrite your query in the following way: “(Journal | SIG) [@ITEMID = “myItemID”]”

Because there is a limit on the length of the SQL query string that the database can process, some of your queries might throw an exception if you have a lot of filters defined and you use wildcard characters.