Native queries guidelines - Improve performance of BQL queries

Native queries guidelines - Improve performance of BQL queries

book

Article ID: KB0070629

calendar_today

Updated On:

Products Versions
TIBCO BusinessEvents Enterprise Edition 6.x

Description

TIBCO BusinessEvents now supports native queries, a native query is a typical query that can be executed using any sql client against the cache. For instance for IGNITE cache , if the query works in a sql client against the space from SQL line or DBeaver or other thin  client then we can use it in BE. A native BQL query is faster than a normal BQL query, but users need to work with the result set differently.

Issue/Introduction

Native Queries Guidelines (execution time faster compared to 'normal' BQL queries)

Environment

All Operating Systems

Resolution

Native queries cannot return the concept(s). You need to add all required fields to the SQL and load them from resultset or create a query that returns the id or extID and load the concept(s) with catalog functions Instance.getBy...() or Cluster.DataGrid.getBy...().
The format of a native query is different from an original BQL query. 

BQL query: 
select <field> from <entityURL> as <alias> where <alias>.<field>=<value> ...
e.g.,
select c@id from /Concepts/DemoConcept as c where c.prop_string="testValue"

Native query: 
"native-query: select <field> from be_gen_<entityURLSeparator_underline> where <field>=<value> ...
e.g., 
"native-query: select _id from be_gen_DemoConcept where prop_string="testValue"
Note:
  • Add prefix "native-query:" to the BQL query to tag the query as a native query.
  • Field name for the ID is "_id"
  • Field name for the extID  is "_extid"
  • Entity URL has a prefix "be_gen_" and the separator "/" replaced with a "_"
  • Native queries not work when AS2.x as in-built cache in BE version 6.3.x (AS2.x deprecated)
  • For Ignite as in-built cache we recommend to use native queries and add a composite index for the columns in WHERE clause (CDD cluster area -> Overrides section).
Sample BE code: 
try{
	String sQuery = "native-query: select _EXTID from be_gen_concepts_cpTest cp where cp.p1='t2'";
	Object oResultList = Query.Util.executeInDynamicQuerySession(sQuery,null,false);
	Object[] oRows = Query.Util.listToArray(oResultList);
	Query.Util.clearList(oResultList);

	for (int i = 0; i < oRows@length; i++){
		String sExtID=oRows[i];
		// load concept into Rete
		Concepts.cpTest cp=Cluster.DataGrid.CacheLoadConceptByExtIdByUri(sExtID,false,"/Concepts/cpTest");
		System.debugOut(Engine.ruleFunctionName() + " loaded concept with extID=" + sExtID);
	}
}
catch (Exception ex) {
	System.debugOut("### ERROR " + Engine.ruleFunctionName() + ": Stacktrace: " + ex@stackTrace);
}

 

Additional Information

https://docs.tibco.com/pub/businessevents-enterprise/6.3.0/doc/html/Default.htm#Developers/Native-Querying-Support.htm
Powered by Wolken Software