I could use some help regarding the optimizer choices for the below update. I expected a direct update, not a deferred update.
In real live this concerns a target table containing ~5M rows being updated from a smaller table with ~200k rows. The deferred update takes quit some time, also because the target table has about 60 columns.
Any suggestions explaining this behavior are very welcome, including suggestions to improve performance of this query. The smaller table can easily be modified, changes to the target table have more impact as it is part of the product datamodel.
Many thanks!
Elroy
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.
Generally speaking, to get a direct update you need to have the table being updated come first in the query plan ... though, ideally, in this case the optimizer should be able to figure out that a) you're joining the tables based on 2 unique indexes (ie, guaranteed to have a 1-to-1 match) so b) a direct update would be preferable.
There are a few ways to get a direct update ... and assuming you don't want to wrap the query in a pair of set forceplan on/off commands, and you don't want to add an abstract query plan (AQP - optimizer hints), you could try something like:
-- 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.
Whether or not this is better/faster than a deferred update will depend on the volume of rows that have to be scanned from tt plus the number of joins to tf1 and tf2.
Also keep in mind that no amount of tricks may help if you've got an trigger on the table, or you find yourself updating an indexed column.