问题提出:
在应用程序中经常需要查询数据。当查询结果数据量比较大的时候,检索结果、界面显示都需要花费大量的时间。为了避免这个问题,应该每次只检索部分数据,也就是使用常见的分页方式来处理。分页的问题在asp.net中好像非常简单,只要在GridView中启用分页就可以了。启用分页后,GridView关联数据源控件,依旧会加载所有的数据。这个解决方法只是“掩耳盗铃” ,会导致在大数据量的情况下导致查询的效率变低。
解决方法:
使用GridView的自定义分页功能。使用自定义分页功能需要实现两个逻辑:得到结果集的总数、查找自定范围的数据。
GridView 分页实现,可以参考 Scott Mitchell 文章 Efficiently Paging Through Large Amounts of Data
本文组要介绍如何在使用SQL Server 实现排序获取分页数据。
1、使用子查询+TOP关键字方式
if object_id('GetStudentPaged') is not null create procedure GetStudentPaged |
如果需要按条件查找,条件可能会有所不同。通过参数将where条件传入到存储过程中。由于where子句不支持使用变量,所以需要将在存储过程中组合SQL语句,通过动态SQL方式执行。
create procedure GetStudentPaged set @sql = N'Select Top(' + ltrim(str(@maxmimumRows)) + N') * ' --判断是否有查询条件 set @sql = @sql + N'StudentId NOT IN (' if @whereExpression is not null and @whereExpression <> N'' --print @sql go |
现在就能在条件检索的情况下也实现分页了。下面的问题是,如果使用GridView时需要支持排序功能,那么还需要添加对排序的支持。其实这个问题就比较简单了,只要增加一个排序参数就可以了。
create procedure GetStudentPaged set @sql = N'Select Top(' + ltrim(str(@maxmimumRows)) + N') * ' --判断是否有查询条件 set @sql = @sql + N'StudentId NOT IN (' if @whereExpression is not null and @whereExpression <> N'' if @sortExpression is not null and @sortExpression <> N'' set @sql = @sql + ')' if @sortExpression is not null and @sortExpression <> N'' --print @sql go |
这里给出我使用Student表定义
create table Student ( |
使用T-SQL产生大量数据
declare @i int |
2、使用临时表实现
根据筛选、排序条件将符合条件的数据保存到临时表,并在临时表中增加一个自动增长的辅助列,用于获取指定范围的数据。
use TeachMis; if object_id('GetStudentPaged') is not null create procedure GetStudentPaged if (object_id('tempdb..#Student') is not null) --产生没有记录的临时表 declare @sql nvarchar(max) if @whereExpression is not null and @whereExpression <> '' if @sortExpression is not null and @sortExpression <> '' execute sp_executesql @sql select go |
3.使用 SQL Server 2005 的 CTE 表达式实现。
具体可以参考 Scott Mitchell 文章 Efficiently Paging Through Large Amounts of Data