ASE 15.7 optimizer does less REFORMATTING than 12.5

[corral]

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

[corral]

Thanks a lot, Mark.

CR 488583 looks interesting to me. I see it as a sign of Engineering being aware of a underestimate in the number of times a subquery is executed, which seems to be our case.

Recoding the subquery as suggested, using NOT EXISTS instead of NOT IN, made no change.

sp_options shows store_index is set, which is the default. That is, reformatting is not banned by this setting. In fact, we could force reformatting by setting basic_optimization:

set basic_optimization on

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using Serial Mode

    STEP 1

        The type of query is SELECT.

  8 operator(s) under root

       |ROOT:EMIT Operator (VA = 😎

       |

       |   |SEQUENCER Operator (VA = 7) has 2 children.

       |   |

       |   |   |STORE Operator (VA = 2)

       |   |   |  Worktable1 created, in allpages locking mode, for REFORMATTING.

       |   |   |  Creating clustered index.

       |   |   |

       |   |   |   |INSERT Operator (VA = 1)

       |   |   |   |  The update mode is direct.

       |   |   |   |

       |   |   |   |   |SCAN Operator (VA = 0)

       |   |   |   |   |  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.

       |   |   |   |

       |   |   |   |  TO TABLE

       |   |   |   |  Worktable1.

       |   |

       |   |   |SQFILTER Operator (VA = 6) has 2 children.

       |   |   |

       |   |   |   |SCAN Operator (VA = 3)

       |   |   |   |  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 = 5)

       |   |   |   |  Evaluate Ungrouped ANY AGGREGATE.

       |   |   |   |  Scanning only up to the first qualifying row.

       |   |   |   |

       |   |   |   |   |SCAN Operator (VA = 4)

       |   |   |   |   |  FROM TABLE

       |   |   |   |   |  Worktable1.

       |   |   |   |   |  Using Clustered Index.

       |   |   |   |   |  Forward Scan.

       |   |   |   |   |  Positioning by key.

       |   |   |   |   |  Using I/O Size 16 Kbytes for data pages.

       |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

       |   |   |

       |   |   |  END OF QUERY PLAN FOR SUBQUERY 1.

Table: Worktable1 scan count 1, logical reads: (regular=12 apf=0 total=12), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: Worktable1 scan count 0, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: TOPERAFR scan count 1, logical reads: (regular=897313 apf=2998 total=900311), physical reads: (regular=8 apf=214761 total=214769), apf IOs used=214761

Table: TCURDIVS scan count 1, logical reads: (regular=52 apf=0 total=52), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: Worktable1 scan count 27, logical reads: (regular=71 apf=0 total=71), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Server Message:  Number  3631, Severity  10

Server 'SDSTRHA01_SY11', Line 1:

Total actual I/O cost for this command: 7170093.

Total writes for this command: 0

Execution Time 103.

Adaptive Server cpu time: 12318 ms.  Adaptive Server elapsed time: 873430 ms.

(1614 rows affected)

In the statistics part of the report, Worktable1 appears three times, one of them having scan count 27. This is the number of distinct DIV_CD values in TCURDIVS in this database. Was 44 in the v12.5 database. Reformatting into Worktable1 costs an expensive TOPERAFR tablescan, but we don't pretend to improve this since we don't want to create new indexes.

So, we have a minimum of two choices: either setting basic_optimization or creating a temp table with the small results of the subquery.

In general terms, there is an answer to the question about 15.7 recurring less to a reformatting strategy: yes, because 15.7 might use merge-join and other advanced mechanisms where 12.5 would have used reformatting. When I ran a similar query coding IN instead of NOT IN, a merge join was what I got.

Thanks again,

Mariano Corral