VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > sql数据库 >

  • sql语句大全之sql优化实战：从250秒+到10秒（简化

今天客服又反馈了一个问题，说客户在访问后台web页面中有一个查询时非常慢，直接报了“查询超时，请联系管理员！”。
 
于是用客户账号登录系统，最后确实是超时，但是用管理员账号登录查询时，虽然返回了68w条数据，但也就18秒。
 
sql代码如下：
 
SELECT  o.id1 AS o_id1 ,
        o.id2 AS o_id2 ,
        o.id3 AS o_id3 ,
        o.id4 AS o_id4 ,
        o.id5 AS o_id5 ,
        s.geo_id AS s_geo_id ,
        s.store_id AS s_store_id ,
        NULL AS s_store_name ,
        s.channel_id AS s_channel_id ,
        s.type_id AS s_type_id ,
        s.corp_id AS s_corp_id ,
        s.nature_id AS s_nature_id ,
        o.org_id AS o_org_id ,
        NULL AS o_empname ,
        e.emp_id AS e_emp_id ,
        NULL AS e_emp_name ,
        t.year AS t_year ,
        t.month AS t_month ,
        t.day AS t_day ,
        f.pn1 AS f_pn1 ,
        f.pn2 AS f_pn2 ,
        f.pn3 AS f_pn3 ,
        f.pn4 AS f_pn4 ,
        p.prod_id AS p_prod_id ,
        f.upload_date AS f_upload_date ,
        f.is_data_entry AS f_is_data_entry ,
        MAX(f.item1) AS M000 ,
        MAX(CASE WHEN ISNUMERIC(f.ITEM1) = 1
                      AND ISNUMERIC(f.prod_price) = 1
                 THEN f.ITEM1 * f.prod_price
                 ELSE 0.00
            END) AS M001
FROM    ( SELECT    t.emp_id ,
                    t.store_Id ,
                    t.BIZ_DATE ,
                    CONVERT (VARCHAR(23), t.UPLOAD_DATE, 20) AS UPLOAD_DATE ,
                    p.PROD_ID
                    ,
                    REPLACE(REPLACE(d.ITEM1, '，', ''), '：', '') AS ITEM1 ,
                    d.ITEM2 ,
                    b.PN1 ,
                    b.PN2 ,
                    b.PN3 ,
                    b.PN4 ,
                    t.func_code ,
                    p.prod_price ,
                    t.is_data_entry ,
                    s.ORG_ID
          FROM     TB_DIST_RULE t WITH ( NOLOCK )
                    inner HASH JOIN TB_DIST_RULE_ITEM d WITH ( NOLOCK ) ON t.ID = d.MAIN_ID
                    LEFT JOIN TB_PRODUCT p WITH ( NOLOCK ) ON d.PROD_ID = p.PROD_ID
                    LEFT JOIN TB_DICT_ITEM b WITH ( NOLOCK ) ON p.BRAND_ID = b.DICT_ITEM_ID
                    LEFT HASH JOIN ( SELECT s.ORG_ID ,
                                            s.STORE_ID ,
                                            o.EMP_ID
                                     FROM   TB_STORE_ORG s WITH ( NOLOCK )
                                            INNER JOIN tb_org o ON o.ORG_ID = s.ORG_ID
                                   ) s ON s.STORE_ID = t.STORE_ID
                                          AND s.ORG_ID = t.emp_org_id_num
          WHERE     1 = 1
                    AND t.biz_date >= '2016-10-01'
                    AND t.biz_date <= '2016-10-31'
                    AND t.func_code IN ( 'TB_005_AT02', 'TB_005_AT03',
                                         'TB_005_AT04', 'TB_005_AT05',
                                         'TB_005_AT06', 'TB_005_AT07',
                                         'TB_005_AT08', 'TB_005_AT09',
                                         'TB_006_AT06', 'TB_006_AT07',
                                         'TB_006_AT05', 'TB_006_AT08',
                                         'TB_006' )
        ) f
        INNER JOIN tb_dims_time t ON f.biz_date = t.the_date
        LEFT JOIN tb_dims_org o ON f.org_id = o.org_id
        LEFT JOIN tb_dims_emp e ON f.emp_id = e.emp_id
        INNER JOIN tb_dims_store s ON f.store_id = s.store_id
        LEFT JOIN tb_dims_product p ON f.prod_id = p.prod_id
WHERE   ( f.biz_date BETWEEN '2016-10-01' AND '2016-10-31' )
        AND o.id4 = '65'        
        AND f.pn1 LIKE '%MC%'
        AND ( f.func_code = 'TB_005_AT02'
              OR f.func_code = 'TB_005_AT03'
              OR f.func_code = 'TB_005_AT04'
              OR f.func_code = 'TB_005_AT05'
              OR f.func_code = 'TB_005_AT06'
              OR f.func_code = 'TB_005_AT07'
              OR f.func_code = 'TB_005_AT08'
              OR f.func_code = 'TB_005_AT09'
            )
GROUP BY o.id1 ,
        o.id2 ,
        o.id3 ,
        o.id4 ,
        o.id5 ,
        s.geo_id ,
        s.store_id ,
        s.channel_id ,
        s.type_id ,
        s.corp_id ,
        s.nature_id ,
        o.org_id ,
        e.emp_id ,wa
        t.year ,
        t.month ,
        t.day ,
        f.pn1 ,
        f.pn2 ,
        f.pn3 ,
        f.pn4 ,
        p.prod_id ,
        f.upload_date ,
        f.is_data_entry
 
运行时间：259秒。
表信息：TB_DIST_RULE表和TB_DIST_RULE_ITEM关联之后，总数据时2800w条。其他的表数据量很少。
 
结果集：3500条数据左右。
 
 
 
优化：发现语句中的过滤条件  AND o.id4 = '65'  如果去掉，虽然返回更多的数据，但是查询速度确比较正常。其中inner hash JOIN TB_DIST_RULE_ITEM d 可以改为 inner loop JOIN TB_DIST_RULE_ITEM d 。
 
于是想到了3种优化方法：
 
（1）想办法把         AND o.id4 = '65' 过滤条件加到语句的最内层，但是由于这个查询条件是用户登录时，系统自动给加上的，所以不好控制，不过还是可以加，就是麻烦一点。
 
（2）可以尝试更新一下统计信息。
 
（3）简化语句。
 
 
 
这里用第3种方式，分析sql发现，这段代码从逻辑上，意义不是很大：
 
                    LEFT HASH JOIN ( SELECT s.ORG_ID ,
                                            s.STORE_ID ,
                                            o.EMP_ID
                                     FROM   TB_STORE_ORG s WITH ( NOLOCK )
                                            INNER JOIN tb_org o ON o.ORG_ID = s.ORG_ID
                                   ) s ON s.STORE_ID = t.STORE_ID
                                          AND s.ORG_ID = t.emp_org_id_num
改为下面的代码：
                    INNER HASH JOIN tb_org o ON t.emp_org_id_num = o.org_id
 
其中的left hash join也改成了inner hash join ，因为最外层关联时和 tb_dims_org是inner join，所以内部的left join本质上就是inner join，这个优化器应该会做转换。
 
之所以简化为一个表，是由于tb_store_org表的字段都没有出现在select中，所以可以去掉。
 
但想了想，tb_store_org中的store_id和org_id关联，是起了过滤作用，又不能轻易去掉。
 
但进一步分析业务，发现并不需要这种过滤，这种过滤是多此一举。。。
 
 
 
这么简化后，从250秒+降到了10秒左右。
--------------------- 
作者：不想长大啊 
来源：CSDN 
原文：https://blog.csdn.net/sqlserverdiscovery/article/details/53067354 
版权声明：本文为博主原创文章，转载请附上博文链接！

相关教程