TIBCO BusinessEvents (BE) BQL queries - Performance impact when keyword "between" is used in WHERE clause

TIBCO BusinessEvents (BE) BQL queries - Performance impact when keyword "between" is used in WHERE clause

book

Article ID: KB0082235

calendar_today

Updated On:

Products Versions
TIBCO BusinessEvents Enterprise Edition 5.5.0/5.4.1HF1

Description

A BQL query that includes a "between" keyword in the WHERE clause can cause a performance issue. The slow execution is caused by the old-parser and fallback mechanism to query AS (mostly without filter) and then do the filtering in-memory.

Sample Query:

Util.HashMap.createMap("mQuery" + Engine.threadName()); 

DateTime oDt1 = DateTime.createTime(2018,02,19,16,30,08,"PST");
DateTime oDt2 = DateTime.createTime(2018,02,21,08,08,08,"PST");
DateTime oDt3 = DateTime.createTime(2018,02,25,16,30,08,"PST");
DateTime oDt4 = DateTime.createTime(2018,02,29,08,08,08,"PST"); 

Util.HashMap.putObject("mQuery","param1",oDt1); Util.HashMap.putObject("mQuery","param2",oDt2); Util.HashMap.putObject("mQuery","param2",oDt3); Util.HashMap.putObject("mQuery","param2",oDt4); 

String query="Select c@extId from /Concepts/TestConcept as c where ((dStartDate between $param1 and $param2) or (dStartDate between $param3 and $param4))"; Object resultList = Query.Util.executeInDynamicQuerySession(query2, Util.HashMap.getMap("mQuery"), true);

Issue/Introduction

TIBCO BusinessEvents (BE) BQL queries - Performance impact, avoid keyword "between" in where clause in current versions (BE 5.5.0, 5.4.1HF1)

Environment

All Operating Systems

Resolution

We recommend to update the query and use greater than equal to (>=) and less than equal to (<=) conditions instead of the BETWEEN clause.

Sample Query with change:

String query="Select c@extId from /Concepts/TestConcept as c where ((dStartDate > $param1 and dStartDate < $param2) or (dStartDate > $param3 and dStartDate < $param4))";

  



 

 

Additional Information

BQL
Powered by Wolken Software