Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2011
    Posts
    9

    Question Unanswered: Query performance tuning help needed

    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

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Did you run update index statistics on all tables after migration?

  3. #3
    Join Date
    Feb 2011
    Posts
    9
    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

  4. #4
    Join Date
    Feb 2011
    Posts
    9

    Query plan for ASE 12.5

    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.

  5. #5
    Join Date
    Feb 2011
    Posts
    9

    Query plan for ASE 15.5

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •