求优化存储过程
CREATE procedure ProductSearch
(
@pagesize int,
@pageindex int,
@Keywords varchar(50),
@Address varchar(50),
@docount bit
)
as
BEGIN TRAN
DECLARE @COUNT INT
DECLARE @SID INT
set nocount on
if(@docount=1)
if(@Address <> ' ')
begin
IF((select count(ProductID) from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID inner join FREETEXTTABLE(Products,Title,@Keywords) as Product2
on Product1.ProductID=Product2.[KEY] where Company.Address LIKE '% '+@Address+ '% ') <21)
SET @COUNT=(select count(ProductID) from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID inner join FREETEXTTABLE(Products,Title,@Keywords) as Product2
on Product1.ProductID=Product2.[KEY] where Company.Address LIKE '% '+@Address+ '% ')
ELSE
SET @COUNT=20
select count(ProductID)+@COUNT from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID inner join containstable(Products,Title,@Keywords) as Product2
on Product1.ProductID=Product2.[KEY]
where Company.Address LIKE '% '+@Address+ '% '
end
else
begin
IF((select count(ProductID) from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID inner join FREETEXTTABLE(Products,Title,@Keywords) as Product2
on Product1.ProductID=Product2.[KEY]) <21)
SET @COUNT=(select count(ProductID) from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID inner join FREETEXTTABLE(Products,Title,@Keywords) as Product2
on Product1.ProductID=Product2.[KEY])
ELSE
SET @COUNT=20
select count(ProductID)+@COUNT from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID inner join containstable(Products,Title,@Keywords) as Product2
on Product1.ProductID=Product2.[KEY]
end
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
if(@Address <> ' ')
begin
BEGIN
insert into @indextable(nid) select top 20 ProductID from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID
inner join containstable(Products,Title,@Keywords) as Product2 on Product1.ProductID=Product2.[KEY] inner join Businesses on Product1.BusinesseID=Businesses.BusinesseID
where Businesses.Grade= '2 ' and Company.Address LIKE '% '+@Address+ '% '
order by Product1.Addtime desc,Product2.[RANK] desc,Businesses.Credit desc
END
insert into @indextable(nid) select ProductID from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID
inner join containstable(Products,Title,@Keywords) as Product2 on Product1.ProductID=Product2.[KEY]
where NOT EXISTS(SELECT nid from @indextable where nid=ProductID) and Company.Address LIKE '% '+@Address+ '% '
order by Product1.Addtime desc,Product2.[RANK] desc
insert into @indextable(nid) select top 20 ProductID from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID
inner join freetexttable(Products,Title,@Keywords) as Product2 on Product1.ProductID=Product2.[KEY]
where NOT EXISTS(SELECT nid from @indextable where nid=ProductID) and Company.Address LIKE '% '+@Address+ '% '
order by Product2.[RANK] desc,Product1.Addtime desc
end
else
BEGIN
BEGIN
insert into @indextable(nid) select top 1 ProductID from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID
inner join containstable(Products,Title,@Keywords) as Product2 on Product1.ProductID=Product2.[KEY] inner join Businesses on Product1.BusinesseID=Businesses.BusinesseID
where Businesses.Grade= '2 '
order by Product1.Addtime desc
insert into @indextable(nid) select top 16 ProductID from Products inner join Company on Products.BusinesseID=Company.BusinesseID inner join Businesses on Businesses.BusinesseID=Products.BusinesseID
where Products.Title=@Keywords and Grade= '2 ' and ProductID in
(select max(ProductID) from Products inner join Company on Products.BusinesseID=Company.BusinesseID inner join Businesses on Products.BusinesseID=Businesses.BusinesseID
where Grade= '2 ' and Products.Title=@Keywords group by CompanyName) and NOT EXISTS(SELECT nid from @indextable where nid=ProductID)
order by Products.AddTime desc
END
BEGIN
insert into @indextable(nid) select top 20 ProductID from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID
inner join containstable(Products,Title,@Keywords) as Product2 on Product1.ProductID=Product2.[KEY] inner join Businesses on Product1.BusinesseID=Businesses.BusinesseID
where Businesses.Grade= '2 ' and NOT EXISTS(SELECT nid from @indextable where nid=ProductID)
and Product1.ProductID in
(select max(Product3.ProductID) from Products as Product3 inner join Company on Product3.BusinesseID=Company.BusinesseID
inner join containstable(Products,Title,@Keywords) as Product4 on Product3.ProductID=Product4.[KEY] inner join Businesses on Product3.BusinesseID=Businesses.BusinesseID
where Grade= '2 ' group by CompanyName)
order by Product1.Addtime desc,Product2.[RANK] desc,Businesses.Credit desc
END
insert into @indextable(nid) select ProductID from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID
inner join containstable(Products,Title,@Keywords) as Product2 on Product1.ProductID=Product2.[KEY] where NOT EXISTS(SELECT nid from @indextable where nid=ProductID)
order by Product1.Addtime desc,Product2.[RANK] desc
insert into @indextable(nid) select top 20 ProductID from Products as Product1 inner join Company on Product1.BusinesseID=Company.BusinesseID inner join Businesses on Product1.BusinesseID=Businesses.BusinesseID
inner join freetexttable(Products,Title,@Keywords) as Product2 on Product1.ProductID=Product2.[KEY] where NOT EXISTS(SELECT nid from @indextable where nid=ProductID)
order by Product2.[RANK] desc,Credit desc,Product1.Addtime desc
END
SELECT (SELECT ' <img src=images/cxlm.gif> <br> '+CAST(Credit AS varchar(50)) From Businesses WHERE Businesses.BusinesseID = O.BusinesseID) AS Credit, ' <B>
<Font Class=title> <A Href=Products/ '+CAST(O.ProductID AS varchar)+ '.html target=_blank> '+O.Title+ ' </A> </Font> </B> ' AS Title,O. Define,
' <BR> <a href= '+(SELECT ShopUrl FROM Businesses WHERE Businesses.BusinesseID=Company.BusinesseID)+ ' target=_blank> '+Company.CompanyName+ ' <font color=red> 拜访该会员商铺 </font> '+ ' </a> ' AS Shop,
' <A Href=Products/ '+CAST(O.ProductID AS varchar)+ '.html target=_blank> <Img width=65 height=65 border=0 src=Productimage/ '+ISNULL(O.ImgPath, 'Default.gif ')+ '> </A> ' AS Img,
LEFT(Company.Address,CHARINDEX( '市 ',Company.Address)) AS Adds,
O.Amount AS Amount
from Products O,@indextable t ,Company
where O.ProductID=t.nid
and t.id> @PageLowerBound and t.id <=@PageUpperBound and O.BusinesseID=Company.BusinesseID order by t.id
end
set nocount off
IF(@@ERROR!=0)
ROLLBACK TRAN
ELSE
COMMIT TRAN
GO
[解决办法]
关注
[解决办法]
nnd,看完了，顶。。。。
------解决方案--------------------
真他妈的长阿！！
[解决办法]
既然是求优化，可惜这么长还不写注释谁知道你的逻辑，习惯不好，实在没兴趣看
[解决办法]
将重复的代码写成自定义函数或者存储过程。
