查询速度慢 [英] Slow performance of query

问题描述

可能的重复:
Sql 查询的慢性能

我已经询问了查询的性能，我试图简化它.但它仍然不起作用.我在下面添加我的查询.请你能更有效地简化它

I have asked the performance of query and i tried to simplyfy it.but still it not works.I am adding my query below.Please can you simplify it more effectively

select 
    r.parent_itemid f_id,
    parent_item.name f_name,
    parent_item.typeid f_typeid,
    parent_item.ownerid f_ownerid,
    parent_item.created f_created,
    parent_item.modifiedby f_modifiedby,
    parent_item.modified f_modified,
    pt.name f_tname,
    child_item.id i_id,
    t.name i_tname,
    child_item.typeid i_typeid,
    child_item.name i_name,
    child_item.ownerid i_ownerid,
    child_item.created i_created,
    child_item.modifiedby i_modifiedby,
    child_item.modified i_modified,
    r.ordinal i_ordinal
from
    item child_item,
    type t,
    relation r,
    item parent_item,
    type pt
where
    r.child_itemid = child_item.id and
    t.id=child_item.typeid and
    parent_item.id = r.parent_itemid and
    pt.id = parent_item.typeid 
    and parent_item.id in (
        select
            itemid
        from
            permission
        where
            itemid=parent_item.id  and
            (holder_itemid in (10,100) and level > 0) )
order by
    r.parent_itemid,
    r.relation_typeid,
    r.ordinal

谢谢问候珍妮

推荐答案

您不需要权限的相关子查询.任何其他问题都需要通过检查连接字段上的索引索引来解决(如 item.child_itemid )&过滤字段(如permission.holder_itemid)将有助于查询的性能

You don't need the correlated subquery on the permissions. Any other problems need to be fixed by checking the indexes indexes on the join fields (like item.child_itemid ) & filter fields (like permission.holder_itemid) will help the performance of your query

            select 
              r.parent_itemid f_id,
              parent_item.name f_name,
              parent_item.typeid f_typeid,
              parent_item.ownerid f_ownerid,
              parent_item.created f_created,
              parent_item.modifiedby f_modifiedby,
              parent_item.modified f_modified,
              pt.name f_tname,
              child_item.id i_id,
              t.name i_tname,
              child_item.typeid i_typeid,
              child_item.name i_name,
              child_item.ownerid i_ownerid,
              child_item.created i_created,
              child_item.modifiedby i_modifiedby,
              child_item.modified i_modified,
              r.ordinal i_ordinal
            from
              item child_item,
              type t,
              relation r,
              item parent_item,
              type pt,
              permission p
            where
              r.child_itemid = child_item.id
              and t.id=child_item.typeid
              and parent_item.id = r.parent_itemid
              and pt.id = parent_item.typeid 
              and parent_item.id = p.itemid
              and p.holder_itemid in (10, 100) 
              and p.level > 0
            order by
              r.parent_itemid,
              r.relation_typeid,
              r.ordinal

这篇关于查询速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！