ALTER proc StatByProduct
@ChainID as uniqueidentifier=null,
@ShopID as uniqueidentifier=null,
@CPID as uniqueidentifier=null,
@ProductType as tinyint=null,
@ItemResult as tinyint=null,
@ItemType as tinyint=null,
@CustermerName as nvarchar(50)=null,
@TimeBegin as datetime=null,
@TimeEnd as datetime=null,
@OrderStatus as tinyint=2,
@Subtotal as tinyint=0,
@Username as nvarchar(50)=null,
@PhoneNumber as nvarchar(50)=null,
@ModelName as nvarchar(50)=null,
@ProductCode as int=null,
@ProductName as nvarchar(50)=null,
@SuiteName as nvarchar(50)=null,
@Rebate as int=null,
@ReCommended as tinyint=null
as
if @Subtotal=0
begin
select e.Code,a.ProductID,e.ProductName,g.ItemDisplayName as ProductType,h.CPName,c.ShopName,i.ChainName,f.ItemDisplayName as ItemResult,a.OriginalPrice,a.Rebate/10 as Discount,a.Price,d.Description,a.SuitePrice,b.TimeCreated,b.UserName,b.OrderNumber,a.TimeCreated
from OrderItem a
inner join [Order] b on a.OrderID=b.OrderID
inner join Shop c on b.ShopID=c.ShopID
inner join Customer d on b.CustomerID=d.CustomerID
inner join Product e on a.ProductID=e.ProductID
inner join Dictionary f on a.ItemResult=f.ItemValue and f.GroupName='ItemResult'
inner join Dictionary g on e.ProductType=g.ItemValue and g.GroupName='ProductType'
inner join ContentProvider h on e.CPID=h.CPID
inner join Chain i on i.ChainID=c.ChainID
inner join MobileModel j on j.ModelID=b.ModelID
where
(a.ItemType=@ItemType or @ItemType is null) and a.ItemType<>0
and (b.OrderStatus=@OrderStatus or @OrderStatus is null)
and (c.ChainID=@ChainID or @ChainID is null)
and (b.ShopID=@ShopID or @ShopID is null)
and (b.TimeCreated>=@TimeBegin or @TimeBegin is null)
and (b.TimeCreated<=@TimeEnd or @TimeEnd is null)
and (d.CustomerName=@CustermerName or @CustermerName is null)
and (a.ItemResult=@ItemResult or @ItemResult is null)
and (e.ProductType=@ProductType or @ProductType is null)
and (e.CPID=@CPID or @CPID is null)
and (b.UserName=@UserName or @UserName is null)
and (d.PhoneNumber=@PhoneNumber or @PhoneNumber is null)
and (j.ModelName=@ModelName or @ModelName is null)
and (e.Code=@ProductCode or @ProductCode is null)
and (a.ProductName=@ProductName or @ProductName is null)
and (a.SuiteName=@SuiteName or @SuiteName is null)
and (a.Rebate=@Rebate or @Rebate is null)
and (e.Recommended=@Recommended or @Recommended is null)


order by e.Code,c.ShopID,b.TimeCreated
end
else
begin
select e.Code,a.ProductID,e.ProductName,g.ItemDisplayName as ProductType,h.CPName,sum(a.OriginalPrice) as OriginalPrice,sum(a.Price) as Price,null as Description,sum(a.SuitePrice) as SuitePrice,e.PurchaseTimes
from OrderItem a
inner join [Order] b on a.OrderID=b.OrderID
inner join Shop c on b.ShopID=c.ShopID
inner join Customer d on b.CustomerID=d.CustomerID
inner join Product e on a.ProductID=e.ProductID
inner join Dictionary g on e.ProductType=g.ItemValue and g.GroupName='ProductType'
inner join ContentProvider h on e.CPID=h.CPID
inner join MobileModel j on j.ModelID=b.ModelID
where
(a.ItemType=@ItemType or @ItemType is null) and a.ItemType<>0
and (b.OrderStatus=@OrderStatus or @OrderStatus is null)
and (c.ChainID=@ChainID or @ChainID is null)
and (b.ShopID=@ShopID or @ShopID is null)
and (b.TimeCreated>=@TimeBegin or @TimeBegin is null)
and (b.TimeCreated<=@TimeEnd or @TimeEnd is null)
and (d.CustomerName=@CustermerName or @CustermerName is null)
and (a.ItemResult=@ItemResult or @ItemResult is null)
and (e.ProductType=@ProductType or @ProductType is null)
and (e.CPID=@CPID or @CPID is null)
and (b.UserName=@UserName or @UserName is null)
and (d.PhoneNumber=@PhoneNumber or @PhoneNumber is null)
and (j.ModelName=@ModelName or @ModelName is null)
and (e.Code=@ProductCode or @ProductCode is null)
and (a.ProductName=@ProductName or @ProductName is null)
and (a.SuiteName=@SuiteName or @SuiteName is null)
and (a.Rebate=@Rebate or @Rebate is null)
and (e.Recommended=@Recommended or @Recommended is null)
group by e.Code,a.ProductID,e.ProductName,g.ItemDisplayName,h.CPName,e.PurchaseTimes
order by e.Code
end

数据量由10万多条 执行太慢 谁有优化的想法或者提议 说下 谢谢