使用了povit，请问个关于性能的有关问题，小弟我一直没搞明白原因

使用了povit，请教个关于性能的问题，我一直没搞明白原因
当我的SQL是这样时，响应时间竟然超了过1分钟

SQL code

select alias,market,today.[中石油] as 'today中石油',today.[中石化] as 'today中石化',today.[中海油] as 'today中海油',today.[社会单位] as 'today社会单位' from Dic_Organization left join (select * from (select Province,Organization,Petrol93 from Sys_ReportData_01 left join Sys_GasStationInfo on Sys_ReportData_01.ObjectId = Sys_GasStationInfo.ID where Sys_ReportData_01.ReportDate>='2012-08-14 00:00:00' and Sys_ReportData_01.ReportDate<='2012-08-14 23:59:59')todaytemp pivot(avg(Petrol93)for Organization in ([中石油],[中石化],[中海油],[社会单位]))todayPivot )todayon today.Province=Dic_Organization.IDleft join (select * from (select Province,Organization,Petrol93 from Sys_ReportData_01 left join Sys_GasStationInfo on Sys_ReportData_01.ObjectId = Sys_GasStationInfo.ID where Sys_ReportData_01.ReportDate>='2012-08-13 00:00:00' and Sys_ReportData_01.ReportDate<='2012-08-13 23:59:59')todaytemp pivot(avg(Petrol93)for Organization in ([中石油],[中石化],[中海油],[社会单位]))lastdayPivot) lastday on lastday.Province=Dic_Organization.ID where CategoryID = 4

然后我在select查询结果中，添加了几个取的字段，响应时间就小于1秒，很奇怪
这是我添加的：

SQL code

lastday.[中石油] as 'lastday中石油',lastday.[中石化] as 'lastday中石化',lastday.[中海油] as 'lastday中海油',lastday.[社会单位] as 'lastday社会单位'

结果SQL就是这样

SQL code

select alias,market,today.[中石油] as 'today中石油',today.[中石化] as 'today中石化',today.[中海油] as 'today中海油',today.[社会单位] as 'today社会单位',lastday.[中石油] as 'lastday中石油',lastday.[中石化] as 'lastday中石化',lastday.[中海油] as 'lastday中海油',lastday.[社会单位] as 'lastday社会单位' from Dic_Organization left join (select * from (select Province,Organization,Petrol93 from Sys_ReportData_01 left join Sys_GasStationInfo on Sys_ReportData_01.ObjectId = Sys_GasStationInfo.ID where Sys_ReportData_01.ReportDate>='2012-08-14 00:00:00' and Sys_ReportData_01.ReportDate<='2012-08-14 23:59:59')todaytemp pivot(avg(Petrol93)for Organization in ([中石油],[中石化],[中海油],[社会单位]))todayPivot )todayon today.Province=Dic_Organization.IDleft join (select * from (select Province,Organization,Petrol93 from Sys_ReportData_01 left join Sys_GasStationInfo on Sys_ReportData_01.ObjectId = Sys_GasStationInfo.ID where Sys_ReportData_01.ReportDate>='2012-08-13 00:00:00' and Sys_ReportData_01.ReportDate<='2012-08-13 23:59:59')todaytemp pivot(avg(Petrol93)for Organization in ([中石油],[中石化],[中海油],[社会单位]))lastdayPivot) lastday on lastday.Province=Dic_Organization.ID where CategoryID = 4

[解决办法]
你可以看一下这两句代码的执行计划，有什么不同。快是快在哪个阶段。
[解决办法]
可能那几个字段上有索引，走了索引就快，上面那个就没有我是这样猜测的。
[解决办法]
嵌套查询太多了，
select Province,Organization,Petrol93 
from Sys_ReportData_01 
 left join Sys_GasStationInfo on Sys_ReportData_01.ObjectId = Sys_GasStationInfo.ID 
这个拿出来放到临时表里，再二次查找，建议改存储过程，另外pivot效率的确不会很高，改为简单的聚合试试。

查看更多 下一篇