Open topic with navigation

TIBCO BusinessEvents® Enterprise Edition 6.3.1

# Use Filtering for Efficient Joins (Cache Queries)

When performing a join between two or more entities in a query, put the most selective operators before the actual join expression. This makes the join more efficient.

See Optimize WHERE Clause Expressions for more information.

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. Comparison operators supported for filtering are as follows:

    >, >=, <, <=, ==, !=, In, Between, And, Or, Not, Like

## Example

In the following example, 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;

Did you find this helpful?

YesNo
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO BusinessEvents® Enterprise Edition

Copyright © 2004-2024. Cloud Software Group, Inc. All Rights Reserved.