高手看一下这句SQL是否可以再优化 急
select khdm,
(case when
max(isnull(yy_xshkxx.xsxtrq,''))>=max(isnull(yy_xshkxx.hkrq,'*'))
then max(isnull(yy_xshkxx.xsxtrq,''))
else max(isnull(yy_xshkxx.hkrq,'*'))
end)
from yy_xshkxx (index pk_yy_xshkxx)
where isnull(yy_xshkxx.cdbz,'')=''
group by khdm
表yy_xshkxx有270万条数据,pk_yy_xshkxx是群集索引包含xsxtrq,hkrq,khdm
执行它我花了6分钟。请教一下可否再优化一下。把时间降下来。
showplan 显示为
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped MAXIMUM AGGREGATE.
Evaluate Grouped MAXIMUM AGGREGATE.
FROM TABLE
yy_xshkxx
Nested iteration.
Using Clustered Index.
Index : pk_yy_xshkxx
Ascending scan.
Positioning at start of table.
Using I/O Size 2 Kbytes.
With MRU Buffer Replacement Strategy.
TO TABLE
Worktable1.