通用存储过程分页改进.doc
文本预览下载声明
通用分页存储过程
文档类别 管理规范 文档编号 T03-00001-010914 来源部门 技术部研发部门 应用范围 技术部 保密级别 ★★★ 修订历史记录 日期 版本 说明 作者 2010-09-15 1.0 Create 王德寿
目 录
1、 目的 2
2、 两个存储结构性能分析对比 2
目的
提高现在的分页存储过程性能，给所有分页存储过程提供一个标准的调用接口。
两个存储结构性能分析对比
原有通用分页存储过程
大体写法格式 分六个部分
输出字段列表
例：
SET @FieldList =
PersonID,
PersonNameID,
SkillPKId,
SkillName,
StartDate,
EndDate,
AuthoriseOrganization,
OriginalFile
数据检索来源
例：
SET @FromClause = VW_HR_PERSON_SKILL
检索条件子句
例：
PersonID IN
(SELECT RightContributor
FROM HR_RESOURCE_RIGHT
WITH (NOLOCK)
WHERE RightOwner = + @personId + AND
ContributorType = 1 AND
ViewPersonUnitInfo = 1 +
)
完整检索SQL语句
拼装完整的SQL语句
例：
SET @basicSQL = SELECT +@FieldList+ FROM + @FromClause + WITH (NOLOCK) WHERE + @WhereClause
在页面需要统计总行数时
需要COUNT 一次，输出总行数，作为返回值。
执行分页输出数据
根据分页排序要求，用嵌套子查询方法，先将整个数据集正序排序检索的到指定页以前所有行数（TOP到当前页行所有行数），再倒序排序检索指定需要的行数（TOP需要的行数），最后按指定的排序要求输出数据。
例：
EXEC (SELECT * FROM (SELECT TOP + @PageCount + * FROM (SELECT TOP + @Pages + * FROM ( + @basicSQL + ) A ORDER BY PersonID ASC, StartDate DESC ) B ORDER BY PersonID DESC, StartDate ASC) C ORDER BY PersonID ASC, StartDate DESC )
总结分析问题：
这里为了得到所要的数据，检索进行了三次子查询，可以肯定的会影响性能。
原存储过程示例：
/*###############组装数据检索SQL语句开始###############*/
DECLARE @basicSQL VARCHAR(8000)
IF @WhereClause =
BEGIN
SET @basicSQL = SELECT +@FieldList+ FROM + @FromClause + WITH (NOLOCK)
END
ELSE
BEGIN
SET @basicSQL = SELECT +@FieldList+ FROM + @FromClause + WITH (NOLOCK) + WHERE + @WhereClause
END
--PRINT @basicSQL
--求记录总数
DECLARE @strSql VARCHAR(3000)
IF @totalNum=-1
BEGIN
CREATE TABLE #temp1(num INT)
INSERT #temp1 VALUES(0)
SET @strSql = SELECT @temp1 = COUNT(*) FROM ( + @basicSQL + ) A
SET @strSql=DECLARE @temp1 INT +@strSql+ UPDATE #temp1 SET num=@temp1
--PRINT (@strSql)
EXEC (@strSql)
SELECT @totalNum=(SELECT * FROM #temp1)
DROP TABLE #temp1
END
/*###############组装数据检索SQL语句结束###############*/
EXEC (SELECT * FROM (SELECT TOP + @PageCount + *
显示全部