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 
版权声明:本文为博主原创文章,转载请附上博文链接!

相关教程