Optimize SQL by adjusting the order of the table union ALL

Original: Optimizing SQL by adjusting the order of the table union ALL

Operating system: Windows XP

Database version: SQL Server 2005

　　Today, when you encounter a SQL, the filters are generated automatically, so it is not possible to adjust the SQL predicates for optimization purposes, only to find the "Big table" in SQL. There is a view that returns a larger set of results, and if you can adjust it, you can only adjust the view.

Looking at the structure of the view, it also applies another layer of view, directly to the innermost view of the query SQL.

SELECT A.dfeesum_no, A.opr_amt-isnull (b.dec_pay, 0)-ISNULL (b.dec_corrpay, 0)-ISNULL (B.dec_deduamt, 0        ) Dec_amt, A.dec_camt-isnull (b.dec_pay, 0)-A.dec_comprate * ISNULL (b.dec_deduamt, 0) Dec_compamt, A.dec_ramt-isnull (b.dec_corrpay, 0)-(a.dec_comprate-1) * ISNULL (B.dec_deduamt, 0) Dec_corramt, a . Dec_qty-isnull (B.dec_qty, 0)-ISNULL (b.dec_deduqty, 0) Opr_qty, ISNULL (b.dec_pay, 0) Dec_pay, ISNULL (b         . Dec_corrpay, 0) Dec_corrpay, ISNULL (b.dec_deduqty, 0) Dec_deduqty, ISNULL (B.dec_deduamt, 0) Dec_deduamt,                            ISNULL (b.dec_qty, 0) Dec_qtyfrom ctlm8686 a left JOIN (SELECT dfeesum_no, SUM (dec_ramt) dec_pay, sum (dec_corramt) dec_corrpay, sum (dec_qty) Dec_qty, SUM (case is flag_dedu = ' 1 ' then Dec_deduamt EL                    SE 0            END) Dec_deduamt, SUM (case is flag_dedu = ' 1 ' then Dec_deduqty                    ELSE 0 END) Dec_deduqty from dfeepay_03 GROUP by Dfeesum_no) b on a.dfeesum_no = B.dfeesum_nounion allselect a.dfeesum_no, A.D Ec_amt-isnull (b.dec_pay, 0)-ISNULL (b.dec_corrpay, 0)-ISNULL (B.dec_deduamt, 0) Dec_amt, A.dec_compamt -ISNULL (b.dec_pay, 0)-A.dec_comprate * ISNULL (b.dec_deduamt, 0) Dec_compamt, A.dec_corramt-isnull (b.de C_corrpay, 0)-(a.dec_comprate-1) * ISNULL (B.dec_deduamt, 0) Dec_corramt, A.opr_qty-isnull (b.dec_qty , 0)-ISNULL (b.dec_deduqty, 0) Opr_qty, ISNULL (b.dec_pay, 0) Dec_pay, ISNULL (b.dec_corrpay, 0) dec_corrpa Y, ISNULL (b.dec_deduqty, 0) Dec_deduqty, ISNULL (B.dec_deduamt, 0) Dec_deduamt, ISNULL (b.dec_qty, 0 ) Dec_qtyfrom Dfeeapp_03 a left JOIN (SELECT dfeesum_no, SUM (Dec_ramt) Dec_pay, SUM (dec_corramt) dec_corrpay, sum (dec_qty) Dec_qty, sum (case WHE N flag_dedu = ' 1 ' then Dec_deduamt ELSE 0 END) dec_dedu                                 AMT, SUM (case if flag_dedu = ' 1 ' then Dec_deduqty ELSE 0 END) Dec_deduqty from dfeepay_03 GROUP by dfeesum _no) b on a.dfeesum_no = B.dfeesum_no

There are 1433891 rows in the returned result set, where

SELECT COUNT (*) from dfeepay_03--1103914
SELECT COUNT (*) from ctlm8686--1131586
SELECT COUNT (*) from dfeeapp_03--302305

In the SQL script above, the subquery is the same, which is two scans of the subquery, consider letting dfeeapp_03 and ctlm8686union all, and then the left join DFEEPAY_03. At the same time, for sub-query, first let dfeepay_03 table first query out flag_dedu = ' 1 ' data, you do not have to judge the case.

The rewritten SQL is as follows

SELECT A.dfeesum_no, A.opr_amt-isnull (b.dec_pay, 0)-ISNULL (b.dec_corrpay, 0)-ISNULL (B.dec_deduamt, 0        ) Dec_amt, A.dec_camt-isnull (b.dec_pay, 0)-A.dec_comprate * ISNULL (b.dec_deduamt, 0) Dec_compamt, A.dec_ramt-isnull (b.dec_corrpay, 0)-(a.dec_comprate-1) * ISNULL (B.dec_deduamt, 0) Dec_corramt, a . Dec_qty-isnull (B.dec_qty, 0)-ISNULL (b.dec_deduqty, 0) Opr_qty, ISNULL (b.dec_pay, 0) Dec_pay, ISNULL (b         . Dec_corrpay, 0) Dec_corrpay, ISNULL (b.dec_deduqty, 0) Dec_deduqty, ISNULL (B.dec_deduamt, 0) Dec_deduamt, ISNULL (b.dec_qty, 0) dec_qtyfrom (SELECT a.dfeesum_no, A.opr_amt, a       . Dec_camt, A.dec_comprate, A.dec_ramt, A.dec_qty from ctlm8686 a UNION all SELECT a.dfeesum_no, A.dec_amt, a.           Dec_compamt,         A.dec_comprate, A.dec_corramt, A.opr_qty from dfeeapp_03 a                            ) a left JOIN (SELECT dfeesum_no, SUM (Dec_ramt) Dec_pay, SUM (dec_corramt) dec_corrpay, sum (dec_qty) dec_qty, sum (Dec                    _deduamt) Dec_deduamt, SUM (dec_deduqty) Dec_deduqty from dfeepay_03 WHERE flag_dedu = ' 1 ' GROUP by Dfeesum_no] B on a.dfeesum_no = B.dfeesum                 _no

Run this view of the query statement, from the original 1.5 to a minute, for the entire SQL, the original run a few minutes from the direct 10S results.

Optimize SQL by adjusting the order of the table union ALL