SQL1:
select sLY,sum(fZJE) from (
select sLY,sum(fZJE) as fZJE from DB1..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30' group by sLY
union all
select sLY,sum(fZJE) as fZJE from DB2..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30' group by sLY)a
group by sLYSQL2:
select sLY,sum(fZJE) from (
select * from DB1..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30'
union all
select * from DB2..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30')a
group by sLYSQL3:
select sLY,sum(fZJE) from (
select * from DB1..MXB
union all
select * from DB2..MXB)a
where dSJ>'2009-10-1' and dSJ<'2010-4-30'
group by sLYMXB数据为200w~400w左右,请问,在SQL2000下,哪个SQL效率最高?
select sLY,sum(fZJE) from (
select sLY,sum(fZJE) as fZJE from DB1..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30' group by sLY
union all
select sLY,sum(fZJE) as fZJE from DB2..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30' group by sLY)a
group by sLYSQL2:
select sLY,sum(fZJE) from (
select * from DB1..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30'
union all
select * from DB2..MXB where dSJ>'2009-10-1' and dSJ<'2010-4-30')a
group by sLYSQL3:
select sLY,sum(fZJE) from (
select * from DB1..MXB
union all
select * from DB2..MXB)a
where dSJ>'2009-10-1' and dSJ<'2010-4-30'
group by sLYMXB数据为200w~400w左右,请问,在SQL2000下,哪个SQL效率最高?
2.分别旋转三条语句,ctrl + L 查看执行计划,看看有没有table scan的地方,有的话将
相关字段加上index
set statistics time on
执行的SQL语句
set statistics time off
set statistics io off
sXM字段不是索引,我查看执行计划,也没table scan呀,显示Clustered Index Scan
--查看计划
SET SHOWPLAN_ALL ON;SET SHOWPLAN_ALL OFF; /*
注意PhysicalOP,通过EstimateIO、EstimateCPU等得到更确切的数据描述。
*/