Hibernate DAO delete instances queries optimization
---------------------------------------------------
Key: ODE-641
URL: https://issues.apache.org/jira/browse/ODE-641
Project: ODE
Issue Type: Improvement
Components: BPEL Runtime
Affects Versions: 1.3.2
Reporter: Rafal Rusin
Assignee: Rafal Rusin
Fix For: 1.3.3
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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
