你的位置:首页 > 数据库

[数据库]dense_rank()+hash提示改写优化SQL


数据库环境:SQL SERVER 2005

今天看到一条SQL,返回10条数据,执行了50多S。刚好有空,就对它进行了优化,优化后1S出结果。

先看下原始SQL

SELECT t1.line_no ,    MAX(sat100.confrim_date) confrim_date ,    sam63_lq.company_name ,    sat04.c_code ,    ctlm23.corr_name ,    MAX(sat04.l_date_d) l_date_d ,    SUM(sat05.qty_d_order) qty_d_order ,    sat100.tran_no_rowFROM  sat100    INNER JOIN sat101 ON sat100.com_id = sat101.com_id               AND sat100.tran_no = sat101.tran_no               AND sat100.tran_row = sat101.tran_row    LEFT JOIN sat05 ON sat101.com_id = sat05.com_id              AND sat101.p_g_order_no = sat05.p_g_order_no              AND sat101.p_g_order_line = sat05.p_g_order_line    LEFT JOIN sat04 ON sat04.com_id = sat05.com_id              AND sat04.p_g_order_no = sat05.p_g_order_no    LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id              AND sat05.item_no = ctlm22.item_no    LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id               AND sat100.company_id = sam63_lq.company_id    LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id               AND sat05.cx_item_no = sam60_lq.cx_item_no    LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no               AND sat100.company_id = sam65_lq.company_id    LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id              AND sat05.sa_unit = ctlm17.unit    LEFT JOIN salm02 ON sat04.com_id = salm02.com_id              AND sat04.c_code = salm02.client_id    LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id              AND sat04.c_code = ctlm23.corr_id    LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id    LEFT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY t2.tran_no_row ) line_no ,              t2.tran_no_row          FROM  ( SELECT DISTINCT                    sat100.tran_no_row               FROM   sat100                    INNER JOIN sat101 ON sat100.com_id = sat101.com_id                               AND sat100.tran_no = sat101.tran_no                               AND sat100.tran_row = sat101.tran_row                    LEFT JOIN sat05 ON sat101.com_id = sat05.com_id                              AND sat101.p_g_order_no = sat05.p_g_order_no                              AND sat101.p_g_order_line = sat05.p_g_order_line                    LEFT JOIN sat04 ON sat04.com_id = sat05.com_id                              AND sat04.p_g_order_no = sat05.p_g_order_no                    LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id                              AND sat05.item_no = ctlm22.item_no                    LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id                               AND sat100.company_id = sam63_lq.company_id                    LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id                               AND sat05.cx_item_no = sam60_lq.cx_item_no                    LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id                              AND sat05.sa_unit = ctlm17.unit                    LEFT JOIN salm02 ON sat04.com_id = salm02.com_id                              AND sat04.c_code = salm02.client_id                    LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id                              AND sat04.c_code = ctlm23.corr_id                    LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id               WHERE   salm02.dept_id LIKE '%'                    AND sat100.company_id = '107'                    AND sat100.corr_id LIKE 'A010131%'                    AND sat04.l_date_d >= '2015/06/01 00:00:00'                    AND sat04.l_date_d <= '2015/06/30 23:59:59'              ) t2         ) t1 ON sat100.tran_no_row = t1.tran_no_rowWHERE  salm02.dept_id LIKE '%'    AND sat100.company_id = '107'    AND sat100.corr_id LIKE 'A010131%'    AND sat04.l_date_d >= '2015/06/01 00:00:00'    AND sat04.l_date_d <= '2015/06/30 23:59:59'GROUP BY t1.line_no ,    sam63_lq.company_name ,    sat04.c_code ,    ctlm23.corr_name ,    sat100.tran_no_rowORDER BY t1.line_no ,    sat100.tran_no_row

View Code

下面,我来说下我的优化思路:

   1.检查SQL的写法是否有问题

   先看下子查询部分,发现和外部访问的表及过滤的条件都差不多,用BeyondCompare工具检查外部查询和子查询的差别,相对于外部查询,

子查询少访问了一个表sam65_lq,即少了这部分内容“LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no AND sat100.company_id

= sam65_lq.company_id”,恰巧字段veh_no和字段company_id是sam65_lq的联合主键,因此,这部分并没有影响查询的数据。

   再看下子查询,它要实现的功能就是根据不重复的tran_no_row生成一个序号,因此,可以用dense_rank()替代子查询实现相同的功能。同时,

sat04有过滤条件,因而可以将left join sat04改成inner join sat04。

   改写后的SQL如下:

SELECT line_no ,    MAX(confrim_date) confrim_date ,    company_name ,    c_code ,    corr_name ,    MAX(l_date_d) l_date_d ,    SUM(qty_d_order) qty_d_order ,    tran_no_rowFROM  ( SELECT  DENSE_RANK() OVER ( ORDER BY sat100.tran_no_row ) AS line_no ,          sat100.confrim_date ,          sam63_lq.company_name ,          sat04.c_code ,          ctlm23.corr_name ,          sat04.l_date_d ,          sat05.qty_d_order ,          sat100.tran_no_row     FROM   sat100          INNER JOIN sat101 ON sat100.com_id = sat101.com_id                     AND sat100.tran_no = sat101.tran_no                     AND sat100.tran_row = sat101.tran_row          LEFT JOIN sat05 ON sat101.com_id = sat05.com_id                    AND sat101.p_g_order_no = sat05.p_g_order_no                    AND sat101.p_g_order_line = sat05.p_g_order_line          INNER JOIN sat04 ON sat04.com_id = sat05.com_id                    AND sat04.p_g_order_no = sat05.p_g_order_no          LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id                    AND sat05.item_no = ctlm22.item_no          LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id                     AND sat100.company_id = sam63_lq.company_id          LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id                     AND sat05.cx_item_no = sam60_lq.cx_item_no          LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no                     AND sat100.company_id = sam65_lq.company_id          LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id                    AND sat05.sa_unit = ctlm17.unit          LEFT JOIN salm02 ON sat04.com_id = salm02.com_id                    AND sat04.c_code = salm02.client_id          LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id                    AND sat04.c_code = ctlm23.corr_id          LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id     WHERE   salm02.dept_id IS NOT NULL          AND sat100.company_id = '107'          AND sat100.corr_id LIKE 'A010131%'          AND sat04.l_date_d >= '2015/06/01 00:00:00'          AND sat04.l_date_d <= '2015/06/30 23:59:59'    ) tGROUP BY line_no ,    company_name ,    c_code ,    corr_name ,    tran_no_rowORDER BY line_no ,    tran_no_row

View Code

   执行后,检查数据,发现和原始SQL的数据一致,说明改写的语法没有问题。但是,执行的时间没多大改观。

   2.检查是否有缺失索引

   看了下执行计划,发现在表sat100和表sat04上都走了索引扫描,在sat100表上没有针对company_id和corr_id建和组合索引,sat04表上也没有

针对l_date_d字段建的索引,而恰巧这些列的选择性都和高,适合建索引。

   建了索引后,再执行SQL,发现最快的还是要10S。不行,还得继续优化。

  3.检查执行计划,看执行计划是否合理

   执行计划部分

   sat05走了索引查找,它的查询开销达到88%。sat05表作为嵌套循环的内部表,外部表有5W多行记录,sat05表总共有50多W条记录,

且在sat05表上没有任何过滤条件。因此,可以判定,这里不应该走嵌套循环,应该使用哈希连接。所以,直接强制走哈希即可。

   改写优化后的完整SQL如下:

SELECT line_no ,    MAX(confrim_date) confrim_date ,    company_name ,    c_code ,    corr_name ,    MAX(l_date_d) l_date_d ,    SUM(qty_d_order) qty_d_order ,    tran_no_rowFROM  ( SELECT  DENSE_RANK() OVER ( ORDER BY sat100.tran_no_row ) AS line_no ,          sat100.confrim_date ,          sam63_lq.company_name ,          sat04.c_code ,          ctlm23.corr_name ,          sat04.l_date_d ,          sat05.qty_d_order ,          sat100.tran_no_row     FROM   sat100          INNER JOIN sat101 ON sat100.com_id = sat101.com_id                     AND sat100.tran_no = sat101.tran_no                     AND sat100.tran_row = sat101.tran_row          LEFT HASH JOIN sat05 ON sat101.com_id = sat05.com_id                      AND sat101.p_g_order_no = sat05.p_g_order_no                      AND sat101.p_g_order_line = sat05.p_g_order_line          INNER JOIN sat04 ON sat04.com_id = sat05.com_id                    AND sat04.p_g_order_no = sat05.p_g_order_no          LEFT JOIN ctlm22 ON sat05.com_id = ctlm22.com_id                    AND sat05.item_no = ctlm22.item_no          LEFT JOIN sam63_lq ON sat100.com_id = sam63_lq.com_id                     AND sat100.company_id = sam63_lq.company_id          LEFT JOIN sam60_lq ON sat05.com_id = sam60_lq.com_id                     AND sat05.cx_item_no = sam60_lq.cx_item_no          LEFT JOIN sam65_lq ON sat100.car_no = sam65_lq.veh_no                     AND sat100.company_id = sam65_lq.company_id          LEFT JOIN ctlm17 ON sat05.com_id = ctlm17.com_id                    AND sat05.sa_unit = ctlm17.unit          LEFT JOIN salm02 ON sat04.com_id = salm02.com_id                    AND sat04.c_code = salm02.client_id          LEFT JOIN ctlm23 ON sat04.com_id = ctlm23.com_id                    AND sat04.c_code = ctlm23.corr_id          LEFT JOIN ctlm08 ON salm02.dept_id = ctlm08.dept_id     WHERE   salm02.dept_id IS NOT NULL          AND sat100.company_id = '107'          AND sat100.corr_id LIKE 'A010131%'          AND sat04.l_date_d >= '2015/06/01 00:00:00'          AND sat04.l_date_d <= '2015/06/30 23:59:59'    ) tGROUP BY line_no ,    company_name ,    c_code ,    corr_name ,    tran_no_rowORDER BY line_no ,    tran_no_row

View Code