时间戳

首页 > 解决方案 > sybase ase 延迟更新模式

sql-update - sybase ase 延迟更新模式

问题描述

对于以下更新，我可以使用一些关于优化器选择的帮助。我希望直接更新，而不是延迟更新。

在现实生活中，这涉及一个包含约 5M 行的目标表，该目标表是从一个具有约 200k 行的较小表中更新的。延迟更新需要一些时间才能退出，这也是因为目标表有大约 60 列。

非常欢迎任何解释此行为的建议，包括提高此查询性能的建议。较小的表可以轻松修改，对目标表的更改会产生更大的影响，因为它是产品数据模型的一部分。

非常感谢！

埃尔罗伊

select @@version --Adaptive Server Enterprise/15.5/EBF 18158 SMP ESD#2/P/X64/Windows Server/asear155/2514/64-bit/OPT/Wed Aug 25 05:39:57 2010

IF OBJECT_ID('test_target') IS NOT NULL
DROP TABLE test_target
GO
CREATE TABLE test_target  ( 
    id              numeric(15,0) IDENTITY NOT NULL,
    col1            numeric(15,0) NOT NULL,
    col2            char(15) NOT NULL,
CONSTRAINT PK_test_target PRIMARY KEY CLUSTERED(id)
    )
LOCK DATAPAGES
go
insert into test_target select 1, '123'
insert into test_target select 1, '456'
go

IF OBJECT_ID('test_from') IS NOT NULL
DROP TABLE test_from
GO
CREATE TABLE test_from  ( 
    from_id         numeric(15,0) NOT NULL,
    from_col2    char(15) NOT NULL
    )
LOCK ALLPAGES
GO
insert into test_from select 1,'1'
go
create unique clustered index k1 on test_from (from_id)
go

set showplan on
go

update test_target 
set col2 = from_col2
from test_target
join test_from on from_id = id 

set showplan off
go

QUERY PLAN FOR STATEMENT 1 (at line 1).


 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: Inner Join)
     |   |   |
     |   |   |   |SCAN Operator (VA = 0)
     |   |   |   |  FROM TABLE
     |   |   |   |  test_from
     |   |   |   |  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
     |   |   |   |  test_target
     |   |   |   |  Using Clustered Index.
     |   |   |   |  Index : PK_test_target
     |   |   |   |  Forward Scan.
     |   |   |   |  Positioning by key.
     |   |   |   |  Keys are:
     |   |   |   |    id ASC
     |   |   |   |  Using I/O Size 16 Kbytes for index leaf pages.
     |   |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
     |   |   |   |  Using I/O Size 16 Kbytes for data pages.
     |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
     |   |
     |   |  TO TABLE
     |   |  test_target
     |   |  Using I/O Size 16 Kbytes for data pages.

标签： sql-updatequery-optimizationsybasedatabase-performancesap-ase

解决方案

一般来说，要获得direct更新，您需要先将要更新的表放在查询计划中......但是，理想情况下，在这种情况下，优化器应该能够弄清楚 a) 您正在加入基于表的表2 个唯一索引（即，保证具有 1 对 1 匹配）因此 b)direct更新将是可取的。

有几种方法可以获得direct更新......假设您不想将查询包装在一对set forceplan on/off命令中，并且您不想添加抽象查询计划（AQP - 优化器提示），您可以尝试类似：

-- for test purposes, make sure optimizer isn't re-using query plan from previous test run
set statement_cache off
go

set showplan on
go

update test_target

                 -- push the join into a sub-query
set    tt.col2 = (select tf1.from_col2 from test_from tf1 where tf1.from_id = tt.id)

from   test_target tt

where  -- only process tt rows that have a match in tf2
       exists(select 1 from test_from tf2 where tf2.from_id = tt.id)
go

STEP 1
    The type of query is UPDATE.

    8 operator(s) under root

   |ROOT:EMIT Operator (VA = 8)
   |
   |   |UPDATE Operator (VA = 7)
   |   |  The update mode is direct.
   |   |
   |   |   |SQFILTER Operator (VA = 6) has 2 children.
   |   |   |
   |   |   |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
   |   |   |   |
   |   |   |   |   |GROUP SORTED Operator (VA = 1)
   |   |   |   |   |Distinct
   |   |   |   |   |
   |   |   |   |   |   |SCAN Operator (VA = 0)
   |   |   |   |   |   |  FROM TABLE
   |   |   |   |   |   |  test_from
   |   |   |   |   |   |  tf2
   |   |   |   |   |   |  Table Scan.
   |   |   |   |   |   |  Forward Scan.
   |   |   |   |   |   |  Positioning at start of table.
   |   |   |   |   |   |  Using I/O Size 2 Kbytes for data pages.
   |   |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
   |   |   |   |
   |   |   |   |   |SCAN Operator (VA = 2)
   |   |   |   |   |  FROM TABLE
   |   |   |   |   |  test_target
   |   |   |   |   |  tt
   |   |   |   |   |  Using Clustered Index.
   |   |   |   |   |  Index : PK_test_target
   |   |   |   |   |  Forward Scan.
   |   |   |   |   |  Positioning by key.
   |   |   |   |   |  Keys are:
   |   |   |   |   |    id 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.
   |   |   |
   |   |   |  Run subquery 1 (at nesting level 1).
   |   |   |
   |   |   |  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 11).
   |   |   |
   |   |   |   Correlated Subquery.
   |   |   |   Subquery under an EXPRESSION predicate.
   |   |   |
   |   |   |   |SCALAR AGGREGATE Operator (VA = 5)
   |   |   |   |  Evaluate Ungrouped ONCE AGGREGATE.
   |   |   |   |
   |   |   |   |   |SCAN Operator (VA = 4)
   |   |   |   |   |  FROM TABLE
   |   |   |   |   |  test_from
   |   |   |   |   |  tf1
   |   |   |   |   |  Using Clustered Index.
   |   |   |   |   |  Index : k1
   |   |   |   |   |  Forward Scan.
   |   |   |   |   |  Positioning by key.
   |   |   |   |   |  Keys are:
   |   |   |   |   |    from_id ASC
   |   |   |   |   |  Using I/O Size 2 Kbytes for data pages.
   |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
   |   |   |
   |   |   |  END OF QUERY PLAN FOR SUBQUERY 1.
   |   |
   |   |  TO TABLE
   |   |  test_target
   |   |  Using I/O Size 2 Kbytes for data pages.

这是否比延迟更新更好/更快将取决于必须从 tt 扫描的行数加上到 tf1 和 tf2 的连接数。

还要记住，如果您在表上有触发器，或者您发现自己正在更新索引列，那么再多的技巧也无济于事。

推荐阅读