<存储过程分页>
现在已经很清晰的表现出来就是那么几种的思路,我的前一个贴子为了解决ADO传递参数的问题
http://expert.csdn.net/Expert/topic/1978/1978897.xml?temp=.5344507
引出了不少的话题
最显眼的就是关于存储过程分页及其性能问题特别是由
free_eyes(自由之眼)
lihonggen0(李洪根,用.NET,标准答案来了)
progame()等高手献计献策我觉得现在应该把这个问题澄清一下,讨论出一个比较完善的解决方案,也省了以后其他同行们继续在这个问题上再浪费时间,还希望高手们一如既往的支持。这里提供一个表和存储过程的interface来统一一下,并且为了通用,表中有另外的字段可能用来作为排序的依据。
大家就按他的结构进行回复CREATE TABLE [dbo].[Content_Table] (
[Id] [int] IDENTITY (1, 1) NOT NULL , --自增Id,默认排序规则从大到小
[Catalog_Id] [int] NOT NULL , --所属类别,查询时作为条件使用
[Content] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
--要得到的内容
[Order_By1] [int] NOT NULL , --可能用到的排序依据1
[Order_By2] [int] NOT NULL --可能用到的排序依据2
) ON [PRIMARY]CREATE PROCEDURE get_Page_from_Content_Table
(
@Catalog_Id [int]=1, --所属类别Id,作为查询的条件 Where [Catalog_Id]=@Catalog_Id
@PageNow [int]=1, --当前页码
@PageSize [int]=20, --每页显示的条数,默认20
@Order_By [int]=0 --使用的排序规则
-- 0:默认排序 Order by Id Desc
-- 1:用Order_By1最为排序规则 Order by Order_By1
-- 2:用Order_By2最为排序规则 Order by Order_By2
)
AS
set nocount on --关闭计数IF @Order_By=1
BEGIN
......
END
ELSE IF @Order_By=2
BEGIN
......
END
ELSE
BEGIN
......
END我们回复的时候就不要象以上那么麻烦了
直接:
@Order_By=1:
{此处写上你的思路 BEGIN......END 中间的部分}
回复可以只写一个情况
比如我现在把其中之一——没有用到Order_By1或Order_By1的一种思路写上:
@Order_By=0:
BEGIN
set nocount on --关闭计数
declare @AllCount int --临时变量定义
declare @BeginId int,@EndId int
declare @Begin_Id int, @End_Id int
SELECT @allid = COUNT(*)
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) --得出记录总数 declare cro_fastread cursor scroll
for select [Id] from [Content_Table] where [Catalog_Id]=@Catalog_Id order by [Id] desc
--定义游标操作,但是不用临时纪录集
--而且游标也不需要全部遍历所有纪录集。
open cro_fastread --打开游标
select @BeginId=(@PageNow-1)*@PageSize+1--得出该页起始记录的位置
select @EndId = @BeginId+@PageSize-1 --得出该页末尾的记录位置 fetch absolute @BeginId from cro_fastread into @Begin_Id
--将他的Id传给一个变量,该页开始的Id
if @EndId>@AllCount --这里要注意,如果某一页不足固定页数的纪录时,
--如只有一页纪录,而且纪录少于我们定义的数目,或者是最后一页时。
fetch last from cro_fastread into @End_Id
--直接将游标绝对定位到最后一条纪录,得出他的Id号来。
else
fetch absolute @EndId from cro_fastread into @End_Id SELECT *
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) AND (Id BETWEEN @End_Id AND @Begin_Id)
ORDER BY [Id] DESC CLOSE cro_fastread
DEALLOCATE cro_fastread
set nocount off --打开计数
END
现在已经很清晰的表现出来就是那么几种的思路,我的前一个贴子为了解决ADO传递参数的问题
http://expert.csdn.net/Expert/topic/1978/1978897.xml?temp=.5344507
引出了不少的话题
最显眼的就是关于存储过程分页及其性能问题特别是由
free_eyes(自由之眼)
lihonggen0(李洪根,用.NET,标准答案来了)
progame()等高手献计献策我觉得现在应该把这个问题澄清一下,讨论出一个比较完善的解决方案,也省了以后其他同行们继续在这个问题上再浪费时间,还希望高手们一如既往的支持。这里提供一个表和存储过程的interface来统一一下,并且为了通用,表中有另外的字段可能用来作为排序的依据。
大家就按他的结构进行回复CREATE TABLE [dbo].[Content_Table] (
[Id] [int] IDENTITY (1, 1) NOT NULL , --自增Id,默认排序规则从大到小
[Catalog_Id] [int] NOT NULL , --所属类别,查询时作为条件使用
[Content] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
--要得到的内容
[Order_By1] [int] NOT NULL , --可能用到的排序依据1
[Order_By2] [int] NOT NULL --可能用到的排序依据2
) ON [PRIMARY]CREATE PROCEDURE get_Page_from_Content_Table
(
@Catalog_Id [int]=1, --所属类别Id,作为查询的条件 Where [Catalog_Id]=@Catalog_Id
@PageNow [int]=1, --当前页码
@PageSize [int]=20, --每页显示的条数,默认20
@Order_By [int]=0 --使用的排序规则
-- 0:默认排序 Order by Id Desc
-- 1:用Order_By1最为排序规则 Order by Order_By1
-- 2:用Order_By2最为排序规则 Order by Order_By2
)
AS
set nocount on --关闭计数IF @Order_By=1
BEGIN
......
END
ELSE IF @Order_By=2
BEGIN
......
END
ELSE
BEGIN
......
END我们回复的时候就不要象以上那么麻烦了
直接:
@Order_By=1:
{此处写上你的思路 BEGIN......END 中间的部分}
回复可以只写一个情况
比如我现在把其中之一——没有用到Order_By1或Order_By1的一种思路写上:
@Order_By=0:
BEGIN
set nocount on --关闭计数
declare @AllCount int --临时变量定义
declare @BeginId int,@EndId int
declare @Begin_Id int, @End_Id int
SELECT @allid = COUNT(*)
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) --得出记录总数 declare cro_fastread cursor scroll
for select [Id] from [Content_Table] where [Catalog_Id]=@Catalog_Id order by [Id] desc
--定义游标操作,但是不用临时纪录集
--而且游标也不需要全部遍历所有纪录集。
open cro_fastread --打开游标
select @BeginId=(@PageNow-1)*@PageSize+1--得出该页起始记录的位置
select @EndId = @BeginId+@PageSize-1 --得出该页末尾的记录位置 fetch absolute @BeginId from cro_fastread into @Begin_Id
--将他的Id传给一个变量,该页开始的Id
if @EndId>@AllCount --这里要注意,如果某一页不足固定页数的纪录时,
--如只有一页纪录,而且纪录少于我们定义的数目,或者是最后一页时。
fetch last from cro_fastread into @End_Id
--直接将游标绝对定位到最后一条纪录,得出他的Id号来。
else
fetch absolute @EndId from cro_fastread into @End_Id SELECT *
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) AND (Id BETWEEN @End_Id AND @Begin_Id)
ORDER BY [Id] DESC CLOSE cro_fastread
DEALLOCATE cro_fastread
set nocount off --打开计数
END
SELECT @allid = COUNT(*)
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) --得出记录总数
句中
allid 改为@AllCount
BEGIN
declare @AllCount int --临时变量定义
declare @BeginId int --开始的记录的位置
declare @Begin_Id int,@Begin_Order_By1 int,@Begin_Order_By2 int
--这三个变量用来记录改页第一条记录的信息
SELECT @AllCount = COUNT(*)
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id)--得出记录总数 select @BeginId=(@PageNow-1)*@PageSize+1--得出该页起始记录的位置 --if @AllCount<@BeginId
--return -1 --页码超标
declare cro_fastread cursor scroll
for select [Id] from [Content_Table] where [Catalog_Id]=@Catalog_Id order by [Id] desc
--定义游标操作,但是不用临时纪录集
--而且游标也不需要全部遍历所有纪录集。
open cro_fastread --打开游标 fetch absolute @BeginId from cro_fastread into @Begin_Id
--获得第一条记录的信息作为这一页的判据
CLOSE cro_fastread
DEALLOCATE cro_fastread set rowcount @PageSize --设置页面大小
SELECT *
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) AND ([Id]<=@Begin_Id)
ORDER BY [Id] DESC --注意此处是desc排序才用的[Id]<=@Begin_Id
set rowcount 0 --还原设置
set nocount off --打开计数
END
并且这个可以解决用另外的(除ID字段以外的)字段作为排序规则的问题
一会我把order_by=1和2的也写上。呵呵
怎么这个贴子大家都不是很热情啊?
是不是没有什么实际用处或者是我的表述不清啊?
declare @BeginId int --开始的记录的位置
declare @Begin_Id int,@Begin_Order_By1 int,@Begin_Order_By2 int
--这三个变量用来记录改页第一条记录的信息
SELECT @AllCount = COUNT(*)
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id)--得出记录总数 select @BeginId=(@PageNow-1)*@PageSize+1--得出该页起始记录的位置 --if @AllCount<@BeginId
--return -1 --页码超标 --以上的和order_By=0没有什么两样,主要是下面的游标定义和select语句中
--特别是desc 和大于、小于的关系要搞清楚
declare cro_fastread cursor scroll
for select [Id],[Order_By1] from [Content_Table] where [Catalog_Id]=@Catalog_Id order by Order_By1,[Id] desc
--
open cro_fastread --打开游标 fetch absolute @BeginId from cro_fastread into @Begin_Id,@Begin_Order_By1
--获得第一条记录的信息作为这一页的判据
CLOSE cro_fastread
DEALLOCATE cro_fastread set rowcount @PageSize --设置页面大小
SELECT *
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) AND ([Id]<=@Begin_Id) And (Order_By1>=@Begin_Order_By1)
ORDER BY Order_By1,[Id] DESC--注意此处是desc排序才用的[Id]<=@Begin_Id
--Order_By1是正常排序所以用Order_By1>=@Begin_Order_By1
set rowcount 0 --还原设置
set nocount off --打开计数order_by=2的情况和以上一样
不好意思我又不能删除
我现在把我写的完整的贴出来
大家来评价:
ALTER PROCEDURE get_Page_from_Content_Table
(
@Catalog_Id [int]=1, --所属类别Id,作为查询的条件
@PageNow [int]=1, --当前页码
@PageSize [int]=20, --每页显示的条数,默认20
@Order_By [int]=0 --使用的排序规则
--0:默认排序Order by Id Desc
--1:用Order_By1最为排序规则Order by Order_By1
--2:用Order_By2最为排序规则Order by Order_By2
)
AS
declare @AllCount int --临时变量定义
declare @BeginId int --开始的记录的位置
declare @Begin_Id int,@Begin_Order_By1 int,@Begin_Order_By2 intselect @BeginId=(@PageNow-1)*@PageSize+1--得出该页起始记录的位置if @Order_By=1
begin
SET rowcount @PageSize
declare cro_fastread scroll cursor
FOR
SELECT * FROM [Content_Table]
where [Catalog_Id]=@Catalog_Id order by Order_By1,[Id] desc
open cro_fastread
fetch absolute @BeginId from cro_fastread
WHILE @@fetch_status = 0
fetch next from cro_fastread
set rowcount 0
CLOSE cro_fastread
DEALLOCATE cro_fastread
end
else if @Order_By=2
begin
SET rowcount @PageSize
declare cro_fastread scroll cursor
FOR
SELECT * FROM [Content_Table]
where [Catalog_Id]=@Catalog_Id order by Order_By2,[Id] desc
open cro_fastread
fetch absolute @BeginId from cro_fastread
WHILE @@fetch_status = 0
fetch next from cro_fastread
set rowcount 0
CLOSE cro_fastread
DEALLOCATE cro_fastread
end
else
begin
declare cro_fastread cursor scroll
for select [Id] from [Content_Table] where [Catalog_Id]=@Catalog_Id order by [Id] desc
--定义游标操作,但是不用临时纪录集
--而且游标也不需要全部遍历所有纪录集。
open cro_fastread --打开游标 fetch absolute @BeginId from cro_fastread into @Begin_Id
--获得第一条记录的信息作为这一页的判据
CLOSE cro_fastread
DEALLOCATE cro_fastread set rowcount @PageSize --设置页面大小
SELECT *
FROM [Content_Table]
WHERE ([Catalog_Id] = @Catalog_Id) AND ([Id]<=@Begin_Id)
ORDER BY [Id] DESC --注意此处是desc排序才用的[Id]<=@Begin_Id
endset rowcount 0 --还原设置
set nocount off --打开计数
注意以上的两种方式是返回不同的的Recordset,在调用的时候也有所区别:<%
Dim Conn,Rs
Set Conn=server.CreateObject ("ADODB.Connection")
Set Rs=server.CreateObject ("ADODB.Recordset")
Conn.Open "DRIVER={SQL Server};Server=XXX;UID=XXX;PWD=XX;DATABASE=XXX"
begintime= timer()
for i=1 to 40
set rs=conn.Execute ("exec get_Page_from_Content_Table 1,1,40,0")
do while not rs.EOF
response.Write rs("content")&"<br>"&vbcr
rs.MoveNext
loop
next
rs.Close
'for i=1 to 40
'以下是调用order_by=1或2的情况
'set rs=conn.Execute ("exec get_Page_from_Content_Table 1,1,40,1")
'set rs=conn.Execute ("exec get_Page_from_Content_Table 1,1,40,2")
'do while not rs.EOF
' response.Write rs("content")&"<br>"&vbcr
' set rs=rs.NextRecordset
'loop
'next
'rs.Close
response.Write 1000*(timer()-begintime)&"<br>"
%>
上面的调用大家先仔细看看他们的区别,为什么会这样我后来说。
我做了测试
order_by=0的时候性能是order_by=1或2是性能的两倍。
你可以自己分析一下什么原因。
但是我没有测试大量数据的时候是什么情况。
declare cro_fastread cursor scroll
for select [Id] from [Content_Table] where [Catalog_Id]=@Catalog_Id order by [Id] desc
这一句还是要把所有记录返回到游标中去啊,跟遍历数据库有什么区别呢?
我把select 加上 top 300 之类的速度马上就快了,说明游标并不是只返回相应页码的数据集嘛......