Sybase专家帮助：groupby聚合性能问题
嘿我有以下表和SQL：
T1：ID，col2，col3-PK（ID） - 23mil行
T2：ID，col2，col3-PK（ID） - 23mil行
T3：ID，名称，值 - PK（ID，名称）-66mil行
1）下面的sql非常快速地返回10k行结果集，没有问题。
select top 10000 T1.col2, T2.col2, T3.name, T4.value
from T1, T2, T3
where T1.ID = T2.ID and T1.ID *= T3.ID and T3.name in ('ABC','XYZ')
and T2.col1 = 'SOMEVALUE'
select top 10000 T1.col2, T2.col2,
ABC = min(case when T3.name='ABC ' then T3.value end)
XYZ = min(case when T3.name='XYZ ' then T3.value end)
from T1, T2, T3
where T1.ID = T2.ID and T1.ID *= T3.ID and T3.name in ('ABC','XYZ')
and T2.col1 = 'SOMEVALUE'
group by T1.col2, T2.col2,
STEP 1
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped MINIMUM AGGREGATE.
FROM TABLE ...etc..
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
FROM TABLE
Worktable1.
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
没有找到相关结果
已邀请:
1 个回复
膏焦凑
Select my_derived_table.t1col2, my_derived_table.t2col2, ABC = min(case when my_derived_table.t3name ='ABC ' then my_derived_table.t3value end), XYZ = min(case when my_derived_table.t3name='XYZ ' then my_derived_table.t3value end) FROM (Select top 10000 T1.col2 as t1col2, T2.col2 as t2col2, t3.name as t3name, t3.value as t3.value from T1, T2, T3 where T1.ID = T2.ID and T1.ID *= T3.ID and T3.name in ('ABC','XYZ') and T2.col1 = 'SOMEVALUE') my_derived_table group by my_derived_table.t1col2, my_derived_table.t2col2