Activiti待办任务查询中基于业务对象的条件查询

SQL查询优化案例

最新推荐文章于 2024-11-07 09:08:11 发布

原创最新推荐文章于 2024-11-07 09:08:11 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

本文探讨了两个SQL查询案例，其中一个可能存在潜在的问题。通过对查询的详细展示，分析了如何从数据库中提取特定流程实例和任务分配给指定用户的数据，并讨论了查询优化的方法。

  select
    v.*,t.id_ as taskid   
from
    provider v
inner join
    act_ru_execution e on v.id = e.business_key_           
inner join
    act_re_procdef p  on e.proc_def_id_ = p.id_      
inner join
     act_ru_task t on t.proc_inst_id_ = e.proc_inst_id_             
where
    e.parent_id_ is null
and e.business_key_ is not null
and t.assignee_ ='admin'

select
   v.*, t.id_ as taskid   
from
     act_ru_task t
inner join
    act_ru_execution e on  t.proc_inst_id_ = e.proc_inst_id_       
inner join
    act_re_procdef p  on e.proc_def_id_ = p.id_      
left join
      provider v on  v.id = e.business_key_           
where
    e.parent_id_ is null
and e.business_key_ is not null
and t.assignee_ ='admin';

上面的代码可能有些问题

Hibernate: 
    select
        count(*) as col_0_0_ 
    from
        tpa_claims claims0_ 
    left outer join
        act_hi_procinst historicpr1_ 
            on claims0_.process_instance_id=historicpr1_.id_ 
    left outer join
        act_ru_task tasks2_ 
            on historicpr1_.id_=tasks2_.proc_inst_id_ 
    left outer join
        act_re_procdef processdef4_ 
            on historicpr1_.proc_def_id_=processdef4_.id_ 
    where
        (
            historicpr1_.end_time_ is null
        ) 
        and processdef4_.key_=? 
        and tasks2_.assignee_=?
2012-07-10 17:57:48,834 [http-9999-5] TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [VARCHAR] - claims
2012-07-10 17:57:48,834 [http-9999-5] TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [VARCHAR] - 4
Hibernate: 
    select
        * 
    from
        ( select
            claims0_.id as id76_0_,
            tasks2_.id_ as id1_68_1_,
            processdef4_.id_ as id1_66_2_,
            claims0_.process_instance_id as process7_76_0_,
            claims0_.claims_date as claims2_76_0_,
            claims0_.create_date as create3_76_0_,
            claims0_.creator_id as creator4_76_0_,
            claims0_.first_check_id as first8_76_0_,
            claims0_.reality_claim_amount as reality5_76_0_,
            claims0_.remark as remark76_0_,
            tasks2_.assignee_ as assignee2_68_1_,
            tasks2_.create_time_ as create3_68_1_,
            tasks2_.delegation_ as delegation4_68_1_,
            tasks2_.description_ as descript5_68_1_,
            tasks2_.due_date_ as due6_68_1_,
            tasks2_.execution_id_ as execution7_68_1_,
            tasks2_.proc_inst_id_ as proc15_68_1_,
            tasks2_.name_ as name8_68_1_,
            tasks2_.owner_ as owner9_68_1_,
            tasks2_.parent_task_id_ as parent10_68_1_,
            tasks2_.priority_ as priority11_68_1_,
            tasks2_.proc_def_id_ as proc12_68_1_,
            tasks2_.rev_ as rev13_68_1_,
            tasks2_.task_def_key_ as task14_68_1_,
            processdef4_.category_ as category2_66_2_,
            processdef4_.deployment_id_ as deployment3_66_2_,
            processdef4_.dgrm_resource_name_ as dgrm4_66_2_,
            processdef4_.has_start_form_key_ as has5_66_2_,
            processdef4_.key_ as key6_66_2_,
            processdef4_.name_ as name7_66_2_,
            processdef4_.resource_name_ as resource8_66_2_,
            processdef4_.rev_ as rev9_66_2_,
            processdef4_.suspension_state_ as suspension10_66_2_,
            processdef4_.version_ as version11_66_2_ 
        from
            tpa_claims claims0_ 
        left outer join
            act_hi_procinst historicpr1_ 
                on claims0_.process_instance_id=historicpr1_.id_ 
        left outer join
            act_ru_task tasks2_ 
                on historicpr1_.id_=tasks2_.proc_inst_id_ 
        left outer join
            act_re_procdef processdef4_ 
                on historicpr1_.proc_def_id_=processdef4_.id_ 
        where
            (
                historicpr1_.end_time_ is null
            ) 
            and processdef4_.key_=? 
            and tasks2_.assignee_=? 
        order by
            claims0_.id asc ) 
    where
        rownum <= ?                             --感谢 @咖啡兔