这个存储过程还能优化吗？

leaf_2008 2009-04-02 04:56:31

CREATE PROCEDURE [huanaigu].[Admin_SelectProductSellQuantities]
@BeginDate nvarchar(40),
@EndDate nvarchar(40),
@CategoryId nvarchar(40) =null,
@Brand nvarchar(40) =null,
@Merchant nvarchar(40) =null,
@Num nvarchar(20)='20'

AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Create a temp table to store the select results
CREATE TABLE #sellquantity
(
[ProductId] int,
[Quantity] int
)

-- Insert into the temp table
declare @SQL1 as nvarchar(3500)
declare @SQL2 as nvarchar(3500)

declare @SQL30 as nvarchar(3500)

SET @SQL1 = 'INSERT INTO #sellquantity(ProductId,Quantity)'
SET @SQL1 = @SQL1 + ' select o.productid,sum(o.quantity) as quantity from orderproduct o '
SET @SQL1 = @SQL1 + ' where o.orderid in(select orderid from orders where orderdate>='+@BeginDate+' and orderdate<='+@EndDate +' and OperateStatusId=6) '

SET @SQL2 = 'INSERT INTO #sellquantity(ProductId,Quantity) select productid,0 from productcount where productid not in(select productid from #sellquantity)'

/*
IF LEN(@CategoryId)>0
BEGIN
SET @SQL1 = @SQL1 + ' and o.productid in(select productid from product '
SET @SQL1 = @SQL1 + ' where categoryid in (select categoryId from category where cat_categoryid='+@CategoryId+'))'
END
*/
IF LEN(@CategoryId)>0 and (select cat_categoryid from category where categoryid=@CategoryId) is null
BEGIN
SET @SQL1 = @SQL1 + ' and o.productid in(select productid from product '
SET @SQL1 = @SQL1 + ' where categoryid in (select categoryId from category where cat_categoryid='+@CategoryId+'))'
SET @SQL2 = @SQL2 + ' and categoryid in (select categoryId from category where cat_categoryid='+@CategoryId+')'
END
IF LEN(@CategoryId)>0 and (select cat_categoryid from category where categoryid=@CategoryId) is not null
BEGIN
SET @SQL1 = @SQL1 + ' and o.productid in(select productid from product '
SET @SQL1 = @SQL1 + ' where categoryid ='+@CategoryId+')'
SET @SQL2 = @SQL2 + ' and categoryid ='+@CategoryId
END
IF (@Brand)!=null
BEGIN
SET @SQL1 = @SQL1 + ' and o.productid in (select productid from product where brandid in (select brandid from brand where name='+@Brand+'))'
SET @SQL2 = @SQL2 + ' and productid in (select productid from product where brandid in (select brandid from brand where name='+@Brand+'))'
END
IF (@Merchant)!=null
BEGIN
SET @SQL1 = @SQL1 + ' and o.productid in (select productid from merchant where merchant1='+@Merchant+')'
SET @SQL2 = @SQL2 + ' and productid in (select productid from merchant where merchant1='+@Merchant+')'

END
SET @SQL1 = @SQL1 + ' group by o.productid order by sum(o.quantity) desc'
SET @SQL2 = @SQL2 + ' group by productid '
exec sp_executesql @SQL1
exec sp_executesql @SQL2
--set @SQL30='
CREATE TABLE #ShipResult
(
[ProductId] int,
[Quantity] int
)
set @SQL30= 'INSERT INTO #ShipResult(ProductId,Quantity) '
set @SQL30= @SQL30+' select productid,sum(Quantity) as Quantity from orderproduct where orderid in(select orderid from orders where OperateStatusId=4 ) group by productid'
exec sp_executesql @SQL30
update #sellquantity set #sellquantity.Quantity= #sellquantity.Quantity+#ShipResult.Quantity from #ShipResult where #sellquantity.productid=#ShipResult.productid
--set @SQL30='update #sellquantity s ,( select productid,sum(quantity) as quantity from orderproduct where orderid in(select orderid from orders where OperateStatusId=4 ) group by productid ) p set s.quantity=p.quantity where s.productid=p.prouctid'

--exec sp_executesql @SQL30

CREATE TABLE #stockResult
(
[ProductId] int,
[Remnant] int
)

declare @SQL4 as nvarchar(3500)
SET @SQL4= 'INSERT INTO #stockresult(ProductId,remnant)'
SET @SQL4 = @SQL4+ ' select s.productid ,sum(s.remnant) as remnant from stockproduct s '
SET @SQL4 = @SQL4 + ' where s.productid in(select productid from #sellquantity)'
SET @SQL4 = @SQL4 + ' group by s.productid'
exec sp_executesql @SQL4

declare @SQL3 as nvarchar(3500)
declare @week as int
--set @week=round(DATEDIFF(day, cast(@BeginDate as datetime ),cast(@EndDate as datetime))/7,0)

SET @SQL3 ='select top '+@Num+' m.Price1,m.Price2,m.Price3,m.Merchant1,m.Merchant2,m.Merchant3,q.productid as productid,q.quantity,case when #stockresult.remnant is null then 0 else #stockresult.remnant end as remnant from #sellquantity q
left join #stockresult on q.productid=#stockresult.productid
left join Merchant m on q.productid=m.productid
where q.productid in (select productid from productcount group by productid)
order by q.quantity desc'
exec sp_executesql @SQL3

drop table #stockresult
drop table #ShipResult
drop table #sellquantity
GO

请问一下大家这个存储过程可以优化吗，现在查询数据库的时候页面都要停20秒以上啊

...全文