1、 目的
1.1 提高现在的分页存储过程性能,给所有分页存储过程提供一个标准的调用接口。
2、 两个存储结构性能分析对比
一、 原有通用分页存储过程
大体写法格式 分六个部分
1 输出字段列表
整理列出需要输出字段
例:
SET @FieldList = '
PersonID,
PersonNameID,
SkillPKId,
SkillName,
StartDate,
EndDate,
AuthoriseOrganization,
OriginalFile '
2 数据检索来源
指定检索的数据源
例:
SET @FromClause = ' VW_HR_PERSON_SKILL '
3 检索条件子句
给出检索过滤条件
例:
' PersonID IN
(SELECT RightContributor
FROM HR_RESOURCE_RIGHT
WITH (NOLOCK)
WHERE RightOwner = ''' + @personId + ''' AND
ContributorType = 1 AND
ViewPersonUnitInfo = 1 ' +
') '
4 完整检索SQL语句
拼装完整的SQL语句
例:
SET @basicSQL = ' SELECT '+@FieldList+ ' FROM ' + @FromClause + ' WITH (NOLOCK) WHERE ' + @WhereClause
5 在页面需要统计总行数时
需要COUNT 一次,输出总行数,作为返回值。
6 执行分页输出数据
根据分页排序要求,用嵌套子查询方法,先将整个数据集正序排序检索的到指定页以前所有行数(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 ' )
7 总结分析问题:
这里为了得到所要的数据,检索进行了三次子查询,可以肯定的会影响性能。
8 原存储过程示例:
/*###############组装数据检索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 + ' * FROM (' + @basicSQL + ') A ORDER BY ' + @OrderClause + ' ) B ORDER BY ' + @OrderClause + ' desc ' )
二、 新的分页存储过程
新的分页存储过程利用SQL2005的新特性RowNumber 函数
大体写法格式 分六个部分
1 输出字段列表
这个部分要指定新特性RowNumber 函数,在RowNumber 函数指定排序字段、排序方法以及另名。
例:
SET @FieldList = '
ROW_NUMBER() OVER(ORDER BY CustomerPKId DESC) AS RowIndex,
CustomerPKId,
CustomerName,
WebSite,
ProvinceCityName,
SaleRepresentNameId,
ServicePersonNameId,
CreateDateTime,
EditLock
2 数据检索来源
指定检索的数据源
例:
SET @FromClause = ' VW_CUS_CUSTOMER '
3 检索条件子句
给出检索过滤条件
例:
SET @WhereClause=
' PersonID IN
(SELECT RightContributor
FROM HR_RESOURCE_RIGHT
WITH (NOLOCK)
WHERE RightOwner = ''' + @personId + ''' AND
ContributorType = 1 AND
ViewPersonUnitInfo = 1 ' +
') '
4 完整检索SQL语句
拼装完整的SQL语句,在这个过程中就要多加上RowNumber函数的范围行数
例:
SET @basicSQL = '
SELECT * FROM (
SELECT '+@FieldList+ ' FROM ' + @FromClause + ' WITH (NOLOCK) WHERE ' + @WhereClause+
) A
' WHERE RowIndex BETWEEN ' +
CONVERT(VARCHAR, ( @CurrentPageNo - 1 ) * @PerPageCount + 1) +
' AND ' + CONVERT(VARCHAR, @CurrentPageNo * @PerPageCount)
5 在页面需要统计总行数时
需要COUNT 一次,输出总行数,作为返回值。
6 分析问题总结:
1) 在用新的分页存储过程中检索所需要的页数时只对RowNumber函数行取出所需要的行数就行,可以省去几次子查询。
2) 把新的存储过程改进后,可以做成一个标准存储过程,以后分页存储过程都可以通过调用这个标准的存储过程。
三、 两个分页存储过程在相同数据情况下的执行性能对比
a) 使用没有索引对比表数据
三次平均值 | 1万条记录执行时间 表中实际6,157条记录中查询100条记录 | 1-10万条记录执行时间(MS) 表中实际567,604条记录中查询100条记录 | 10万条以上记录执行时间(MS) 表中实际15,841,860 条记录中查询100条记录 |
原来 存储过程 | ms(70) | 00:00:10-- ms(10423) | 00:04:34 -- ms(266776) |
新 存储过程 | ms(33) | 00:00:01- ms(1063) | 00:00:46 -- ms(46700) |
b) 使用索引对比表数据
三次平均值 | 1万条记录执行时间 表中实际6,157条记录中查询100条记录 | 1-10万条记录执行时间(MS) 表中实际567,604条记录中查询100条记录 | 10万条以上记录执行时间(MS) 表中实际15,841,860 条记录中查询100条记录 |
原来 存储过程 | ms(66) | ms(16) | ms(86) |
新 存储过程 | ms(8) | ms(13) | ms(45) |
四、 结论
a) 新的方法存储过程可以做成一个标准的存储过程,供其他地方统一调用,简单方便。
b) 在测试的性能上有比较大的提高。
五、 标准通用分页存储过程
1 标准通用分页存储过程原形。
CREATE PROCEDURE [dbo].[COMMON_PROCEDURE_SelectWithPage]
@Sql VARCHAR(8000),
@CurrentPageNo INT,
@PerPageCount INT,
@TotalNum INT OUTPUT
AS
SET NOCOUNT ON --阻止在结果集中返回可显示受Transact-SQL
语句或存储过程影响的行计数的消息,为ON 时,不返回计数
DECLARE @SqlCmd NVARCHAR(4000)
------------------------------------------
--查询数据
SET @SqlCmd = 'SELECT * FROM (' + @Sql + ') A WHERE RowIndex BETWEEN
+ CONVERT(VARCHAR, ( @CurrentPageNo - 1 ) * @PerPageCount + 1)
+ ' AND ' + CONVERT(VARCHAR, @CurrentPageNo * @PerPageCount)
EXEC sp_executesql @SqlCmd --执行Transact-SQL语句
------------------------------------------
--求记录总数
IF @TotalNum = -1
BEGIN
CREATE TABLE #Temp1 ( num INT )
INSERT INTO #Temp1
EXEC ( 'SELECT count(*) FROM (' + @Sql + ') A')
SELECT @TotalNum = ( SELECT * FROM #Temp1 )
DROP TABLE #Temp1
END
2 调用示例
SET @basicSQL = 'SELECTROW_NUMBER() OVER(ORDER BY CreateDateTime DESC) AS RowIndex,
CdmuExperiencePKId,
ExperiencePKId,
MemberPKId,
LoginAccount,
CustomerPKId,
CustomerName,
ShopState,
ExperienceClass,
ProcessSatae,
EndDate,
EmployeePKId,
EmployeeNameId,
DemandDate
FROMdbo.VW_BIZ_SHOP_MAIN
WHEREAgentMemberPKId = '
+ CONVERT(VARCHAR, @AgentMemberPKId)
EXEC dbo.COMMON_PROCEDURE_SelectWithPage
@basicSQL, -- varchar(8000)
@CurrentPageNo, -- int
@PerPageCount, -- int
@TotalNum OUTPUT