Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Angry Unanswered: Poor Order By Performance in 12.5 vs. 11.9.x

    I have two identicle images of data on 2 servers, one running under 11.9.2 and the other under 12.5. Both servers have identicle hardware specs. When performing a simple select statement with one join and an order by on a column that is a clustered index of the primary table, 12.5 always creates a Worktable while 11.9.2 avoids the worktable as expected by using the clustered index. The use of the worktable in 12.5 makes the query run almost twice as long.

    Any thoughts as to why 12.5.0 insists on creating a work table for sorting when the column being sorted on is a clustered index in the primary table? I'm stumped as to why the optimizer is ignoring the index.

    Here is the query and the plans for both 11.9.2 and 12.5.0

    select
    x.EmployeeId, -- clustered index in Employee_tbl
    x.FirstName,
    y.OrderNum -- clustered index in Order_tbl
    from
    Employee_tbl x
    left outer join --same behavior exists for inner joins
    Order_tbl y
    on
    x.EmployeeId = y.EmployeeId
    order by x.a

    -- show plan for 11.9.2

    STEP 1
    The type of query is SELECT.
    FROM TABLE
    Employee_tbl
    x
    Nested iteration.
    Using Clustered Index.
    Index : cl_empid_idx
    Forward scan.
    Positioning at index start.
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    FROM TABLE
    Order_tbl
    y
    Nested iteration.
    Using Clustered Index.
    Index : cl_order_idx
    Forward scan.
    Positioning by key.
    Keys are:
    EmployeeId ASC
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    -- show plan for 12.5.0
    STEP 1
    The type of query is INSERT.
    The update mode is direct.
    Executed in parallel by coordinating process and 2 worker processes.
    Worktable1 created, in allpages locking mode, for ORDER BY.
    FROM TABLE
    Employee_tbl
    x
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Executed in parallel with a 2-way hash scan.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    FROM TABLE
    Order_tbl
    y
    Nested iteration.
    Using Clustered Index.
    Index : cl_order_idx
    Forward scan.
    Positioning by key.
    Keys are:
    EmployeeId
    ASC
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    Worktable1.
    STEP 2
    The type of query is SELECT.
    Executed by coordinating process.
    This step involves sorting.
    FROM TABLE
    Worktable1.
    Using GETSORTED
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 2 Kbytes for data pages.
    With MRU Buffer Replacement Strategy for data pages.

  2. #2
    Join Date
    Nov 2003
    Location
    Nigeria
    Posts
    3

    Re: Poor Order By Performance in 12.5 vs. 11.9.x

    It is likely that the index is ignored because statistics is not up to date for the index. Try generating statistics for all columns by running

    update index statistics for the index.

    Also, ensure that you use the right datatypes for search arguments in your where clauses. LHS datatypes must be higher in hierarchy compared to SARGS

Posting Permissions

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