cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

[HOW TO] Speed up queries in API code by using Absolute Identifiers

cpucci
MEGA
MEGA

Hello, do you know that the way you use to write queries in API code may affect the performance of the query?

 

This article shows how to raise performance in queries used inside the code (Java or VB) by simply changing names to absolute identifiers. 

 

Look at this example (in  VB but Java as exactly the same results):

 

root=getRoot

s=Timer
for i=1 to 10000
 set res = root.getSelection("Select [Application] Where [Defined-Service].[Operation].[Organizational Process]='World@Hand::BPMN Notation Diagrams:: Purchasing:: Purchase Goods & Services:: Contract Negotiation'")
next
e=Timer

print "Query without IdAbs: " & (e-s)*1000 & "ms"

 

s=Timer
for i=1 to 10000
 set res = root.getSelection("Select ~MrUiM9B5iyM0[Application] Where ~ltSTdNNHjqj0[Defined-Service] in (~TsUiT9B5iyQ0[IT Service] WHERE ~hqUiTCB5iK72[Operation].~mrUiaCB5iCB2[Organizational Process]='~W3qoNsjV91e6[Contract Negotiation]')")
next
e=Timer

print "Query with IdAbs: " & (e-s)*1000 & "ms"

 

The environment is Demonstration with MEGA 2009 SP5 CP6 R6.

We execute (a lot of times, 10000 times) the same query and then we will print the elapsed time.

The first query uses the names for MetaClass, MetaAssociationEnds, and the name for the source object.

The second query replace names with Absolute Identifiers (in the MegaField format).

 

The first cycle of queries took 20312,51 ms to be executed (about 20 seconds), the second cycles took about 16796,885 ms to be executed (a little bit more than 1 second and half).

 

By using idabs for the query we gain about the 25% in performances.

Remember (for the entry point) that MEGA accept a megaField when you can put a name.

To get the megaField of an object (its Id) just write myObject.megaField (you can use also .megaUnnamedField, in this case the name of the object will be replaced by a X, for the system it is the same).

 

In complex reports where you do a lot of queries the execution time of the report can benefit from queries with Identifiers.

 

Claudio Pucci
1 Reply

lrobinet
MEGA
MEGA

This approach can also be implemented for imbedded queries in HTML descriptions for websites.

Not only is it interesting as far as peformance is concerned, it is also more maintainable (as written text for names of MetaClasses, MetaAssociationEnds and MetaAttributes is not stable in the long term).