Sybase，A left join B 与 B left join A 速度相差太大，并且执行计划也不一样，求解

moses3017 · 2010-5-23 23:36:49

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？站点注册

×

Sybase数据库，A表数据量122806，B表数据量198161，通过N_STORAGE_ID字段做left join，此字段在A、B两张表中均不是主键，A、B表均有主键，没有索引。
1） SELECT count(*) FROM B b LEFT JOIN A a ON a.N_STORAGE_ID = b.N_STORAGE_ID 执行时间2s
2） SELECT count(*) FROM A a LEFT JOIN B b ON a.N_STORAGE_ID = b.N_STORAGE_ID 执行时间：半个小时搞不定
执行计划：
Sql代码

第一种情况 1）
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is EXECUTE.
Executing a previously cached statement.
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 6 worker processes.
STEP 1
The type of query is INSERT.
The update mode is direct.
Executed in parallel by coordinating process and 6 worker processes.
Worktable1 created for REFORMATTING.
FROM TABLE
XZ_GLY_LDJLB
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 6-way hash scan.
Using I/O Size 128 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
Executed in parallel by coordinating process and 6 worker processes.
FROM TABLE
IDLE_DEVICE
b
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 6-way hash scan.
Using I/O Size 128 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
Worktable1.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 128 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
Parallel result buffer merge.
STEP 3
The type of query is SELECT.
Executed by coordinating process.
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.

复制代码

Sql代码

第二种情况 2）
2)QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is EXECUTE.
Executing a newly cached statement.
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 6 worker processes.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
Executed in parallel by coordinating process and 6 worker processes.
FROM TABLE
XZ_GLY_LDJLB
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 6-way hash scan.
Using I/O Size 128 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
IDLE_DEVICE
b
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 128 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
Parallel result buffer merge.
STEP 2
The type of query is SELECT.
Executed by coordinating process.
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.

复制代码

这让我苦思不得其解，大家帮忙啊

shakeone · 2010-5-27 21:27:58

第一个plan:
step1: 为A建了个worktable，这个worktable有个clustered index
step2: nested loop join, inner table是这个work table的clustered index, outer table是B，所以这样的join是很快的，因为inner table很小，可以一直cache in memory
step3: 输出结果
第二个plan：
没有使用刚才建立的worktable的index，同样的nested loop join，inner table为B，outer table为A，所以性能很差，因为B不能被cache in memory，I/O很多次。
所以关键还是execution/optimizer做的太滥，给你个work arround，就是第一个执行完毕后，drop table XZ_GLY_LDJLB

shakeone · 2010-5-27 21:28:55

你用的估计是15x，我都觉得15x的qp做得烂了

账号		站点注册	自动登录	找回密码
密码			站点注册