sql语句， 谁帮忙优化一下啊，数据多了太慢啦

[ymf5555]

select P_ID,P_Name,
(select isnull(sum(POI_Number),0) from dbo.ProductOrderInformation(订单表) where POI_ProductID=P_ID and POI_AddDate between
convert(nvarchar(10),'2011-02-26',120) and convert(nvarchar(10),'2011-07-30',120)) as SumNumber,
(select isnull(sum(POI_Number*POI_Price),0) from dbo.ProductOrderInformation where POI_ProductID=P_ID
and POI_AddDate between convert(nvarchar(10),'2011-02-26',120) and convert(nvarchar(10),'2011-07-30',120)) as SumMoney,
round((select isnull(sum(POI_Number*POI_Price),0) from dbo.ProductOrderInformation where POI_ProductID=P_ID and POI_AddDate between
convert(nvarchar(10),'2011-02-26',120) and convert(nvarchar(10),'2011-07-30',120))/datediff(day,convert(varchar(10),'2011-02-26',120),
convert(varchar(10),'2011-07-30')),2) as EveryMoney,
((select isnull(sum(SOI_Number),0) from dbo.ShopperProductInformation where SOI_PID=P_ID)
+
(select isnull(sum(SP_StockNumber),0) from dbo.StockProductInformation where SP_PID=P_ID)
-
(select isnull(Sum(PDR_Number),0) from dbo.ProductDamagedReturn where PDR_ProductID=P_ID and PDR_Type = 1)
+
(select isnull(Sum(PDR_Number),0) from dbo.ProductDamagedReturn where PDR_ProductID=P_ID and PDR_Type = 2)
+
(select isnull(Sum(PDR_Number),0) from dbo.ProductDamagedReturn where PDR_ProductID=P_ID and PDR_Type = 3)
-
(select isnull(Sum(PDR_Number),0) from dbo.ProductDamagedReturn where PDR_ProductID=P_ID and PDR_Type = 4)
-
(select isnull(Sum(PUT_Number),0) from dbo.StoragePutInformation where PUT_ProductID=P_ID and PUT_Type = 1 or PUT_Type = 2)) as LastNumber
from dbo.ProductInformation(商品表)
where P_ClassID in (select PC_ID from dbo.ProductClass(商品类别) where PC_ParentID=1)

[ymf5555]

这两天咋没人帮忙呢

[唐诗三百首]

原则是把上面的子查询挪到from后面与ProductInformation做联接.

[ymf5555]

select a.P_ID, a.P_Name, isnull(sum(b.POI_Number),0) as SumNumber,
isnull(sum(b.POI_Number*b.POI_Price),0) as SumMoney
from dbo.ProductOrderInformation b, ProductInformation a
where b.POI_ProductID=a.P_ID
and a.P_ClassID in (select PC_ID from dbo.ProductClass where PC_ParentID=1)
group by a.P_ID, a.P_Name

改了 还是慢

[Andy-W]

先通過SQL Server Execution_Plans 檢查計劃成本最高的部份，再從那一部份入手，改善具體的表索引。

[-晴天]

把 ProductOrderInformation,ShopperProductInformation,StockProductInformation,ProductDamagedReturn,StoragePutInformation与
ProductInformation 连接后进行分组统计.

[cd731107]

select a.P_ID, a.P_Name, isnull(sum(b.POI_Number),0) as SumNumber,
isnull(sum(b.POI_Number*b.POI_Price),0) as SumMoney
from dbo.ProductOrderInformation b, ProductInformation a,dbo.ProductClass c
where b.POI_ProductID=a.P_ID
and a.P_ClassID =c.PC_ID and c.PC_ParentID=1
group by a.P_ID, a.P_Name

[ymf5555]

还是很慢

[ASPNETCHENGXU]

你能能不能，把版派的好看一些，这么乱看了就烦。。。