數據庫環境：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

下面，我來說下我的優化思路：

　　 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 sat0