Chapter 8 Working With Continuous Queries : Optimizing the Design

Optimizing the Design
It is important to be aware of the following points when working with queries.
Reuse Existing Queries and Statements Whenever Possible
Creating a new query is an "expensive" operation. If possible, create the queries ahead of time (in a startup function), then keep reusing those existing query definitions in new statements. (See Lifecycle of a Query—Use of Query Functions for more details)
For example, you could create a query in a startup function. That query may use bind variables, for more flexibility (see Using Bind Variables in Query Text. Then, in a preprocessor rule function, you could create a new statement using that query, set values in the statement for all the bind variables of the query using the data in the event, and execute the statement. As a result, the query would be customized and executed for each event reaching the preprocessor.
Depending on your situation, it might be possible to create a single statement, and keep reusing that same statement, executing it multiple times.
The function that creates a new query requires that you provide a globally unique name. You can later refer to that query using its name. The function that opens a new statement requires you to provide an existing query name, and a new globally unique statement name. You can later refer to that statement using its name.
Improve Performance by Pre-fetching Objects
When a query executes, objects are fetched from the cache as needed for query processing. Objects are placed in the local query cache for use by the query. You can improve performance by pre-fetching the objects. See the section Configuring Query Agents—Engine Properties for Performance in TIBCO BusinessEvents User’s Guide for details.
Use Filtering for Efficient Joins
When performing a join between two or more entities in a query, the most selective operators (filter on entity attributes) must appear before the actual join expression. This makes the join more efficient.
Joins that test for equality (equivalent joins), that is, joins between two entities that use the equals operator (=), perform better than joins that test for inequality (non-equivalent joins), that is, joins using operators such as greater than, less than, and so on (>, <, >=, <=).
Example
In the example below, the two entities Trade and StockTick are joined by matching their respective securityId and symbol. But the query also places the restriction that only TIBX trades and stock ticks are of interest, and only if the trade's settlement status is FULLY_SETTLED. These filters appear before the actual join expression, which is more efficient than if they were placed after the join (t.securityId = tick.symbol).

 
select tick.symbol,
    sum(tick.price) * 1000 / count(*),
    avg(tick.volume),
    count(*),
    t.counterpartyId
from /Trade t, /StockTick {policy: maintain last 1 sliding where symbol = "TIBX"} tick
where t.securityId = "TIBX"
    and t.settlestatus = "FULLY_SETTLED"
    and t.securityId = tick.symbol
group by tick.symbol, t.counterpartyId
having count(*) > 2;

 
Effect of the Cache on Continuous Queries
Queries are run against the object cache, not against the contents of working memory. Ensure that the objects you want to query are in the cache when the query is run, and are not, for example, removed from the cache before the query executes.
For example, while a continuous query is running, multiple batches of results may be received. At the time it is first received, a batch of continuous query results contains items that are in the cache. If you wait for another batch, some (or all) of the objects in the old results may have been evicted from the cache.
Effect of Time on Queries
While running continuous queries, errors can occur if entity creation and deletion happen in rapid succession.
Example
Consider a continuous query that is monitoring entities of type /OrderEvents. Suppose that OrderEvents entities are created, asserted, and consumed, at a fast rate. When an OrderEvent entity is asserted, it is also added to the cache. When it is consumed, an OrderEvent entity is deleted from the cache. The continuous query receives the creation notification and the deletion notification one after the other.
If there is a long enough delay between the creation and deletion actions and the moment a query agent attempts to process the related notifications, the agent will try to retrieve OrderEvent entities that have already been removed from the cache, resulting in runtime errors.
This situation may occur when, for example, a very quick succession of notifications is sent, or the network traffic suffers delays, and so on.
Query Agent Local Cache
The query agent retains the most recently processed entities in a local cache to avoid frequent network lookups. But in the example above, the OrderEvent is deleted from the cache even before the create-notification is processed by the query, so the Orderevent can’t be copied into the query agent's private cache.
Keep such situations in mind as you design your queries.