2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高)

本文主要是介绍2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

-- /*-----存储过程 分页处理 SW 2005-03-28创建 -------*/
--
/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
--
/*-----存储过程 分页处理 2005-04-21修改 添加Distinct查询功能-------*/
--
/*-----存储过程 分页处理 2005-05-18修改 多字段排序规则问题-------*/
--
/*-----存储过程 分页处理 2005-06-15修改 多字段排序修改-------*/
--
/*-----存储过程 分页处理 2005-12-13修改 修改数据分页方式为top max模式 性能有极大提高-------*/
--
/*-----缺点:相对之前的not in版本主键只能是整型字段,如主键为GUID类型请使用not in 模式的版本-------*/
CREATE   PROCEDURE  dbo.proc_ListPageInt
(
@tblName       nvarchar ( 200 ),         -- --要显示的表或多个表的连接
@fldName       nvarchar ( 500 =   ' * ' ,     -- --要显示的字段列表
@pageSize      int   =   10 ,         -- --每页显示的记录个数
@page          int   =   1 ,         -- --要显示那一页的记录
@pageCount      int   =   1  output,             -- --查询结果分页后的总页数
@Counts      int   =   1  output,                 -- --查询到的记录数
@fldSort      nvarchar ( 200 =   null ,     -- --排序字段列表或条件
@Sort          bit   =   0 ,         -- --排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')
@strCondition      nvarchar ( 1000 =   null ,     -- --查询条件,不需where
@ID          nvarchar ( 150 ),         -- --主表的主键
@Dist                   bit   =   0             -- --是否添加查询字段的 DISTINCT 默认0不添加/1添加
)
AS
SET  NOCOUNT  ON
Declare   @sqlTmp   nvarchar ( 1000 )         -- --存放动态生成的SQL语句
Declare   @strTmp   nvarchar ( 1000 )         -- --存放取得查询结果总数的查询语句
Declare   @strID       nvarchar ( 1000 )         -- --存放取得查询开头或结尾ID的查询语句

Declare   @strSortType   nvarchar ( 10 )     -- --数据排序规则A
Declare   @strFSortType   nvarchar ( 10 )     -- --数据排序规则B

Declare   @SqlSelect   nvarchar ( 50 )          -- --对含有DISTINCT的查询进行SQL构造
Declare   @SqlCounts   nvarchar ( 50 )           -- --对含有DISTINCT的总数查询进行SQL构造


if   @Dist    =   0
begin
    
set   @SqlSelect   =   ' select  '
    
set   @SqlCounts   =   ' Count(*) '
end
else
begin
    
set   @SqlSelect   =   ' select distinct  '
    
set   @SqlCounts   =   ' Count(DISTINCT  ' + @ID + ' ) '
end


if   @Sort = 0
begin
    
set   @strFSortType = '  ASC  '
    
set   @strSortType = '  DESC  '
end
else
begin
    
set   @strFSortType = '  DESC  '
    
set   @strSortType = '  ASC  '
end



-- ------生成查询语句--------
--
此处@strTmp为取得查询结果数量的语句
if   @strCondition   is   null   or   @strCondition = ''       -- 没有设置显示条件
begin
    
set   @sqlTmp   =    @fldName   +   '  From  '   +   @tblName
    
set   @strTmp   =   @SqlSelect + '  @Counts= ' + @SqlCounts + '  FROM  ' + @tblName
    
set   @strID   =   '  From  '   +   @tblName
end
else
begin
    
set   @sqlTmp   =   +   @fldName   +   ' From  '   +   @tblName   +   '  where (1>0)  '   +   @strCondition
    
set   @strTmp   =   @SqlSelect + '  @Counts= ' + @SqlCounts + '  FROM  ' + @tblName   +   '  where (1>0)  '   +   @strCondition
    
set   @strID   =   '  From  '   +   @tblName   +   '  where (1>0)  '   +   @strCondition
end

-- --取得查询结果总数量-----
exec  sp_executesql  @strTmp ,N ' @Counts int out  ' , @Counts  out
declare   @tmpCounts   int
if   @Counts   =   0
    
set   @tmpCounts   =   1
else
    
set   @tmpCounts   =   @Counts

    
-- 取得分页总数
     set   @pageCount = ( @tmpCounts + @pageSize - 1 ) / @pageSize

    
/**当前页大于总页数 取最后一页**/
    
if   @page > @pageCount
        
set   @page = @pageCount

    
-- /*-----数据分页2分处理-------*/
     declare   @pageIndex   int   -- 总数/页大小
     declare   @lastcount   int   -- 总数%页大小 

    
set   @pageIndex   =   @tmpCounts / @pageSize
    
set   @lastcount   =   @tmpCounts % @pageSize
    
if   @lastcount   >   0
        
set   @pageIndex   =   @pageIndex   +   1
    
else
        
set   @lastcount   =   @pagesize

    
-- //***显示分页
     if   @strCondition   is   null   or   @strCondition = ''       -- 没有设置显示条件
     begin
        
if   @pageIndex < 2   or   @page <= @pageIndex   /   2   +   @pageIndex   %   2     -- 前半部分数据处理
             begin  
                
if   @page = 1
                    
set   @strTmp = @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName                         
                        
+ '  order by  ' +   @fldSort   + '   ' +   @strFSortType
                
else
                
begin                     
                    
set   @strTmp = @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                        
+ '  where  ' + @ID + '  <(select min( ' +   @ID   + ' ) from ( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 1 as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                        
+ '  order by  ' +   @fldSort   + '   ' +   @strFSortType + ' ) AS TBMinID) '
                        
+ '  order by  ' +   @fldSort   + '   ' +   @strFSortType
                
end     
            
end
        
else
            
begin
            
set   @page   =   @pageIndex - @page + 1   -- 后半部分数据处理
                 if   @page   <=   1   -- 最后一页数据显示                
                     set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @lastcount   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                        
+ '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType  
                
else
                    
set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                        
+ '  where  ' + @ID + '  >(select max( ' +   @ID   + ' ) from( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 2 ) + @lastcount   as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                        
+ '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TBMaxID) '
                        
+ '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType  
            
end
    
end

    
else   -- 有查询条件
     begin
        
if   @pageIndex < 2   or   @page <= @pageIndex   /   2   +   @pageIndex   %   2     -- 前半部分数据处理
         begin
                
if   @page = 1
                    
set   @strTmp = @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName                         
                        
+ '  where 1=1  '   +   @strCondition   +   '  order by  ' +   @fldSort   + '   ' +   @strFSortType
                
else
                
begin                     
                    
set   @strTmp = @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                        
+ '  where  ' + @ID + '  <(select min( ' +   @ID   + ' ) from ( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 1 as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                        
+ '  where (1=1)  '   +   @strCondition   + '  order by  ' +   @fldSort   + '   ' +   @strFSortType + ' ) AS TBMinID) '
                        
+ '   ' +   @strCondition   + '  order by  ' +   @fldSort   + '   ' +   @strFSortType
                
end             
        
end
        
else
        
begin  
            
set   @page   =   @pageIndex - @page + 1   -- 后半部分数据处理
             if   @page   <=   1   -- 最后一页数据显示
                     set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @lastcount   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                        
+ '  where (1=1)  ' +   @strCondition   + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType                      
            
else
                    
set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                        
+ '  where  ' + @ID + '  >(select max( ' +   @ID   + ' ) from( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 2 ) + @lastcount   as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                        
+ '  where (1=1)  ' +   @strCondition   + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TBMaxID) '
                        
+ '   ' +   @strCondition + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType                 
        
end     
    
end

-- ----返回查询结果-----
exec  sp_executesql  @strTmp
-- print @strTmp
SET  NOCOUNT  OFF
GO
调用方法列子:
/// <summary>
    
/// 通用分页数据读取函数 
    
/// 注意:在函数调用外部打开和关闭连接,以及关闭数据读取器
    
/// </summary>
    
/// <param name="comm">SqlCommand对象</param>
    
/// <param name="_tblName">查询的表/表联合</param>
    
/// <param name="_fldName">要查询的字段名</param>
    
/// <param name="_pageSize">每页数据大小</param>
    
/// <param name="_page">当前第几页</param>
    
/// <param name="_fldSort">排序字段</param>
    
/// <param name="_Sort">排序顺序0降序1升序</param>
    
/// <param name="_strCondition">过滤条件</param>
    
/// <param name="_ID">主表主键</param>        
    
/// <param name="_dr">返回的SqlDataReader ref</param>

     public   static   void  CutPageData(SqlConnection conn,  ref  SqlCommand comm,  string  _tblName,  string  _fldName,  int  _pageSize,  int  _page,  string  _fldSort,  int  _Sort,  string  _strCondition,  string  _ID,  ref  SqlDataReader _dr)
    
{
        
//注意:在函数调用外部打开和关闭连接,以及关闭数据读取器
        
//comm = new SqlCommand("proc_ListPage",conn);
        
//comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Add("@tblName", SqlDbType.NVarChar, 200);
        comm.Parameters[
"@tblName"].Value = _tblName;
        comm.Parameters.Add(
"@fldName", SqlDbType.NVarChar, 500);
        comm.Parameters[
"@fldName"].Value = _fldName;
        comm.Parameters.Add(
"@pageSize", SqlDbType.Int);
        comm.Parameters[
"@pageSize"].Value = _pageSize;
        comm.Parameters.Add(
"@page", SqlDbType.Int);
        comm.Parameters[
"@page"].Value = _page;
        comm.Parameters.Add(
"@fldSort", SqlDbType.NVarChar, 200);
        comm.Parameters[
"@fldSort"].Value = _fldSort;
        comm.Parameters.Add(
"@Sort", SqlDbType.Bit);
        comm.Parameters[
"@Sort"].Value = _Sort;
        comm.Parameters.Add(
"@strCondition", SqlDbType.NVarChar, 1000);
        comm.Parameters[
"@strCondition"].Value = _strCondition;
        comm.Parameters.Add(
"@ID", SqlDbType.NVarChar, 150);
        comm.Parameters[
"@ID"].Value = _ID;
        comm.Parameters.Add(
"@Counts", SqlDbType.Int, 0);
        comm.Parameters[
"@Counts"].Direction = ParameterDirection.Output;
        comm.Parameters.Add(
"@pageCount", SqlDbType.Int, 0);
        comm.Parameters[
"@pageCount"].Direction = ParameterDirection.Output;

        _dr 
= comm.ExecuteReader();
    }

调用例如:
CutPageData(conn, ref comm, "VOX_CDSinger", "id, cdsinger, cdsingertype, area, cdsingerreadme", 15, page, "id", 1, strFilter, "id", ref dr);
对应说明:
CutPageData(数据连接对象, ref Sqlcommand对象, "需要表或视图名称", "要查询的字段", 每页读取数据条数, 当前页, " 排序字段可多字段如(addtime desc, visitcounts注意这里最后一个字段不加desc或asc 最后一个字段对应于后面的排序规则 )", 排序方式(1 desc 0 asc), where条件(这里不再添加where条件添加如:' and visitcounts>100'), 表主键, ref 返回的SqlDataReader对象);


 

这篇关于2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/462269

相关文章

在macOS上安装jenv管理JDK版本的详细步骤

《在macOS上安装jenv管理JDK版本的详细步骤》jEnv是一个命令行工具,正如它的官网所宣称的那样,它是来让你忘记怎么配置JAVA_HOME环境变量的神队友,:本文主要介绍在macOS上安装... 目录前言安装 jenv添加 JDK 版本到 jenv切换 JDK 版本总结前言China编程在开发 Java

linux下shell脚本启动jar包实现过程

《linux下shell脚本启动jar包实现过程》确保APP_NAME和LOG_FILE位于目录内,首次启动前需手动创建log文件夹,否则报错,此为个人经验,供参考,欢迎支持脚本之家... 目录linux下shell脚本启动jar包样例1样例2总结linux下shell脚本启动jar包样例1#!/bin

java内存泄漏排查过程及解决

《java内存泄漏排查过程及解决》公司某服务内存持续增长,疑似内存泄漏,未触发OOM,排查方法包括检查JVM配置、分析GC执行状态、导出堆内存快照并用IDEAProfiler工具定位大对象及代码... 目录内存泄漏内存问题排查1.查看JVM内存配置2.分析gc是否正常执行3.导出 dump 各种工具分析4.

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MyBatis-Plus通用中等、大量数据分批查询和处理方法

《MyBatis-Plus通用中等、大量数据分批查询和处理方法》文章介绍MyBatis-Plus分页查询处理,通过函数式接口与Lambda表达式实现通用逻辑,方法抽象但功能强大,建议扩展分批处理及流式... 目录函数式接口获取分页数据接口数据处理接口通用逻辑工具类使用方法简单查询自定义查询方法总结函数式接口

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页(deeppagination)却成为了性能优化的一个挑战,在本文中,我们将... 目录引言:深分页,真的只是“翻页慢”那么简单吗?一、背景介绍二、深分页的性能问题三、业务场景分析四、

Linux进程CPU绑定优化与实践过程

《Linux进程CPU绑定优化与实践过程》Linux支持进程绑定至特定CPU核心,通过sched_setaffinity系统调用和taskset工具实现,优化缓存效率与上下文切换,提升多核计算性能,适... 目录1. 多核处理器及并行计算概念1.1 多核处理器架构概述1.2 并行计算的含义及重要性1.3 并

Spring boot整合dubbo+zookeeper的详细过程

《Springboot整合dubbo+zookeeper的详细过程》本文讲解SpringBoot整合Dubbo与Zookeeper实现API、Provider、Consumer模式,包含依赖配置、... 目录Spring boot整合dubbo+zookeeper1.创建父工程2.父工程引入依赖3.创建ap

SpringBoot3.X 整合 MinIO 存储原生方案

《SpringBoot3.X整合MinIO存储原生方案》本文详细介绍了SpringBoot3.X整合MinIO的原生方案,从环境搭建到核心功能实现,涵盖了文件上传、下载、删除等常用操作,并补充了... 目录SpringBoot3.X整合MinIO存储原生方案:从环境搭建到实战开发一、前言:为什么选择MinI

Linux下进程的CPU配置与线程绑定过程

《Linux下进程的CPU配置与线程绑定过程》本文介绍Linux系统中基于进程和线程的CPU配置方法,通过taskset命令和pthread库调整亲和力,将进程/线程绑定到特定CPU核心以优化资源分配... 目录1 基于进程的CPU配置1.1 对CPU亲和力的配置1.2 绑定进程到指定CPU核上运行2 基于