• 自定义函数引发的性能问题


    ---执行1次
    select count(1)
      from LOAN_DUEBILLDATA bd
      left join LOAN_CONTRACTDATA bc
        on bd.RelativeSerialNo2 = bc.SerialNo
     where nvl(bd.ActualMaturity, bd.Maturity) <= '2014/08/06'
       and nvl(bd.ActualMaturity, bd.Maturity) >= '2014/07/07'
       and (bd.FinishDate is null or bd.FinishDate = '')
       and (bc.ManageUserID = '00717' or
            getCustomerManager(bc.CustomerId) = '00717')
    ----返回4条记录
    
    ---getCustomerManager函数里的sql执行3619次
    SELECT USERID FROM CUSTOMER_BELONG WHERE CUSTOMERID = :B1 AND BELONGATTRIBUTE='1'
    
    
    select  count(1)
      from LOAN_DUEBILLDATA bd
      left join LOAN_CONTRACTDATA bc
        on bd.RelativeSerialNo2 = bc.SerialNo
     where nvl(bd.ActualMaturity, bd.Maturity) <= '2014/08/06'
       and nvl(bd.ActualMaturity, bd.Maturity) >= '2014/07/07'
       and (bd.FinishDate is null or bd.FinishDate = '')
       and (bc.ManageUserID = '00717' or
            getCustomerManager(bc.CustomerId) = '00717');
    
    
    初步改写为:
    select count(1)
      from LOAN_DUEBILLDATA bd, LOAN_CONTRACTDATA bc, (select CustomerId FROM CUSTOMER_BELONG WHERE BELONGATTRIBUTE='1') be
     where bd.RelativeSerialNo2 = bc.SerialNo(+)
       and nvl(bd.ActualMaturity, bd.Maturity) <= '2014/08/06'
       and nvl(bd.ActualMaturity, bd.Maturity) >= '2014/07/07'
       and (bd.FinishDate is null or bd.FinishDate = '')
       and be.CustomerId = bc.CustomerId
       and (bc.ManageUserID = '00717' or be.customerid = '00717')
    

  • 相关阅读:
    词频统计
    第二周每周例行报告
    事务管理(ACID)
    Redis
    jar包和war包的区别
    CSS实现Loading加载动画
    如何实现“返回顶部”的页面效果
    PHP页面跳转-常见方法
    局域网络调试方式
    Thinkphp 统计数据库字段总值
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13352226.html
Copyright © 2020-2023  润新知