通用分页存储过程

最新推荐文章于 2018-05-23 14:50:18 发布
原创 最新推荐文章于 2018-05-23 14:50:18 发布 · 411 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

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


niuyongaiai
关注
sql server 通用分页存储过程
08-17
1.查询返回的表、列名以及排序列没有写死，可以根据需要将这些放到存储过程的输入参数中； 2.适用于所有需要分页的单表或多表联合查询。
基于Sql Server通用分页存储过程的解决方法
12-15
以下是一个通用的分页存储过程的创建代码： ```sql CREATE PROCEDURE [dbo].[Common_GetPagedList] @TableName nvarchar(100), -- 表名 @ColumnNames nvarchar(1000) = '*', -- 字段名集合(全部字段为*, 其它逗号...
我认为比较好的分页存储过程
weixin_30355437的博客
01-14 125
create PROCEDURE Sp_Conn_Sort(@tblName varchar(255), -- 表名 @strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的字段名 @PageSize int = 40, -- 页尺寸 @Pa...
通用存储过程.分页存储过程（转）
weixin_30810239的博客
07-27 120
/*名称:spAll_ReturnRows输入:输出:调用: EXEC spAll_ReturnRows 'SELECT * FROM 表名', 页号, 返回记录数, '主键', '排序字段' spAll_ReturnRows 'SELECT * FROM all_Categories',2,10,'[ID]','[ID]'说明:[百万级]通用存储过程.分页存储过程..返回指定返回条数、指...
一个通用的分页存储过程(原创)
weixin_30252709的博客
06-16 116
2006年6月14日 作者:meil 博客:meil75.bokee.com 本文版权归作者meil转载请注明出处 这是我项目中使用的一个分页存储过程，具有很强的通用性。配合前台ASP.NET使用50万条数据基本感不到延迟。数据库为SQLServer2000。 1.分页存储过程 CREATE procedure pagination @st...
Sql Server 通用分页存储过程(适用与 BootStrap Table)
10-05
通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
通用分页存储过程，源码共享，大家共同完善
09-11
【通用分页存储过程】是数据库管理中一种常见的技术，用于在大量数据中实现高效的分页查询。在大型数据集的展示中，分页能够帮助用户逐步加载和浏览数据，而不是一次性加载所有记录，从而提高用户体验和系统性能。...
高效的存储过程分页代码
mike0807的专栏
10-09 437
CREATE PROCEDURE pagination @tblName varchar(255), -- 表名 @strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @doCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0,
sql Server 通用分页存储过程
09-10
sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程
通用Mysql分页存储过程
05-17
通用Mysql分页存储过程，支持多表查询。
通用的分页存储过程
qq_42273983的博客
05-23 306
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。分隔符MySQL默认以";"为分隔符，如果没有声明分隔符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用...
最通用的分页存储过程
hexzwj的专栏
07-31 418
 -- 最通用的分页存储过程    -- 获取指定页的数据      -- Powered By:CHENQP   -- Modify Date:17:08 2006-9-15   CREATE PROCEDURE usp_Pagination         @tblName   varchar(255),       -- 表名          @strGet
Oracle通用分页存储过程的实现与优化
Oracle通用分页存储过程是指在Oracle数据库中实现分页查询的存储过程，其目的是为了在查询大量数据时，能够有效地分批次获取数据，提高数据库查询的效率和响应速度。以下将详细介绍关于Oracle通用分页存储过程的知识...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值