ASE 15.7 optimizer does less REFORMATTING than 12.5
Dear group,
let me ask two generic questions first, then I'll describe the details of our case.
Is ASE 15.7 optimizer less prone to choose reformatting than 12.5 was?
How can ASE be invited to do more reformatting?
Our problem query is this:
SELECT * FROM TCURDIVS
WHERE DIV_CD NOT IN (SELECT DISTINCT(DIV_ENT_CD) FROM TOPERAFR
WHERE ALT_TS >= '20131021' AND SIT_CD = 'L')
The subquery on table TOPERAFR is inefficient, because the table is large and has no useful index.
ASE 12.5 handles it pretty well: it reformats TOPERAFR and builds a worktable. Then, subquery processing becomes a fast access to this worktable. This is the showplan and statistics output; some blank lines have been removed.
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created for REFORMATTING.
FROM TABLE
TOPERAFR
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
FROM TABLE
TCURDIVS
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Run subquery 1 (at nesting level 1).
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
STEP 1
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 2).
Correlated Subquery.
Subquery under an IN predicate.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.
FROM TABLE
Worktable1.
EXISTS TABLE : nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
END OF QUERY PLAN FOR SUBQUERY 1.
Server Message: Number 3630, Severity 10
Server 'STAREU_DE01', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 40459081.
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Server Message: Number 1562, Severity 10
Server 'STAREU_DE01', Line 1:
The sort for Worktable1 is done in Serial
Table: TCURDIVS scan count 1, logical reads: (regular=155 apf=0 total=155), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TOPERAFR scan count 1, logical reads: (regular=1064389 apf=403 total=1064792), physical reads: (regular=62 apf=435876 total=435938), apf IOs used=435876
Table: Worktable1 scan count 44, logical reads: (regular=107975 apf=0 total=107975), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Server Message: Number 3631, Severity 10
Server 'STAREU_DE01', Line 1:
Total actual I/O cost for this command: 10192728.
Total writes for this command: 425
Execution Time 191.
SQL Server cpu time: 19100 ms. SQL Server elapsed time: 703386 ms.
(177 rows affected)
Reformatting does a single TOPERAFR pass, at a heavy cost of 1M logical reads. The subquery is executed 44 times, since Worktable1 shows scan count 44, with an accumulated cost of 107,975 logical reads. 44 is the number of distinct values of DIV_CD, so ASE manages to query the worktable just once per distinct DIV_CD value.
15.7 does no reformatting, executes 46 times the subquery (they weren't 44 due to small differences in test data), which translates into 46 huge tablescans at an enormous cost. These are the reports.
QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode
STEP 1
The type of query is SELECT.
4 operator(s) under root
|ROOT:EMIT Operator (VA = 4)
|
| |SQFILTER Operator (VA = 3) has 2 children.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | TCURDIVS
| | | Table Scan.
| | | Forward Scan.
| | | Positioning at start of table.
| | | Using I/O Size 16 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.
| |
| | Run subquery 1 (at nesting level 1).
| |
| | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 2).
| |
| | Correlated Subquery.
| | Subquery under an IN predicate.
| |
| | |SCALAR AGGREGATE Operator (VA = 2)
| | | Evaluate Ungrouped ANY AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | TOPERAFR
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With MRU Buffer Replacement Strategy for data pages.
| |
| | END OF QUERY PLAN FOR SUBQUERY 1.
Server Message: Number 3630, Severity 10
Server 'SDSTRHA01_SY08', Line 1:
Total estimated I/O cost for statement 1 (at line 1): 2147483647.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
==================== Lava Operator Tree ====================
Emit
(VA = 4)
r:531 er:8142
cpu: 222100
/
SQFilter
(VA = 3)
r:531 er:8142
/ \
TableScan ScalarAgg
TCURDIVS Any
(VA = 0) (VA = 2)
r:8142 er:8142 r:46 er:1
l:163 el:165 cpu: 222100
p:3 ep:25
/
TableScan
TOPERAFR
(VA = 1)
r:43 er:5813
l:4.585e+07 el:1.017e+06
p:2.12e+06 ep:618646
============================================================
Table: TCURDIVS scan count 1, logical reads: (regular=163 apf=0 total=163), physical reads: (regular=3 apf=0 total=3), apf IOs used=0
Table: TOPERAFR scan count 46, logical reads: (regular=45851621 apf=135 total=45851756), physical reads: (regular=482 apf=2119486 total=2119968), apf IOs used=2119493
Server Message: Number 3631, Severity 10
Server 'SDSTRHA01_SY08', Line 1:
Total actual I/O cost for this command: 144703113.
Total writes for this command: 0
Execution Time 2221.
Adaptive Server cpu time: 206670 ms. Adaptive Server elapsed time: 1812994 ms.
(531 rows affected)
My first thought was "the optimizer must have underestimated the number of tablescans, so it thought reformatting was not worth." It may be true, but I cannot figure out why. The Lava tree shows the estimate logical reads for TOPERAFR: 1.017e+06, which is exactly the cost of one table scan. This explains the optimizer's choice, but why only one tablescan? The optimizer overestimated the number of TCURDIVS filtered rows, 8142 instead of 531, so I supposed it would have estimated a higher number of tablescans.
I asked the dba to run
update statistics TCURDIVS(DIV_CD)
But we got the same execution plan. At the time of this writing the query has not finished yet, so we dont have the Lava tree and statistics.
Thanks in advance,
Mariano Corral