Access分页及效率分析(MSSQL Server、Oracle分页)

时间:2016-06-07 15:50:11 来源:互联网 作者: 神秘的大神 字体:

在实际过运用过程中,我们开发的项目有时需要支持多种数据库,那么在开发中我们会遇到不同的数据库在SQL语句方面还有区别,导致我们有些细节需要去调整,下面就分页功能在不同的数据库中的具体使用详细说明。 一、Access数据库分页与效率分析 由于Access操作

在实际过运用过程中,我们开发的项目有时需要支持多种数据库,那么在开发中我们会遇到不同的数据库在SQL语句方面还有区别,导致我们有些细节需要去调整,下面就分页功能在不同的数据库中的具体使用详细说明。

一、Access数据库分页与效率分析
由于Access操作简单,调用,迁移方便,节省费用,对于搭建者的能力要求也会低些,对于较小量的数据,我们使用Access数据库是比较适合的,但是随着数据量增加,达到几十万、几百万甚至更多的时候,那么数据库的效率就会出现问题了,这个时候比如像分页功能可能就出现问题了,下面我们就看看常见的Access分页有哪些方式?

方案一:使用ADO.NET本身的结果集,使用PageSize,AbsolutePage的属性来进行分页
当然我一般不推荐使用拖控件进行快速开发,拖控件会导致产生大量的垃圾代码,是网站效率低下,当然在后台可以使用部分控件,今天就不说.NET拖控件效率低下的问题了,使用ADO.NE的结果集方式,每次都要读入符合条件的所有记录,然后再定位于对应页的记录。当数据量大的时候,效率就十分的低下。
方案二:使用not in 方式

select top 3 * from Article where Id not in(select top 6 Id from Article)
使用not in 方式,其中的top效率很高,但是not in 呢? 测试发现,当数据量比较小时还是挺快的,但是当到达10万条数据是,单击查询就慢了,如果使用该分页方式,当数据量很大时,估计天天有人在骂:这是哪个SB开发的系统啊,这么垃圾!
方案三:使用select top pageSize * from (select top pageindex*pageSize * from ywgl_news order by id desc) order by id
在实际过程中发现,当数据量比较大是,使用这种方式分页,Access的效率还可以,比not in 方式效率高多了,但是此处也需要注意的是:很多人喜欢使用select * from 表名, 实际中发现这不是一个好习惯,我们应该需要什么字段查询什么字段,这样能够极大的节省服务器资源。

二、MSSQL Server和Oracle数据库的分页
当然MSSQL Server和Oracle数据库的分页可以选择的方式更多,除了使用ADO.NET数据集、Not in 方式、Select top方式外,还有row_number方式等更好的分页实现方式,
select * from
(select * ,row_number() over(order by Id) rownumber from T_Users) as t
where t.rownumber>4 and t.rownumber<=6
需要注意的是:在MSSQL Server和Oracle中使用row_number还有些细节不同,下面就是Oracle和MS SqlServer中的具体分页方式:
int start = (pageindex - 1) * pageSize;
int end = pageindex * pageSize;
Oracle的分页T-SQL语句:
string sql = "select * from(select a.*,rownum row_num from(select * from ywgl_news t {0} order by t.Id desc) a)b where b.row_num>" + start.ToString() + " and b.row_num<=" + end.ToString() + "";
MSSQL Server的分页T-SQL语句:
string sql = "select * from(select * ,row_number() over(order by Id) rownumber from ywgl_news {0}) as t where t.rownumber>"+start.ToString()+" and t.rownumber<="+end;

三、附录存储过程的写法(MSSQL SERVER为例)
--创建存储过程row_number方式
alter proc GetPageForRownumber
(
@pageIndex int,--当前页
@pageSize int,--页容量
@rowCount int out,--总行数
@pageCount int out --总页数
)
as
begin
declare @sql nvarchar(225)
select @rowCount=count(Id),@pageCount=ceiling((count(Id)+0.0)/@pageSize) from T_Users
set @sql='select * from
(select * ,row_number() over(order by Id) rownumber from T_Users) as t
where t.rownumber>'+str((@pageIndex-1)*@pageSize)+' and t.rownumber<='+str(@pageIndex*@pageSize)+''
exec(@sql)
end

---测试row_number 方式的存储过程
declare @rc int,@pc int
exec GetPageForRownumber 3,2,@rc out,@pc out
select @rc,@pc

四、开发中遇到的小问题
1、报错"标准表达式中数据类型不匹配。"
Access在进行参数化查询的时候老是报错,这让哥很纳闷啊,看着SQL语句也是对的,参数的值也是对的,为什么老是提示报错呢?如下图代码就会报该错误。
string sql = "UPDATE ywgl_News set New_Title=@New_Title, New_Source=@New_Source ,New_ReadCount=@New_ReadCount,New_Content=@New_Content,New_Summary=@New_Summary,New_Author=@New_Author,New_ClassId=@New_ClassId where Id=@Id";
OleDbParameter[] para = new OleDbParameter[]
{

new OleDbParameter("@Id",model.New_id),
new OleDbParameter("@New_Title",ToDBValue(model.New_title)),
new OleDbParameter("@New_Source",ToDBValue(model.New_source)),
new OleDbParameter("@New_ReadCount",ToDBValue(model.New_readcount)),
new OleDbParameter("@New_Content",ToDBValue(model.New_content)),
new OleDbParameter("@New_Summary",ToDBValue(model.New_summary)),
new OleDbParameter("@New_Author", ToDBValue(model.New_author)),
new OleDbParameter("@New_ClassId",ToDBValue(model.New_class.Id))
};
num = AccessHelper.ExecuteNonQuery(sql, para);
经过查找原因,原来问题出在”@“符号上了,我们可以用”?“占位符替换,这个MSSQL Server有点小不同,如果使用了”@“那么就要确保各个参数的顺序一致。否则就报该错误。
2、在删除操作时删除不了
各个数据库具体T-SQL语句还有些不同,在Oracle中中 string sql = "Delete ywgl_news where Id=:Id";可以删除,没问题,但是在Access中这样写就不行了应该这样写:
string sql = "Delete from ywgl_news where Id=@Id";

五、总结三种不同数据库的分页方式及效率
那么我们在实际应用中,到底该选择哪种类型的数据库呢?使用Access,还是MSSQL Server,还是Oracle?不要觉得Oracle就觉得你的系统很牛B,这个需要根据系统的定位和使用者来进行确定,如果说是一个很小的政府门户网站,数据量也很小,那么用一个Access完全够了,而且数据量很小的时候,Access的速度还更快,当然如果说是做GIS的国土数据整合系统,那像这样的海量的数据,那就肯定需要用像Oracle大型数据库了。