山寨机中的战斗机！	程序优化工程师到底对IT界有没有贡献

我写的分页过程，哎。。。一个字，慢，代码长的乱！！！高手牺牲时间指点指点不。。。在网页里执行老超时！

楼主ghaihua（晨昕）2005-11-03 11:27:29 在 MS-SQL Server / 疑难问题提问

－－我把代码帖出来，如果真能解决速度问题或者有更好的解决方法，我愿加分！谢谢了！急啊！！

--查询配对信息

CREATE PROCEDURE sp_tbbinfo_with_Tb_Information_mates_Search_test
(
@Status int OUTPUT, --状态
@ErrMsg varchar(255) OUTPUT, --错误信息
@pageRows int, --每页条数
@rowscount int, --记录总数
@keyWord varchar(255) = NULL, --关键字
@Sales_buy_type int = 3, --主营方向类别
@sales_Key varchar(255) = NULL, 　　　　--配对的销售关键字
@buy_Key varchar(255) = NULL, --配对的求购关键字
@currPage int, --当前页
@forwardPage int = 1, --上一个页
@issuedate int = 30 --商机发布/更新的时间

)AS
SET NOCOUNT ON
Declare @SQL varchar(3000) 　　　　--存储语句
--Declare @order_by varchar(255) 　　　　--排序
Declare @sales_buy_search varchar(1000) --配对关键字
Declare @sales_SQL varchar(500) --销售配对查询语句
Declare @buy_SQL varchar(500) --采购配对查询语句
Declare @keyword_SQL varchar(500) 　　　　--关键字查询语句
Declare @date_SQL varchar(255) --商机发布/更新查询语句
Declare @order_by varchar(100)
--Declare @order_by_temp varchar(100)
Declare @SomeID int
Create table #temp (ciBid int,cvcBName varchar(255),cvcBdetails varchar(2000),ciBSalestype int,ciUserid int,cdtRecordTime datetime,cvcImagepath varchar(255))
Create table #temp_Rlt (ciBid int,cvcBName varchar(255),cvcBdetails varchar(2000),ciBSalestype int,ciUserid int,cdtRecordTime datetime,cvcImagepath varchar(255))

SET @Status=0
SET @ErrMsg=''
Set @sales_buy_search=''
Set @sales_SQL=''
Set @buy_SQL=''
Set @keyword_SQL=''
-- SET @SQL = 'Select top '+convert(varchar,@pageRows)+' TB.ciBId,TB.cvcBName,TB.cvcBDetails,TB.ciBSalestype,TB.ciUserId,TB.cdtRecordTime,TB.cvcImagepath from tbbinfo TB where ciStatus>=0'
SET @SQL = 'Select top '+convert(varchar,@rowscount)+' TB.ciBId,TB.cvcBName,TB.cvcBDetails,TB.ciBSalestype,TB.ciUserId,TB.cdtRecordTime,TB.cvcImagepath from tbbinfo TB where ciStatus>=0'
--Set @order_by = ' order by TB.cdtRecordTime DESC,TB.ciBid DESC,TB.ciStatus DESC,TB.ciLevel DESC'
Set @order_by = ' order by cdtRecordTime DESC,ciBid DESC,ciStatus DESC,ciLevel DESC'
--Set @order_by_temp = ' order by cdtRecordTime DESC,ciBid DESC,ciStatus DESC,ciLevel DESC'
Set @sales_buy_search=''
if @sales_Key is null Set @sales_Key=''
if @buy_Key is null Set @buy_Key=''
if @keyWord is null Set @keyWord=''
if @sales_Key<>'' Set @sales_SQL=' ('+dbo.split_info_mates_cvcBname(@sales_Key,',')+') or ('+dbo.split_info_mates_cvcBDetails(@sales_Key,',')+')'
if @buy_Key<>'' Set @buy_SQL=' ('+dbo.split_info_mates_cvcBname(@buy_Key,',')+') or ('+dbo.split_info_mates_cvcBDetails(@buy_Key,',')+')'

Set @date_SQL=
Case @issuedate
when 1 then
case
when DatePart(Hour,GetDate())<=11 then
' and datediff(hour,getdate(),cdtrecordtime)<=24'
else
' and datediff(hour,getdate(),cdtrecordtime)=0'
end
when 3 then ' and datediff(day,getdate(),cdtrecordtime)<=2'
when 7 then ' and datediff(week,getdate(),cdtrecordtime)=0'
when 15 then ' and datediff(week,getdate(),cdtrecordtime)<=1'
when 30 then ' and datediff(month,getdate(),cdtrecordtime)=0'
when 45 then ' and datediff(week,getdate(),cdtrecordtime)<=5'
when 60 then ' and datediff(month,getdate(),cdtrecordtime)<=1'
when 90 then ' and datediff(month,getdate(),cdtrecordtime)<=2'
else ' and datediff(month,getdate(),cdtrecordtime)=0'
END

--关键字
if @keyWord<>''
　　　　Set @keyword_SQL = @keyword_SQL+' TB.cvcBkeyword like ''%'+@keyWord+'%'''
--else
--Set @sales_buy_search = @sales_buy_search+' and ('
if @keyword_SQL is null Set @keyword_SQL=''
if @buy_SQL is null Set @buy_SQL=''

Set @sales_buy_search =
Case
when @Sales_buy_type=3 then
case when (@sales_SQL<>'' and @sales_SQL is not null) then ' (('+@sales_SQL+') and TB.ciBsalesType=1)' end+case when (@buy_SQL<>'' and @buy_SQL is not null) then ' or (('+@buy_SQL+') and TB.ciBsalesType=2)' end
--' and (('+dbo.f_split(@sales_Key,',','cvcBname')+') or ('+dbo.f_split(@sales_Key,',','cvcBDetails')+') or ('+dbo.f_split(@buy_Key,',','cvcBname')+') or ('+dbo.f_split(@buy_Key,',','cvcBDetails')+'))'
when @Sales_buy_type=2 then
case when (@buy_SQL<>'' and @buy_SQL is not null) then @buy_SQL end
--' and (('+dbo.f_split(@buy_Key,',','cvcBname')+') or ('+dbo.f_split(@buy_Key,',','cvcBDetails')+')) and TB.ciBsalesType=2'
when @Sales_buy_type=1 then
case when (@sales_SQL<>'' and @sales_SQL is not null) then @sales_SQL end
--' and (('+dbo.f_split(@sales_Key,',','cvcBname')+') or ('+dbo.f_split(@sales_Key,',','cvcBDetails')+')) and TB.ciBsalesType=1'
END

if (len(@sales_buy_search)=3) or (@sales_buy_search=' or ') or (@sales_buy_search is null)
Set @sales_buy_search = ''

if @keyword_SQL<>'' and @keyword_SQL is not null Begin
if @sales_buy_search<>'' and @sales_buy_search is not null
Set @SQL = @SQL+' and ('+@keyword_SQL+' or '+@sales_buy_search+')'
else
Set @SQL = @SQL+' and ('+@keyword_SQL+')'
END
ELSE
if @sales_buy_search<>'' and @sales_buy_search is not null
Set @SQL = @SQL+' and ('+@sales_buy_search+')'
if @Sales_buy_type=1
Set @SQL = @SQL+' and TB.ciBsalesType=1'
if @Sales_buy_type=2
Set @SQL = @SQL+' and TB.ciBsalesType=2'

Set @SQL = @SQL+@date_SQL+@order_by

insert into #Temp execute(@SQL)

if @currPage=1
--insert into #temp_Rlt
Set @SQL = 'select top '+cast(@pageRows as varchar)+' ciBid,cvcBName,cvcBdetails,ciBSalestype,ciUserid,cdtRecordTime,cvcImagepath from #temp '
ELSE Begin
--insert into #temp_Rlt
Set @SomeID=(@currPage-1)*@pageRows
Set @SQL = 'select top '+cast(@pageRows as varchar)+' ciBid,cvcBName,cvcBdetails,ciBSalestype,ciUserid,cdtRecordTime,cvcImagepath from #temp where cibid not in (select top '+cast(@SomeID as varchar)+' cibid from #temp) '
END

insert into #temp_Rlt execute(@SQL)
select A.ciBid,A.cvcBName,A.cvcBdetails,A.ciBSalestype,A.ciUserid,A.cdtRecordTime,A.cvcImagepath,B.cvcENDomain as Url from #temp_Rlt A Left Outer Join tbUserChRelation B on A.Ciuserid=B.CiUserID
--select A.ciBid,A.cvcBName,A.cvcBdetails,A.ciBSalestype,A.ciUserid,A.cdtRecordTime,A.cvcImagepath,B.cvcENDomain as Url from #temp_Rlt as A Left join tbUserChRelation as B on A.Ciuserid=B.CiUserID
Drop table #temp
Drop table #temp_Rlt

IF @@Error<>0 BEGIN
SET @Status=-1
SET @ErrMsg='数据库异常错误，请稍后再试'
RETURN
END
GO 问题点数：100、回复次数：11Top

1 楼whchen（土龙）回复于 2005-11-03 11:36:41 得分 60

给你一个现成的供参考
CREATE procedure main_table_pwqzc
(@pagesize int,
@pageindex int,
@docount bit,
@this_id)
as
if(@docount=1)
begin
select count(id) from luntan where this_id=@this_id
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
insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc
select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
GO

存储过程会根据传入的参数@docount来确定是不是要返回所有要分页的记录总数
特别是这两行
set rowcount @PageUpperBound
insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc

真的是妙不可言!!set rowcount @PageUpperBound当记录数达到@PageUpperBound时就会停止处理查询
,select id 只把id列取出放到临时表里,select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
而这句也只从表中取出所需要的记录,而不是所有的记录,结合起来,极大的提高了效率!!
妙啊,真的妙!!!!Top

2 楼churchatp1（别看资料，看聊效！）回复于 2005-11-03 11:58:09 得分 10

真晕
确实够长Top

3 楼ghaihua（晨昕）回复于 2005-11-03 15:45:06 得分 0

DingTop

4 楼ghaihua（晨昕）回复于 2005-11-04 09:17:32 得分 0

哎，，，真是没有愿意看。。。
不看也帮忙顶下罗，谢谢啦，我连顶三次就不让我顶了啊Top

5 楼beijixing9（sql server,bi 工程师(www.windbi.com)）回复于 2005-11-06 16:12:33 得分 10

我帮你顶一下吧

Top

6 楼beijixing9（sql server,bi 工程师(www.windbi.com)）回复于 2005-11-06 16:12:46 得分 10

再帮你顶一次

Top

7 楼beijixing9（sql server,bi 工程师(www.windbi.com)）回复于 2005-11-06 16:12:58 得分 10

帮你顶够三次!

Top

8 楼ghaihua（晨昕）回复于 2005-11-08 08:11:47 得分 0

yun,呵呵...谢谢...Top

9 楼beijixing9（sql server,bi 工程师(www.windbi.com)）回复于 2006-01-15 12:32:10 得分 0

有钱出钱
有力出力
没钱没力的就只有帮你顶了

Top

10 楼beijixing9（sql server,bi 工程师(www.windbi.com)）回复于 2006-01-15 12:32:30 得分 0

有钱出钱
有力出力
没钱没力的就只有帮你顶了

顶第二次

Top

11 楼beijixing9（sql server,bi 工程师(www.windbi.com)）回复于 2006-01-15 12:32:47 得分 0

有钱出钱
有力出力
没钱没力的就只有帮你顶了

顶第三次

Top