使用了povit，请问个关于性能的有关问题，小弟我一直没搞明白原因-MSSQL教程-爱易网页

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

日期：2014-05-17　浏览次数：20463 次

使用了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 )today
on today.Province=Dic_Organization.ID
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-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 )today
on today.Province=Dic_Organization.ID
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-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效率的确不会很高，改为简单的聚合试试。

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>