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);

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