Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Optimizing UPDATE for two almost identical temp tables (350000 rows each of them)

    Base information:
    Adaptive Server Enterprise/15.0.3/EBF 17769 ESD#4/P/Sun_svr4/OS 5.8/ase1503/2768/64-bit/FBO/Thu Aug 26 15:08:16 2010

    #tmp_comm has 345626 rows
    1) The #counterparty temp table is created from #tmp_comm. #counterparty has the same 345626 rows.

    2) Next step: sub SP is used to populate columns "desc" and "code" of #counterparty temp table. Go back to the main SP.

    3) populate name and counterparty columns of #tmp_comm by values from #counterparty .

    Performance issue:
    Updating #tmp_comm table can take 10 minutes.
    I have faced with two cases with identical Query Plan.
    One of them runs ~1,5 minute (#1).
    The second one runs ~10 minutes (#2)

    ===
    {Query itself}
    Code:
    set statement_cache off
    
    create unique clustered index ix_temp on #counterparty(a_id, t_id, desc, code)
    
    update     #tmp_comm
    set     name         = c.desc,
        counterparty     = c.code        
    from #counterparty c 
    where c.a_id = #tmp_comm.a_id 
      and c.t_id = #tmp_comm.t_id

    variant #1
    QUERY PLAN FOR STATEMENT 1 (at line 34).


    STEP 1
    The type of query is UPDATE.

    4 operator(s) under root

    |ROOT:EMIT Operator (VA = 4)
    |
    | |UPDATE Operator (VA = 3)
    | | The update mode is deferred_varcol.
    | |
    | | |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Left Semi Join)
    | | |
    | | | |SCAN Operator (VA = 0)
    | | | | FROM TABLE
    | | | | #tmp_comm
    | | | | 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 = 1)
    | | | | FROM TABLE
    | | | | #counterparty
    | | | | c
    | | | | Using Clustered Index.
    | | | | Index : ix_temp
    | | | | Forward Scan.
    | | | | Positioning by key.
    | | | | Keys are:
    | | | | a_id ASC
    | | | | t_id ASC
    | | | | Using I/O Size 2 Kbytes for data pages.
    | | | | With LRU Buffer Replacement Strategy for data pages.
    | |
    | | TO TABLE
    | | #tmp_comm
    | | Using I/O Size 2 Kbytes for data pages.


    Parse and Compile Time 25.
    Adaptive Server cpu time: 2500 ms.
    Code:
    ==================== Lava Operator Tree ====================
    
                                         
                                        Emit
                                        (VA = 4)
                                        r:345626 er:530267
                                        cpu: 41500
    
    
                             /
                            Update
                            #tmp_comm
                            (VA = 3)
                            r:345626 er:530267
                            l:114274 el:0
                            p:0 ep:0
                 /
                NestLoopJoin
                Left Semi Join
                (VA = 2)
                r:345626 er:530267
    
    
    /                      \
    TableScan               IndexScan
    #tmp_comm               ix_temp_ (c)
    (VA = 0)                (VA = 1)
    r:345626 er:345626      r:345626 er:530267
    l:27435 el:27435        l:1.394e+06 el:1.383e+06
    p:0 ep:3431             p:0 ep:11094
    
    ============================================================

    Table: #tmp_comm scan count 0, logical reads: (regular=114274 apf=0 total=114274), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Table: #tmp_comm scan count 1, logical reads: (regular=27435 apf=0 total=27435), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Table: #counterparty scan count 345626, logical reads: (regular=1393594 apf=0 total=1393594), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Total writes for this command: 4416

    Execution Time 455.
    Adaptive Server cpu time: 45500 ms. Adaptive Server elapsed time: 89840 ms.
    The sort for Worktable1 is done in Serial


    Variant #2:

    QUERY PLAN FOR STATEMENT 1 (at line 36).


    STEP 1
    The type of query is UPDATE.

    4 operator(s) under root

    |ROOT:EMIT Operator (VA = 4)
    |
    | |UPDATE Operator (VA = 3)
    | | The update mode is deferred.
    | |
    | | |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Left Semi Join)
    | | |
    | | | |SCAN Operator (VA = 0)
    | | | | FROM TABLE
    | | | | #tmp_comm
    | | | | 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 = 1)
    | | | | FROM TABLE
    | | | | #counterparty
    | | | | c
    | | | | Using Clustered Index.
    | | | | Index : ix_temp
    | | | | Forward Scan.
    | | | | Positioning by key.
    | | | | Keys are:
    | | | | a_id ASC
    | | | | t_id ASC
    | | | | Using I/O Size 2 Kbytes for data pages.
    | | | | With LRU Buffer Replacement Strategy for data pages.
    | |
    | | TO TABLE
    | | #tmp_comm
    | | Using I/O Size 2 Kbytes for data pages.


    Parse and Compile Time 23.
    Adaptive Server cpu time: 2300 ms.

    Code:
    ==================== Lava Operator Tree ====================
    
                                         
                                        Emit
                                        (VA = 4)
                                        r:345626 er:525745
                                        cpu: 42200
    
    
                             /
                            Update
                            #tmp_comm
                            (VA = 3)
                            r:345626 er:525745
                            l:2.041e+06 el:0
                            p:0 ep:0
                 /
                NestLoopJoin
                Left Semi Join
                (VA = 2)
                r:345626 er:525745
    
    
    /                      \
    TableScan               IndexScan
    #tmp_comm               ix_temp (c)
    (VA = 0)                (VA = 1)
    r:345626 er:345626      r:345626 er:525745
    l:28240 el:28243        l:1.383e+06 el:1.383e+06
    p:0 ep:3531             p:0 ep:11094
    
    ============================================================
    Table: #tmp_comm scan count 0, logical reads: (regular=2041127 apf=0 total=2041127), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Table: #tmp_comm scan count 1, logical reads: (regular=28240 apf=0 total=28240), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Table: #counterparty scan count 345626, logical reads: (regular=1382504 apf=0 total=1382504), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Total writes for this command: 75985

    Execution Time 1001.
    Adaptive Server cpu time: 100100 ms. Adaptive Server elapsed time: 519233 ms.
    The sort for Worktable1 is done in Serial

    My question is:
    1) How we can improve above update?
    2) if improving is not possible how I can exclude the variant 2?

    Thanks

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    hi
    Try to add an index for the table #tmp_comm.
    But review if its not heavy to and a clustered index, if it is create a nonclustered.

    This will make you lose time with the creationf of the index but save a lot on the queries, such big tables.

    Hope it helps.

Posting Permissions

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