ode-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alexis Midon (JIRA)" <j...@apache.org>
Subject [jira] Reopened: (ODE-641) Hibernate DAO delete instances queries optimization
Date Sat, 03 Oct 2009 04:22:23 GMT

     [ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Alexis Midon reopened ODE-641:
------------------------------


Hi Rafal,

could you please report your query optimizations+ODE-671 in trunk? 

Thanks!

> 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: 2.0
>
>         Attachments: ode1x_indexes.sql
>
>
> 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.


Mime
View raw message