优化有标量子查询的SQL

数据库环境：SQL SERVER 2008R2

今天在数据库中抓出一条比较耗费资源的SQL，只返回904条数据，居然跑了40多分钟。SQL及对应的数据量如下图：

SELECT saft04.cur_year ,    LEFT(saft04.dept_id, 4) sdept_id ,    saft04.vdept_id ,    saft04.dept_id ,    saft04.fee_id ,    saft04.vitem_id ,    ISNULL(saft04.fee_amt, 0) AS saft04_fee_amt ,    ISNULL(saft04.fee_qty, 0) AS saft04_fee_qty ,    ISNULL(saft04.fee_amt_flex, 0) AS saft04_fee_amt_flex ,    ISNULL(saft04.adj_amt, 0) AS saft04_adj_amt ,    ISNULL(saft04.init_amt, 0) AS saft04_init_amt ,    ISNULL(saft04.flex_amt, 0) AS saft04_flex_amt ,    ISNULL(saft04.conf_fee_amt, 0) AS saft04_conf_fee_amt ,    saft04.fc_app_no ,    saft04.zone_id ,    saft04.corr_id ,    CASE WHEN saft04.fc_app_no < '2010'       THEN ( CASE WHEN saft04.flexfapp_flag = 'Y'             THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)               - ISNULL(conf_fee_amt, 0)             ELSE ISNULL(init_amt, 0) + ISNULL(flex_amt, 0)               + ISNULL(adj_amt, 0) - ISNULL(conf_fee_amt, 0)          END )       ELSE CASE WHEN b.fee_type2 = '01'              OR b.fee_type2 = '02'            THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)              - ISNULL(conf_fee_amt, 0)            WHEN b.fee_type2 = '03'            THEN ISNULL(init_amt, 0) + ISNULL(flex_amt, 0)              + ISNULL(adj_amt, 0) - ISNULL(conf_fee_amt, 0)         END    END bal_amt ,    ISNULL(( SELECT SUM(b.opr_amt)         FROM  v_saft04_fexp b         WHERE b.fcapp_id = saft04.fc_app_no        ), 0) AS qty1 ,    CASE WHEN b.fee_type2 = '01'         OR b.fee_type2 = '03'       THEN ISNULL(saft04.conf_fee_amt, 0)         - ( ISNULL(( SELECT  SUM(b.opr_amt)                FROM   v_saft04_fexp b                WHERE  b.fcapp_id = saft04.fc_app_no               ), 0) )       WHEN b.fee_type2 = '02'       THEN ISNULL(saft04.init_amt, 0) + ISNULL(saft04.flex_amt, 0)         - ISNULL(( SELECT SUM(b.opr_amt)               FROM  v_saft04_fexp b               WHERE b.fcapp_id = saft04.fc_app_no              ), 0)         + ISNULL(( SELECT SUM(d.opr_amt)               FROM  v_fadj_rd d               WHERE d.fcapp_id = saft04.fc_app_no              ), 0)    END qty2 ,    c.base_data2FROM  saft04    LEFT JOIN v_ctlm60 b ON b.fee_id = saft04.fee_id    LEFT JOIN ctlm1000 c ON c.d_type = 'fee_type2'                AND b.fee_type2 = c.base_data1WHERE  1 = 1    AND saft04.com_id = 'LQPJ'    AND saft04.cur_year = 2015    AND saft04.dept_id LIKE '2001%'    AND ( saft04.dept_id IN ( SELECT  dept_id                 FROM   ctlm2000                 WHERE   user_id1 = '0100030' )       OR '0100030' = 'MANAGER'      )ORDER BY saft04.cur_year ,    saft04.vdept_id ,    saft04.dept_id ,    saft04.fee_id ,    saft04.vitem_id ,    saft04.zone_id ,    saft04.corr_id ,    saft04.fc_app_no

        
 
        
海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com
        

        
原标题：优化有标量子查询的SQL
        
关键词：sql