[ODE-641] Hibernate DAO delete instances queries optimization

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.3.2
• Fix Version/s: 1.3.4, 2.0-beta3
• Component/s: BPEL Runtime
• Assignee: Rafal Rusin
• Reporter: Rafal Rusin
• Created: 03/Aug/09 13:34
• Updated: 10/Feb/10 11:11
• Resolved: 10/Feb/10 11:11

Description

I saw that queries like:
select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
are poorly optimized by MySQL.
Changing them to:
select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
makes them faster by order of magnitude.
I used indexes from attachment.

Oracle seems to handle them well, however it has problems with "or" queries like this:
"SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"

So I divided "or" queries into 2 separate queries.

I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
mysql:
without patch 2000 ms
patched 40 ms

oracle:
without patch 2000 ms
patched 75 ms

Issue Links

• is related to: ODE-671 clean up queries in HLargeData are too broad