可用分押宝游戏火热进行中...	专题改版：Java Web 专题

分页存储过程的问题

楼主todaywlq（今天）2006-10-18 09:24:37 在 .NET技术 / ASP.NET 提问

我用下面的存储过程做分页，数据库有160万条记录，我的查询条件里有CONTAINS(mingc,'儿童'),在查询分析器里需要3分多才能返回结果，web前台一查询就超时，各位有没有什么改进的办法，分不够可以再加。

CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where (' + @strWhere + ')'
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end

if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where (' + @strWhere + ') ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + '])
　　　　　　 from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder

if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where (' + @strWhere + ') '
+ @strOrder + ') as tblTmp) and (' + @strWhere + ') ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] '
+ @strOrder + ') as tblTmp) ' + @strOrder
end
end

print @strSQL

exec (@strSQL)
GO
问题点数：100、回复次数：37Top

1 楼junmail（浪子）回复于 2006-10-18 09:59:01 得分 1

帮顶！Top

2 楼myminimouse（坚决不用baidu）回复于 2006-10-18 14:50:47 得分 2

可以看看
http://www.cnblogs.com/ilovejolly/archive/2006/10/10/523992.htmlTop

3 楼aafshzj（生活需要breakthrough）回复于 2006-10-18 14:51:06 得分 1

mingc字段（或者与其它经常共用作查询语句的条件一道）做索引。

Top

4 楼saman11（）回复于 2006-10-18 14:51:16 得分 0

不明白楼主在问什么。Top

5 楼sunjay117（悟空）回复于 2006-10-18 15:00:00 得分 1

1.mingc做检索,
2.提取字段不要太多
3.cmd连接时间设置以下Top

6 楼jedliu（21世纪什么最贵? 人才!）回复于 2006-10-18 15:13:18 得分 1

已经很不错了，用了Count(*)、top、order by，没用如in、union、not这些东西，很不错了！
Top

7 楼jedliu（21世纪什么最贵? 人才!）回复于 2006-10-18 15:13:42 得分 1

拿你东西去试下，看看是什么问题！Top

8 楼xingyaohua（邢跃华）回复于 2006-10-18 15:19:05 得分 1

upTop

9 楼todaywlq（今天）回复于 2006-10-18 15:34:21 得分 0

我是做的全文索引，有没有办法让查询快点Top

10 楼jedliu（21世纪什么最贵? 人才!）回复于 2006-10-18 16:02:28 得分 1

没问题啊，挺好用啊！
全文索引，你不会是把所有的东西一次查询出吧？Top

11 楼todaywlq（今天）回复于 2006-10-18 16:16:28 得分 0

是不是检索条件的问题？
@strWhere = 'nian >=''2000'' and nian<=''2006'' AND (left(bianh,3)= ''001'' OR left(bianh,3) = ''002'' OR left(bianh,3) = ''003'' OR left(bianh,3) = ''004'' OR left(bianh,3) = ''005'' OR left(bianh,3) = ''006'' OR left(bianh,3) = ''007'' OR left(bianh,3) = ''008'' OR left(bianh,3) = ''009'' OR left(bianh,3) = ''010'' OR left(bianh,3) = ''011'' OR left(bianh,3) = ''012'') AND CONTAINS(mingc,''儿童'')'Top

12 楼todaywlq（今天）回复于 2006-10-18 16:19:08 得分 0

在查询分析器里执行的语句，帮我看看
DECLARE @RC int
DECLARE @tblName varchar(255)
DECLARE @strGetFields varchar(1000)
DECLARE @fldName varchar(255)
DECLARE @PageSize int
DECLARE @PageIndex int
DECLARE @doCount bit
DECLARE @OrderType bit
DECLARE @strWhere varchar(1500)
SELECT @tblName = 'qikan'
SELECT @strGetFields = 'id,sjid,bianh,mingc,zuoz,jig,kanm,nian,qi'
SELECT @fldName = 'ID'
SELECT @PageSize = 20
SELECT @PageIndex = 1
SELECT @doCount = 0
SELECT @OrderType = 0
SELECT @strWhere = 'nian >=''2000'' and nian<=''2006'' and (left(bianh,3)= ''001'' OR left(bianh,3) = ''002'' OR left(bianh,3) = ''003'' OR left(bianh,3) = ''004'' OR left(bianh,3) = ''005'' OR left(bianh,3) = ''006'' OR left(bianh,3) = ''007'' OR left(bianh,3) = ''008'' OR left(bianh,3) = ''009'' OR left(bianh,3) = ''010'' OR left(bianh,3) = ''011'' OR left(bianh,3) = ''012'') AND CONTAINS(mingc,''儿童'')'
EXEC @RC = [qikan].[dbo].[pagination] @tblName, @strGetFields, @fldName, @PageSize, @PageIndex, @doCount, @OrderType, @strWhere
DECLARE @PrnLine nvarchar(4000)
PRINT '存储过程: qikan.dbo.pagination'
SELECT @PrnLine = ' 返回代码 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLineTop

13 楼jedliu（21世纪什么最贵? 人才!）回复于 2006-10-18 16:34:03 得分 5

加上or会降低速度的！
要不你试下那个什么case when then end！Top

14 楼sunbird69（太阳鸟）回复于 2006-10-18 16:42:34 得分 1

upTop

15 楼jedliu（21世纪什么最贵? 人才!）回复于 2006-10-18 16:46:10 得分 0

exec pagination 'Item',DEFAULT,'It_Itemid',DEFAULT,195625,DEFAULT,DEFAULT,DEFAULT
我用我机器上的一个数据表实验了下，有2百万数据，每页10行，查第195625页花费了12秒！查199999页花费13秒！查完后，10万以下的页面基本是1秒内完成！
Top

16 楼sp1234（asp.net不是一个语言，是一个操作系统）回复于 2006-10-18 16:54:41 得分 20

太长了，没仔细看。

不过如果使用sql server2005，对于可变的top参数完全没有必要那样写，另外要把所有嵌套在where中的子查询都（基本上都应该）改为inner join或者left join的准确的关系操作，多于地执行"count()"统计也是常犯的错误。Top

17 楼sp1234（asp.net不是一个语言，是一个操作系统）回复于 2006-10-18 16:58:22 得分 0

“(left(bianh,3)= ''001'' ”本应该写为“bianh like ''001%''”并且为bianh建立索引，这是不需要分析的。Top

18 楼todaywlq（今天）回复于 2006-10-18 17:23:17 得分 0

bianh上已经建索引了Top

19 楼hertcloud（·￡孙子兵法￡·）回复于 2006-10-19 08:32:39 得分 10

http://www.cnblogs.com/hertcloud/Top

20 楼Edisoncat（http://www.Edisonliu.com）回复于 2006-10-19 08:45:43 得分 1

帮顶,接分Top

21 楼szc21（卖炭翁）回复于 2006-10-19 09:05:05 得分 1

太长了，顶Top

22 楼chenjunjarysky（非洲小白脸）回复于 2006-10-19 09:22:56 得分 10

建议用case when then end结构。Top

23 楼netlg（雕龙）回复于 2006-10-19 09:23:35 得分 1

学习Top

24 楼xiaoyuehen（OH, NO~It's Unfair!）回复于 2006-10-19 09:37:31 得分 20

SELECT @strWhere = 'nian >=''2000'' and nian<=''2006'' and (bianh like ''001%'' OR bianh like ''002%'' OR bianh like ''003%'' OR bianh like ''004%'' OR bianh like ''005%'' OR bianh like ''006%'' OR bianh like ''007%'' OR leftbianh like ''008%'' OR bianh like ''009%'' OR bianh like ''010%'' OR bianh like ''011%'' OR bianh like ''012%'') AND CONTAINS(mingc,''儿童'')'
Top

25 楼todaywlq（今天）回复于 2006-10-19 10:46:31 得分 0

我改为@strWhere = 'CONTAINS(mingc,''儿童'')'也一样慢，第一页用了12秒，第二页就要用5分09秒，怎么差这么多？
Top

26 楼jedliu（21世纪什么最贵? 人才!）回复于 2006-10-19 12:44:32 得分 20

or 会引起全表扫描，也许就是这个弄慢的。其他的我都试过，即使200万的数据，查最后几页也只要13秒左右！Top

27 楼lih163（做自己喜欢的事）回复于 2006-10-19 12:54:32 得分 1

顶Top

28 楼jedliu（21世纪什么最贵? 人才!）回复于 2006-10-19 13:04:07 得分 0

奇怪，我即使按你的那样做，也是很快！
exec pagination 'Item',default,'It_Itemid',200,default,default,default,
'It_Itemid=''EST-1000'' or It_Itemid=''EST-1020'' or It_Itemid=''EST-1020000''
or It_Productid like ''RP-LI-4%'' or It_Productid like ''RP-LI-3%''
or It_Productid like ''RP-LI-2%'' or It_Productid like ''RP-LI-5%''
or It_Productid like ''RP-LI-1%'''

每页的大小都加到了200！

你为什么不把bianh截掉呢，用substring(bianh,0,3)截出前两位，如果是“00”，则截出第三位substring(bianh,3,1)，判断当大于1小于11时，可以进行查询！

不知道这样会不会快点！
Top

29 楼belldandy11（路漫漫其修远兮,吾将上CSDN求索）回复于 2006-10-19 13:04:15 得分 1

学习Top

30 楼todaywlq（今天）回复于 2006-10-19 13:11:30 得分 0

我觉得我的问题出在全文索引上，请问使全文索引有要什么注意的吗？Top

31 楼todaywlq（今天）回复于 2006-10-19 14:00:19 得分 0

怎么搞的
我用mingc like '%儿童%' 也比 CONTAINS(mingc,'儿童')Top

32 楼todaywlq（今天）回复于 2006-10-19 14:04:57 得分 0

怎么搞的
我用mingc like '%儿童%' 也比 CONTAINS(mingc,'儿童')要快Top

33 楼jinher（）回复于 2006-10-19 14:23:09 得分 0

ding
www.jh0101.comTop

34 楼todaywlq（今天）回复于 2006-10-19 15:25:42 得分 0

自已顶Top

35 楼todaywlq（今天）回复于 2006-10-19 17:08:00 得分 0

和SQL Server 补丁有没有关系，我没有装补丁？Top

36 楼todaywlq（今天）回复于 2006-10-20 08:20:00 得分 0

………………Top

37 楼Homers（）回复于 2006-10-20 12:52:59 得分 0

'select count(*) as Total from [' + @tblName + '] where (' + @strWhere + ')'
像这样的语句,如果数据多的话,会影响速度的

将需要查询的字段索引Top