SQLServer2005和2008的分页技术比较_JAVA_编程开发_程序员俱乐部

    <span style="font-size: small">我们以前在开发大数据量的分页存储过程时，往往都为了怎么样实现高效的性能，而大伤脑筋，似乎总是想写出最佳的存储过程分页方法，我们假如建立一个学生基本信息表StudentInfo，我们看在Sql Server 2000中我们实现的存储过程： <br /> <br />CREATE&nbsp; PROCEDURE p_GetStudentInfo <br />@strWhere&nbsp;&nbsp; varchar(1500)&nbsp;&nbsp; -- 查询条件 (注意: 不要加 where) <br />,@PageSize&nbsp;&nbsp;&nbsp; int = 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- 页尺寸 <br />,@PageIndex&nbsp;&nbsp; int = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- 页码 <br />AS <br />BEGIN <br /> <br />declare @strSQL&nbsp;&nbsp; varchar(5000)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- 主语句 <br />declare @strTmp&nbsp;&nbsp; varchar(110)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- 临时变量 <br />declare @strOrder varchar(400)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- 排序类型 <br /> <br />set @strTmp = '&lt;(select min' <br />set @strOrder = ' order by studentinfoid desc' <br /> <br />if @PageIndex = 1 <br />begin <br />&nbsp;&nbsp;&nbsp;&nbsp; if @strWhere != ''&nbsp;&nbsp; <br />&nbsp;&nbsp;&nbsp;&nbsp; set @strSQL = 'select top ' + str(@PageSize) +' *&nbsp; from studentinfo where ' + @strWhere + ' ' + @strOrder <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set @strSQL = 'select top ' + str(@PageSize) +'&nbsp; *&nbsp;&nbsp; from studentinfo '+ @strOrder <br />--如果是第一页就执行以上代码，这样会加快执行速度 <br />end <br />else <br />begin <br />--以下代码赋予了@strSQL以真正执行的SQL代码 <br />set @strSQL = 'select top ' + str(@PageSize) +'&nbsp; *&nbsp; from studentinfo <br />where studentinfoid' + @strTmp + '(studentinfoid) from (select top ' + str((@PageIndex-1)*@PageSize) + ' studentinfoid from studentinfo' + @strOrder + ') as tblTmp)'+ @strOrder <br />if @strWhere != '' <br />&nbsp;&nbsp;&nbsp;&nbsp; set @strSQL = 'select top ' + str(@PageSize) +'&nbsp; * from studentinfo <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where studentinfoid' + @strTmp + '(studentinfoid) from <br />&nbsp;&nbsp;&nbsp; (select top ' + str((@PageIndex-1)*@PageSize) + ' studentinfoid from studentinfo where ' + @strWhere + ' ' <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder <br />end <br />--print (@strSQL) <br />exec (@strSQL) <br />END <br />GO <br />&nbsp; 我们在上面的存储过程中可以看到，如果执行的是第一页的记录时，我们可以看到只执行 <br /> <br />'select top ' + str(@PageSize) +' * from studentinfo where ' + @strWhere + ' ' + @strOrder <br />&nbsp; 就可以了，而如果翻页的时候，就会在条件查询中又嵌套子查询，势必性能会有所下降，而这个已经在我们Sql Server2000中算是高效的分页存储过程实现方式了，而现在如果我们换作Sql Server 2005的时候，我们是不是还是用这种方式呢? <br /> <br />&nbsp; 在Sql Server 2005中，我们可以利用新增函数row_number()来更高效的实现分页存储 <br /> <br />CREATE PROCEDURE p_GetStudentInfo <br />　　@PageSize INT, <br />　　@PageIndex INT, <br />　　@strWhere varchar(1500) -- 查询条件(注意: 不要加where) <br />　　As <br />　　Begin <br />　　select * from ( <br />　　select row_number() over (order by StudentInfoId) row,* from StudentInfo ) StudentInfo <br />　　where row between @PageSize*(@PageIndex-1) and @PageSize*@PageIndex <br />　　End <br />　　go <br />&nbsp; 依照群组显示每条记录在该群组中出现的顺序位置，在显示每条记录编号时非常有用，并且搭配OVER子句，这样就可以实现查询记录的条数了。 <br /> <br />　　我们现在来比较一下它们执行的性能，StudentInfo表中现在有30多万条数据，我们分别来执行下面的存储过程： <br /> <br />　　Exec p_GetStudentInfo <br />　　@PageSize = 10000, <br />　　@PageIndex = 10, <br />　　@strWhere = '1=1' <br /> <br /> <br />&nbsp; 在Sql Server 2000中，执行的时间是接近2秒； <br /> <br />&nbsp; 在Sql Server 2005中，执行的时间显示的是接近0秒。 <br /> <br />&nbsp; 如果数据涉及到千万级的数据时,比较上面两种方法,就可以看到一些显著的差别了</span>