Eclipse Community Forums: Eclipse Process Manager (Stardust) » Extremely high number of SQL statements executed for activity instance query

Home » Archived » Eclipse Process Manager (Stardust) » Extremely high number of SQL statements executed for activity instance query(Activity Instance Query: SQL Statements)

Extremely high number of SQL statements executed for activity instance query [message #749208]

Tue, 25 October 2011 00:57

Eclipse User

Hi,

We are using following code to get a list of activity instances having associated data.

ActivityInstanceQuery activityInstanceQuery = ActivityInstanceQuery.findAll();
DataFilter dataFilter = DataFilter.in(dataID, disputeIDs);
activityInstanceQuery.getFilter().add(dataFilter);
ActivityInstanceState [] activityInstanceStates = new ActivityInstanceState [] {
ActivityInstanceState.Created,
            ActivityInstanceState.Suspended,
            ActivityInstanceState.Application                  
};

activityInstanceQuery.getFilter().add(new ActivityStateFilter(activityInstanceStates));
QueryService queryService = getQueryService(serviceFactory);
Collection activityInstances = queryService.getAllActivityInstances(activityInstanceQuery);

return activityInstances;

This method fires multiple queries while fetching the data. What we have observed here is, irrespective of number of disputeIDs being passed the method fires multiple queries.

Please let us know what might be causing such problem.

Re: Extremely high number of SQL statements executed for activity instance query [message #749220 is a reply to message #749208]

Tue, 25 October 2011 01:06

Eclipse User

I would assume that the data you're passing in is a descriptor (IN Data Path on process definition marked as descriptor pointing to Dispute ID data). Is this correct?

Descriptor values are fetched with the query, but only if below construct has been defined for the query as well.

EvaluationPolicy policy = DescriptorPolicy.WITH_DESCRIPTORS;
query.setPolicy(policy);

Per default descriptors are not fetched (as long as this default behavior has not been changed we will verify). Are you using this construct somewhere?

... We cannot really believe that this is an dispute ID. Is it? What process data type are you using for dispute IDs?

[Updated on: Tue, 25 October 2011 01:06] by Moderator

Re: Extremely high number of SQL statements executed for activity instance query [message #749227 is a reply to message #749220]

Tue, 25 October 2011 01:11

Eclipse User

We checked if descriptors are fetched per default and it's indeed the case. Especially if you just pass one dispute ID into query, fetching the same does of course not make much sense. You can overwrite or remove the policy with one of the constructs below ...

// overwriting
ActivityInstanceQuery query = ActivityInstanceQuery.findAll();
EvaluationPolicy policy = DescriptorPolicy.NO_DESCRIPTORS;
query.setPolicy(policy);

// removing
ActivityInstanceQuery query = ActivityInstanceQuery.findAll();
query.removePolicy(DescriptorPolicy.class);

Both will have the effect that the descriptors are not fetched.

To better analyze performance of queries, can you please set log level to info and add property below. It can also be very useful to turn off prepared statements.

Carnot.Engine.Tuning.DB.slowStatementTracingThreshold = 100
AuditTrail.UsePreparedStatements = false

Having both properties in place would print all SELECT statements to the log in case duration exceeds 100 ms.

Re: Extremely high number of SQL statements executed for activity instance query [message #749232 is a reply to message #749227]

Tue, 25 October 2011 01:15

Eclipse User

So it looks like we have a different issue here. You have not defined descriptors at all. The problem is that the data you're using for your query is a Serializable, which will be serialized first and stored as multiple String snippets in STRING_DATA table. To do a comparison, the engine needs to load the data from STRING_DATA table. That's why you're observing hundreds of SELECT statements of type below, even though the actual result set would much smaller.

SELECT oid, objectid, data_type, data
FROM gpcarnot.STRING_DATA WHERE (gpcarnot.STRING_DATA.objectid = 25672 AND gpcarnot.STRING_DATA.data_type = 'data_value')
ORDER BY gpcarnot.STRING_DATA.oid ASC

SELECT dv.oid, dv.model, dv.data, dv.string_value, dv.number_value, dv.type_key, dv.processInstance
FROM gpcarnot.data_value dv
WHERE (dv.processInstance = 3637 AND dv.data = 281474976710667)

In those scenarios you should write the identifier (dispute ID) into a primitive process data once the Dispute is created. Primitives (strings or numbers) can be compared with what you're passing into query in one shot as part of the query itself.

Previous Topic:	Process is not appearing as Startable process
Next Topic:	Document Service Integration - Attached Document Panel

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 22 18:48:10 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter