Hi All,
I am experiencing a performance issue for a batch process after migration on ASE 15.5 from 12.5. The batch process used to take just 4 mins on ASE 12.5, now it hangs up for 3 days. The batch process does execute few query very fast but as soon as it come across one of the table, it get hang. But after creating an index on the table the batch moves ahead. I don't understand why should I create an index on the table if the batch works fine without index on 12.5. I also tried using different optimization goals, force plan on, merge_join off but it didn't help me. The query looks like something as below :

Select Distinct
trade_id,
deal_id,
maturity_date,
product,
columna,
columb
from
problemtable a,
term b,
product_classification c
where
a.term_id=b.term_id and
a.product_id=c.product_id and
/* the below sub query is problematic, on creating index on trade_id, it run fast */
convert(datetime,maturity_date) in ( select max(convert(datetime,maturity_date)) from problemtable d
where
a.trade_id=d.trade_id)

Please suggest

Did you run update index statistics on all tables after migration?

Yes, I had ran update index statistics only on the problem table(Not sure if update statistics was run by my mates).... But If I replace the problem table with some other table(Instrument table ex Fx,IR,Forward,etc) in the same query...the query executes smoothly....I tried recreating the table but didn't help too....I do saw the query plans but didn't go thru them in depth

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET STATISTICS ON.

Total estimated I/O cost for statement 1 (at line 1): 0.

Total actual I/O cost for this command: 0.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.

Total estimated I/O cost for statement 1 (at line 1): 0.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total actual I/O cost for this command: 0.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 2 worker processes.


STEP 1
The type of query is INSERT.
The update mode is direct.
Executed by coordinating process.
Worktable1 created for REFORMATTING.

FROM TABLE
mydb..problemtable
d
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 INSERT.
The update mode is direct.
Executed by coordinating process.
Worktable3 created for REFORMATTING.

FROM TABLE
mydb..product_classification
b
Nested iteration.
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.
TO TABLE
Worktable3.

STEP 3
The type of query is INSERT.
The update mode is direct.
Executed by coordinating process.
Worktable4 created for REFORMATTING.

FROM TABLE
mydb..term
c
Nested iteration.
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.
TO TABLE
Worktable4.

STEP 4
The type of query is INSERT.
The update mode is direct.
Executed in parallel by coordinating process and 2 worker processes.
Worktable2 created, in allpages locking mode, for DISTINCT.

FROM TABLE
mydb..problemtable
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 2-way hash scan.

Run subquery 1 (at nesting level 1).
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable3.
Nested iteration.
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.

FROM TABLE
Worktable4.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable2.

STEP 5
The type of query is SELECT.
Executed by coordinating process.
This step involves sorting.

FROM TABLE
Worktable2.
Using GETSORTED
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.
STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 22).

Correlated Subquery.
Subquery under an IN predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped MAXIMUM AGGREGATE.
Executed by 2 worker processes.

FROM TABLE
Worktable1.
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.

STEP 2
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.
Executed by 2 worker processes.

END OF QUERY PLAN FOR SUBQUERY 1.


Total estimated I/O cost for statement 1 (at line 1): 25082972.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

Total estimated I/O cost for statement 1 (at line 1): 0.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total actual I/O cost for this command: 0.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET STATISTICS OFF.

Total estimated I/O cost for statement 1 (at line 1): 0.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

Total estimated I/O cost for statement 1 (at line 1): 0.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET STATISTICS ON.

Total estimated I/O cost for statement 1 (at line 1): 0.

Total actual I/O cost for this command: 0.
Total writes for this command: 0

Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.

Total estimated I/O cost for statement 1 (at line 1): 0.

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total actual I/O cost for this command: 0.
Total writes for this command: 0

Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 3 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode


STEP 1
The type of query is SELECT.

17 operator(s) under root

|ROOT:EMIT Operator (VA = 17)
|
| |SEQUENCER Operator (VA = 16) 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
| | | | | mydb..problemtable
| | | | | a
| | | | | 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.
| |
| | |HASH DISTINCT Operator (VA = 15)
| | | Using Worktable2 for internal storage.
| | | Key Count: 8
| | |
| | | |SQFILTER Operator (VA = 14) has 2 children.
| | | |
| | | | |N-ARY NESTED LOOP JOIN Operator (VA = 7) has 3
children.
| | | | |
| | | | | |SCAN Operator (VA = 3)
| | | | | | FROM TABLE
| | | | | | mydb..product_classification
| | | | | | b
| | | | | | 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.
| | | | |
| | | | | |SCAN Operator (VA = 4)
| | | | | | FROM TABLE
| | | | | | mydb..term
| | | | | | c
| | | | | | Using Clustered Index.
| | | | | | Index : cluster_idx1
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Keys are:
| | | | | | term_id ASC
| | | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | | With LRU Buffer Replacement Strategy for data
pages.
| | | | |
| | | | | |RESTRICT Operator (VA = 6)(6)(0)(0)(6)(0)
| | | | | |
| | | | | | |SCAN Operator (VA = 5)
| | | | | | | FROM TABLE
| | | | | | | Worktable1.
| | | | | | | Using Clustered Index.
| | | | | | | Forward Scan.
| | | | | | | Positioning by key.
| | | | | | | Using I/O Size 2 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
22).
| | | |
| | | | Correlated Subquery.
| | | | Subquery under an IN predicate.
| | | |
| | | | |SEQUENCER Operator (VA = 13) has 2 children.
| | | | |
| | | | | |SCALAR AGGREGATE Operator (VA = 10)
| | | | | | Evaluate Ungrouped MAXIMUM AGGREGATE.
| | | | | |
| | | | | | |RESTRICT Operator (VA = 9)(0)(0)(0)(3)(0)
| | | | | | |
| | | | | | | |SCAN Operator (VA = 8)
| | | | | | | | FROM TABLE
| | | | | | | | mydb..problemtable
| | | | | | | | d
| | | | | | | | 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.
| | | | |
| | | | | |SCALAR AGGREGATE Operator (VA = 12)
| | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | Scanning only up to the first qualifying row.
| | | | | |
| | | | | | |SCALAR Operator (VA = 11)
| | | |
| | | | END OF QUERY PLAN FOR SUBQUERY 1.


Total estimated I/O cost for statement 1 (at line 1): 2147483647.

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.

Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

Total estimated I/O cost for statement 1 (at line 1): 0.

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Total actual I/O cost for this command: 0.
Total writes for this command: 0

Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET STATISTICS OFF.

Total estimated I/O cost for statement 1 (at line 1): 0.

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

Total estimated I/O cost for statement 1 (at line 1): 0.