(讨论)分页存储过程示例

Question

这个存储过程应该怎样改进,在博客完里面看到一篇文章,希望大家讨论一下
主要是效率的问题,这个存储如果查询大数据量时,会出问题,应该怎么改进?SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--名称：分页存储过程
--使用示例 EXEC sp_PageIndex '*',' FROM StuSources ',2,10
--注意 
--目前还没有对输入的参数进行严格的验证
--默认为输入都是合法有效的ALTER  PROC sp_PageIndex
 @sqlSelect varchar(800) --SELECT 后面 FROM 前面 的 字段 不用包含SELECT
,@sqlFrom varchar(800) --FROM 后面 的 字段 包含FROM
,@countPerPage int -- 每页数据行数
,@toPage int --要转到的页码ASBEGIN-- 根据每页数据行数 和 要转到的页码 得到 数据起止点
Declare @start int
Declare @end intset @end = @countPerPage * @toPage
set @start = @countPerPage * (@toPage - 1) + 1
-- 临时表名称 可随机命名
Declare @tmpTable varchar(10)
SET @tmpTable ='#tmp'Declare @sqlStr varchar(800)
-- 创建数据源到临时表
SELECT @sqlStr = 'SELECT Identity(int,1,1) AS RowIndex,'
SELECT @sqlStr = @sqlStr + rtrim(@sqlSelect) + ' INTO  '+ @tmpTable 
SELECT @sqlStr = @sqlStr + rtrim(@sqlFrom) 
-- 查询临时表 得到所需要的数据
SELECT @sqlStr = @sqlStr + ' '+'SELECT '+ rtrim(@sqlSelect) +' FROM ' + @tmpTable 
SELECT @sqlStr = @sqlStr + ' WHERE  RowIndex BETWEEN ' + Convert(char,@start) + " AND " + Convert(char,@end)
-- 删除临时表
SELECT @sqlStr = @sqlStr + ' '+'DROP TABLE '+@tmpTable
EXEC (@sqlStr)ENDGO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Answer 1

@sqlSelect varchar(800) --SELECT 后面 FROM 前面 的 字段 不用包含SELECT
,@sqlFrom varchar(800) --FROM 后面 的 字段 包含FROM
,@countPerPage int -- 每页数据行数
,@toPage int --要转到的页码-------------------把这些参数凑齐，分页语句已近呼之欲出啦，还需要存储过程么

Answer 2

这个存储过程简单来说 还算好的，起码感觉比Top强些。
但是问题在于临时表存储的数据问题，如果生成的数据有大文本数据就很糟糕，那么相当于复制了一张表（当然排序后的表，生成了序号而已），这个表一旦很大，那就很糟糕了。所以不建议直接复制表，而是复制表的主键字段（通常是int类型，这个很小，最多不过是guid类型）即可。之后进行一次连接查询就可以。这里连接查询肯定是有消耗的，由于查询数据有限，这个消耗可以忽略。
而且临时表可以创建索引，消耗更可以忽略了。
并在生成临时数据明显小于LZ的查询方式，这地方的效率提升则是明显的，因为大家都知道查询一个小字段，比查询出对个字段（或长字段）效率是差距是很大的（这也是经常建议为什么查询不建议写select * 的原因之一）。至于实在的海量数据，那么即使这么做也没有办法的，但对于一般的情况我觉得足够了。