- 浏览: 426235 次
- 性别:
- 来自: 广州
文章分类
最新评论
-
xiaomuxingren:
你好,请问update-alternatives --inst ...
JDK8安装及jenkins安装 -
wahahachuang8:
我觉得这种东西自己开发太麻烦了,就别自己捣鼓了,找个第三方,方 ...
HTML5 WebSocket -
晨曦的朝阳:
刚好有需求,学习一下!
MySql主从配置 -
mfkvfn:
这是恶意的商业竞争呀
解决 android 微信5.0及后续版本无法打开电话号码的问题 -
fwyhf:
require('./monitor/module_liste ...
NodeJS Server 进程自动重启
建立一个 Web 应用,分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法,也就是利用ADO自带的分页功能(利用游标)来实现分页。但这种分页方法仅适用于较小数据量的情形,因为游标本身有缺点:游标是存放在内存 中,很费内存。游标一建立,就将相关的记录锁住,直到取消游标。游标提供了对特定集合中逐行扫描的手段,一般使用游标来逐行遍历数据,根据取出数据条件的 不同进行不同的操作。而对于多表和大表中定义的游标(大的数据集合)循环很容易使程序进入一个漫长的等待甚至死机。 更重要的是,对于非常大的数据模型而言,分页检索时,如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据,而非检索所有的数据,然后单步执行当前行。 最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标,由于游标的局限性,所以这个方法并没有得到大家的普遍认可。 后来,网上有人改造了此存储过程,下面的存储过程就是结合我们的办公自动化实例写的分页存储过程: 以上存储过程运用了SQL SERVER的最新技术――表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然,在这个过程中,您也可以把其中的表变量写成临时 表:CREATE TABLE #Temp。但很明显,在SQL SERVER中,用临时表是没有用表变量快的。所以笔者刚开始使用这个存储过程时,感觉非常的不错,速度也比原来的ADO的好。但后来,我又发现了比此方 法更好的方法。 笔者曾在网上看到了一篇小短文《从数据表中取出第n条到第m条的记录的方法》,全文如下: 我当时看到这篇文章的时候,真的是精神为之一振,觉得思路非常得好。等到后来,我在作办公自动化系统(ASP.NET+ C#+SQL SERVER)的时候,忽然想起了这篇文章,我想如果把这个语句改造一下,这就可能是一个非常好的分页存储过程。于是我就满网上找这篇文章,没想到,文章 还没找到,却找到了一篇根据此语句写的一个分页存储过程,这个存储过程也是目前较为流行的一种分页存储过程,我很后悔没有争先把这段文字改造成存储过程: 其实,以上语句可以简化为: 但这个存储过程有一个致命的缺点,就是它含有NOT IN字样。虽然我可以把它改造为: 即,用not exists来代替not in,但我们前面已经谈过了,二者的执行效率实际上是没有区别的。既便如此,用TOP 结合NOT IN的这个方法还是比用游标要来得快一些。 虽然用not exists并不能挽救上个存储过程的效率,但使用SQL SERVER中的TOP关键字却是一个非常明智的选择。因为分页优化的最终目的就是避免产生过大的记录集,而我们在前面也已经提到了TOP的优势,通过TOP 即可实现对数据量的控制。 在分页算法中,影响我们查询速度的关键因素有两点:TOP和NOT IN。TOP可以提高我们的查询速度,而NOT IN会减慢我们的查询速度,所以要提高我们整个分页算法的速度,就要彻底改造NOT IN,同其他方法来替代它。 我们知道,几乎任何字段,我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值,所以如果这个字段不重复,那么就可以利用 这些不重复的字段的max或min作为分水岭,使其成为分页算法中分开每页的参照物。在这里,我们可以用操作符“>”或“<”号来完成这个使 命,使查询语句符合SARG形式。如: 于是就有了如下分页方案: 在选择即不重复值,又容易分辨大小的列时,我们通常会选择主键。下表列出了笔者用有着1000万数据的办公自动化系统中的表,在以GID(GID是 主键,但并不是聚集索引。)为排序列、提取gid,fariqi,title字段,分别以第1、10、100、500、1000、1万、10万、25万、 50万页为例,测试以上三种分页方案的执行速度:(单位:毫秒) 从上表中,我们可以看出,三种存储过程在执行100页以下的分页命令时,都是可以信任的,速度都很好。但第一种方案在执行分页1000页以上后,速度就降了下来。第二种方案大约是在执行分页1万页以上后速度开始降了下来。而第三种方案却始终没有大的降势,后劲仍然很足。 在确定了第三种分页方案后,我们可以据此写一个存储过程。大家知道SQL SERVER的存储过程是事先编译好的SQL语句,它的执行效率要比通过WEB页面传来的SQL语句的执行效率要高。下面的存储过程不仅含有分页方案,还 会根据页面传来的参数来确定是否进行数据总数统计。 以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况: 如果@OrderType不是0,就执行降序,这句很重要! 如果是第一页就执行以上代码,这样会加快执行速度 以下代码赋予了@strSQL以真正执行的SQL代码 上面的这个存储过程是一个通用的存储过程,其注释已写在其中了。 在大数据量的情况下,特别是在查询最后几页的时候,查询时间一般不会超过9秒;而用其他存储过程,在实践中就会导致超时,所以这个存储过程非常适用于大容 量数据库的查询。 笔者希望能够通过对以上存储过程的解析,能给大家带来一定的启示,并给工作带来一定的效率提升,同时希望同行提出更优秀的实时数据分页算法。 以上的这第三种存储过程在小数据量的情况下,有如下现象: 笔者就此分析了一下,原来产生这种现象的症结是如此的简单,但又如此的重要:排序的字段不是聚集索引! 笔者只所以把“查询优化”和“分页算法”这两个联系不是很大的论题放在一起,就是因为二者都需要一个非常重要的东西――聚集索引。 但要既使聚集索引列既符合查询列的需要,又符合排序列的需要,这通常是一个矛盾。笔者前面“索引”的讨论中,将fariqi,即用户发文日期作为了 聚集索引的起始列,日期的精确度为“日”。这种作法的优点,前面已经提到了,在进行划时间段的快速查询中,比用ID主键列有很大的优势。 但在分页时,由于这个聚集索引列存在着重复记录,所以无法使用max或min来最为分页的参照物,进而无法实现更为高效的排序。而如果将ID主键列作为聚集索引,那么聚集索引除了用以排序之外,没有任何用处,实际上是浪费了聚集索引这个宝贵的资源。 为解决这个矛盾,笔者后来又添加了一个日期列,其默认值为getdate()。用户在写入记录时,这个列自动写入当时的时间,时间精确到毫秒。即使这样,为了避免可能性很小的重合,还要在此列上创建UNIQUE约束。将此日期列作为聚集索引列。 有了这个时间型聚集索引列之后,用户就既可以用这个列查找用户在插入数据时的某个时间段的查询,又可以作为唯一列来实现max或min,成为分页算法的参照物。 经过这样的优化,笔者发现,无论是大数据量的情况下还是小数据量的情况下,分页速度一般都是几十毫秒,甚至0毫秒。而用日期段缩小范围的查询速度比原来也没有任何迟钝。聚集索引是如此的重要和珍贵,所以笔者总结了一下,一定要将聚集索引建立在:CREATE procedure pagination1
(@pagesize int, --页面大小,如每页存储20条记录
@pageindex int --当前页码
)
as
set nocount on
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 gid from TGongwen
where fariqi >dateadd(day,-365,getdate()) order by fariqi desc
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t
where O.gid=t.nid and t.id>@PageLowerBound
and t.id<=@PageUpperBound order by t.id
end
set nocount off从publish 表中取出第 n 条到第 m 条的记录:
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
(SELECT TOP n-1 id
FROM publish))
id 为publish 表的关键字 CREATE PROCEDURE pagination2
(
@SQL nVARCHAR(4000), --不带排序语句的SQL语句
@Page int, --页码
@RecsPerPage int, --每页容纳的记录数
@ID VARCHAR(255), --需要排序的不重复的ID号
@Sort VARCHAR(255) --排序字段及规则
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str=''SELECT TOP ''+CAST(@RecsPerPage AS VARCHAR(20))+'' * FROM
(''+@SQL+'') T WHERE T.''+@ID+''NOT IN (SELECT TOP ''+CAST((@RecsPerPage*(@Page-1))
AS VARCHAR(20))+'' ''+@ID+'' FROM (''+@SQL+'') T9 ORDER BY ''+@Sort+'') ORDER BY ''+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GOSELECT TOP 页大小 *
FROM Table1 WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id))
ORDER BY IDSELECT TOP 页大小 *
FROM Table1 WHERE not exists
(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id )
order by idSelect top 10 * from table1 where id>200
select top 页大小 *
from table1
where id>
(select max (id) from
(select top ((页码-1)*页大小) id from table1 order by id) as T
)
order by id
页码
方案1
方案2
方案3
1
60
30
76
10
46
16
63
100
1076
720
130
500
540
12943
83
1000
17110
470
250
10000
24796
4500
140
100000
38326
42283
1553
250000
28140
128720
2330
500000
121686
127846
7168
获取指定页的数据:CREATE PROCEDURE pagination3
@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 else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"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 + "] "+ @strOrderend
else
beginset @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
end
end
exec (@strSQL)
GO
1、分页速度一般维持在1秒和3秒之间。
2、在查询最后一页时,速度一般为5秒至8秒,哪怕分页总数只有3页或30万页。
虽然在超大容量情况下,这个分页的实现过程是很快的,但在分前几页时,这个1-3秒的速度比起第一种甚至没有经过优化的分页方法速度还要慢,借用户的话说就是“还没有ACCESS数据库速度快”,这个认识足以导致用户放弃使用您开发的系统。
在前面的讨论中我们已经提到了,聚集索引有两个最大的优势:
1、以最快的速度缩小查询范围。
2、以最快的速度进行字段排序。
第1条多用在查询优化时,而第2条多用在进行分页时的数据排序。
而聚集索引在每个表内又只能建立一个,这使得聚集索引显得更加的重要。聚集索引的挑选可以说是实现“查询优化”和“高效分页”的最关键因素。
1、您最频繁使用的、用以缩小查询范围的字段上;
2、您最频繁使用的、需要排序的字段上。
发表评论
-
MySql悲观锁和乐观锁的基础理解
2016-08-12 15:51 859mysql悲观锁和乐观锁的理解 1、悲观锁 悲观锁顾名思意, ... -
MySql主从配置
2016-06-15 16:18 897今天发现Mysql的主从数据库没有同步 先上Master库 ... -
Oracle常用命令
2010-12-25 11:24 1142本篇为oracle的一些常用命令的收集,对于玩命令的玩家是一件 ... -
Oracle学习集锦
2010-11-15 21:36 1363Oracle的基本语句 Oracle 数据库中的服务项: ... -
cmd命令提示行连接mysql乱码的解决
2010-06-09 20:31 3572下面的介绍是设置为gb2312的,而实际上很多 ... -
mysql alter 语句用法,添加、修改、删除字段等
2009-06-06 23:51 1110//主键 alter table tabelname ... -
ubuntu9.0.4 mysql乱码解决办法
2009-06-05 11:14 1385操作系统环境: ubuntu9.0.4、MySql5.1 ... -
SQL语句应用大全
2009-05-13 17:14 1944SQL与ACCESS、EXCEL的导入导出 ... -
SQL简单常用语句
2009-05-13 17:14 1100(1) 数据记录筛选: sql=&quo ... -
存储过程和用户自定义函数的区别
2009-05-13 17:13 1937存储过程 : 存储过程可以使得对数据 ... -
在SQL Server2000中处理半个汉字的问题
2009-05-13 17:13 1516问:由于从其它数据库中导入的数据存在半个汉字的问题,所以希望能 ... -
MySQL导入导出
2009-05-13 17:12 1156mysql -q -s -e "se ... -
mysql用户及权限管理
2009-05-13 17:11 2360首先要声明一下:一般情况下,修改 MySQL 密码,授权,是 ... -
mysql学习笔记
2009-05-13 17:11 10461.登录 MySQL(和PHP搭 ... -
mysql常用授权
2009-05-13 17:10 1016GRANT 语句的语法如下: GRANT privi ... -
sql server 2000存储过程例子
2009-05-13 17:09 3226/****** Object: Stored Procedu ... -
mysql查看表结构命令
2009-05-13 17:08 9301mysql查看表结构命令,如下:--查看表结构信息(本人相看, ... -
limit动态参数问题
2009-05-13 17:07 1508先说两句体外话:好久没在CSDN的blog写东西了,一个原因是 ... -
mysql存储过程经典语句(非常的常用)
2009-05-13 17:07 1523存储过程: create procedure p() be ... -
mysql游标的应用
2009-05-13 17:06 3077create procedure test(out d flo ...
相关推荐
本文将详细解析"经典的分页、排序SQL 通用存储过程"所涉及的知识点,并给出如何实现这样的存储过程。 首先,分页是数据库查询中的一种优化策略,用于限制每次查询返回的结果数量,从而避免一次性加载大量数据导致的...
sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程
SQLserver存储过程异常处理
通用sql分页存储过程,提供12参数可供选择。其中,提供两种分页方案被选择和相关排序方式,支持自定义查询、自定义排序等
SQL 通用 分页 存储 过程 完整代码
内容概要:简单的C# winform调用存储过程实例,创建存储过程入参,通过SqlConnection对象和SqlCommand对象调用存储过程,获取存储过程的出参并显示出来,详细代码注释,希望对用到C#调用存储过程的小伙伴有帮助 ...
【SQL通用存储过程分页】是指在数据库管理中,通过创建一个存储过程来实现对多表数据的分页查询。这种存储过程可以适应多种不同表结构的查询需求,避免为每张表单独编写分页查询的存储过程,提高了开发效率。在本例...
### SQL通用查询存储过程知识点详解 #### 一、概述 SQL通用查询存储过程是一种高度灵活且可重用的数据库对象,旨在通过动态构建SQL查询来实现数据的灵活检索。这种方式可以显著减少应用程序中的代码量,并增强对...
标题提到的“通用的SQL server分页存储过程”提供了一种解决方案,可以避免重复编写存储过程,只需调整参数即可满足不同分页需求。 分页存储过程的核心思想是利用`OFFSET`和`FETCH NEXT`这两个SQL Server 2012及更...
通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
比较通用的sql分页存储过程存储过程 比较通用的sql分页存储过程存储过程
标题提到的是一个针对SQL Server 2005的通用分页存储过程。这个存储过程可以帮助用户高效地实现数据分页功能,尤其是在处理大数据量时,能够保持较快的响应速度。 #### 描述分析 描述部分强调了该存储过程的优点:...
"通用存储过程+增删改查"的主题涵盖了数据库中最基础且重要的操作:插入(Insert)、删除(Delete)、更新(Update)和查询(Select)。这些操作是数据库应用开发的核心,通过存储过程实现,能提高数据处理的性能,...
"SQL通用分页存储过程"就是一种专门用于实现分页查询的存储过程,它可以接收用户输入的参数,灵活地返回指定页码的数据。 首先,我们需要理解分页的基本概念。在数据库查询中,如果一次性获取所有数据可能导致内存...
mysql通用存储过程sql脚本,支持多表,子查询,高级查询 分页
值得注意的是,脚本中包含了对不同类型的数据库对象(如存储过程、触发器、函数和视图)的处理逻辑,表明了该脚本的通用性和灵活性。 具体而言,脚本首先确定目标对象的类型和父对象ID,然后从`syscomments`表中...
sqlserver通用存储过程,全面好用,你可下载看看代码哦,保证最全面。
通用查询,通用修改,通用插入,通用删除,模糊查询存储过程,上一个通用查询存储过程有错勿下,发现不够及时请原谅
mysql 通用存储过程,,下载直接导入mysql工具中即可使用,包含所有通用查询功能,下载资源即可使用