一个通用的分页存储过程实现-SqlServer（附上sql源码，一键执行即刻搭建运行环境）

使用前提

　　查询表必须有ID字段，且该字段不能重复，建议为自增主键

背景

　　如果使用ADO.NET进行开发，在查询分页数据的时候一般都是使用分页存储过程来实现的，本文提供一种通用的分页存储过程，只需要传入：

表名（以DBName.dbo.TableName）的形式
Where条件（ID > 0 AND ID < 100）
Select字段（ID,NAME,CreateDate）
Order字段（NAME ASC,CreateDate DESC）
PageSize （15）
PageIndex（2）
TotalCount，此为output参数

　　这7个参数，存储过程就能够返回指定条件下的分页数据，和数据总数。

sql源码&测试环境搭建

--创建Util库

CREATE DATABASE Util

GO

--创建通用的分页存储过程

USE Util

GO

/****** Object:  StoredProcedure [dbo].[UP_GeneralPagedQuery_v1]    Script Date: 04/03/2014 17:32:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Author:        DeanZhou

-- Create date: 2013-09-24

-- Description:    通用的分页存储过程（一）

CREATE PROCEDURE [dbo].[UP_GeneralPagedQuery_v1]

    @TableName VARCHAR(100) ,                    --表名称：如 MKT.dbo.UV_CouponInfo

    @WhereField NVARCHAR(1000) = '' ,            --筛选条件：如 Status = 1 AND CreateUser = 'admin'

    @SelectField NVARCHAR(1500) = '*' ,          --需要查询的列：如 *

    @OrderField NVARCHAR(1000) = '' ,            --需要进行排序的字段：如  CustomerName desc,StartDate asc,Status desc,id asc

    @PageSize INT = 15 ,                         --页面大小：如 15

    @PageIndex INT = 1 ,                         --当前页面：如 1

    @TotalCount INT = 0 OUT                      --记录总数：输出值

AS

    BEGIN   

        IF @OrderField IS NULL OR @OrderField = ''

            BEGIN

                SET @OrderField = ' ID '

            END

        IF @WhereField IS NULL OR @WhereField = ''

            BEGIN

                SET @WhereField = ' WHERE ID > 0 '

            END

        ELSE

            BEGIN

                SET @WhereField = ' WHERE ' + @WhereField

            END

        DECLARE @ExceptCount INT = @PageSize * ( @PageIndex - 1 )

        DECLARE @TakeCount INT = @PageSize

        DECLARE @IsNeedSubQuery INT

        DECLARE @SqlPreview NVARCHAR(MAX) =

              'SELECT @C = COUNT(1) FROM ' + @TableName + ' ' + @WhereField + ';'

            + 'IF @EC < 0 SET @EC = 0 IF @EC >= @C SET @EC = @C;'

            + 'IF @TC < 0 SET @TC = 15 IF (@EC + @TC) > @C SET @TC = @C - @EC;'

            + 'IF @EC > 0 AND @TC > 0 SET @NSQ = 1 ELSE SET @NSQ = 0;'

        EXEC sp_executesql @SqlPreview,

            N'@C INT OUTPUT,@EC INT OUTPUT,@TC INT OUTPUT,@NSQ INT OUTPUT',

            @TotalCount OUTPUT, @ExceptCount OUTPUT, @TakeCount OUTPUT, @IsNeedSubQuery OUTPUT    

        DECLARE @MaxOrMin VARCHAR(3) = 'MAX'

        DECLARE @DescOrAsc VARCHAR(4) = ''

        IF @ExceptCount > @TotalCount / 2

            BEGIN

                SET @MaxOrMin = 'MIN'

                SET @DescOrAsc = 'DESC'

                SET @ExceptCount = @TotalCount - @ExceptCount + 1

            END

        DECLARE @SqlQuery NVARCHAR(MAX) =

              ' DECLARE @T_IDS TABLE (ID INT) '

                + ' IF @NSQ = 1' +

                    ' BEGIN ' +

                    '    SELECT @MD = ' + @MaxOrMin + '(ID) FROM '+

                    '        (SELECT TOP ' + CONVERT(VARCHAR(15), @ExceptCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' ORDER BY ' + @OrderField + ')T1;' +

                    '    INSERT INTO @T_IDS '+

                    '         SELECT TOP ' + CONVERT(VARCHAR(15), @TakeCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' AND ID > @MD  ORDER BY ' + @OrderField +

                    '    SELECT ' + @SelectField + ' FROM ' + @TableName + ' S WHERE ID IN (SELECT ID FROM @T_IDS) ORDER BY ' + @OrderField +

                    ' END ' +

                  ' ELSE' +

                    ' BEGIN ' +

                    '    INSERT INTO @T_IDS SELECT ID FROM (SELECT TOP ' + CONVERT(VARCHAR(15), @TakeCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' ORDER BY  ' + @OrderField + ')T;' +

                    '    SELECT ' + @SelectField + ' FROM ' + @TableName + ' S WHERE ID IN (SELECT ID FROM @T_IDS) ORDER BY ' + @OrderField +

                    ' END '

        EXEC sp_executesql @SqlQuery, N'@MD INT,@NSQ INT', 0, @IsNeedSubQuery

    END   

    GO

--创建测试库

CREATE DATABASE Test

GO

--创建测试表

USE [Test]

CREATE TABLE TableTest (ID INT,NAME NVARCHAR(50),CreateDate DATETIME)

GO

--插入测试数据

INSERT INTO TableTest

SELECT 1,'dean1',GETDATE() UNION

SELECT 2,'dean2',GETDATE() UNION

SELECT 3,'dean3',GETDATE() UNION

SELECT 4,'dean4',GETDATE()

　　打开您的sqlserver，在本地新建查询，并运行上面的代码，会在你的数据库中创建以下内容：

一个名称为【Util】的数据库，该库下面有一个名为【UP_GeneralPagedQuery_v1】的存储过程，这个存储过程就是通用的分页存储过程。
一个名称为【Test】的数据库，该库下面有一个名为【TableTest】的表，这个表里面有4条数据

分页存储过程实现-SqlServer

请注意：在执行sql之前确认一下没有重名数据库，以免出错

测试

　　新建一个查询，执行下面sql，就完成了分页数据的获取

DECLARE    @TotalCount int

EXEC Util.[dbo].[UP_GeneralPagedQuery_v1]

        @TableName = N'Test.dbo.TableTest',

        @WhereField = N'ID > 0 AND ID < 100',

        @SelectField = N'ID,NAME,CreateDate',

        @OrderField = N'NAME ASC,CreateDate DESC',

        @PageSize = 2,

        @PageIndex = 2,

        @TotalCount = @TotalCount OUTPUT

SELECT    @TotalCount as N'@TotalCount'

分页存储过程实现-SqlServer

分类: ASP.NET, SQLSERVER

标签: sqlserver, 存储过程, sql, 通用分页

SQLServer通过链接服务器远程删除数据性能问题解决

在上一遍文章中介绍了SQLServer通过链接服务器访问Oracle性能问题的解决方法，本文介绍链接服务器下远程删除SQLServer数据的性能问题解决

1. 问题发现

系统中有个功能，需要远程删除SQLServer实例的表数据，删除语句中有where条件，条件中有一个子查询。

该功能前台执行速度非常慢。所以准备调优。

下面为演示代码，未优化前如下：

DELETE

FROM [LINKSERVERNAME].[AdventureWorks2008].[Sales].[SalesOrderDetail]

WHERE SalesOrderDetailID=5

    AND EXISTS(SELECT TOP 1 1 FROM [LINKSERVERNAME].[AdventureWorks2008].[Sales].[SalesOrderDetail])

　　此时的执行计划如下图：

分页存储过程实现-SqlServer

可以看到执行计划存在一个远程扫描，然后在本地执行筛选。

在远程服务器开启profiler跟踪，部分内容如下图：

分页存储过程实现-SqlServer

可以看到远程服务器开启了一个游标，然后逐行读取数据并返回给调用端。

可以预见性能会非常差，如何避免不带where条件的远程扫描呢？

2. 问题解决

2.1 OpenQuery

使用OpenQuery将delete数据的筛选提交到远程服务器执行。

DELETE

FROM OPENQUERY([LINKSERVERNAME]

    ,'SELECT *

FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]

WHERE SalesOrderDetailID=5

    AND EXISTS(SELECT TOP 1 1 FROM [AdventureWorks2008].[Sales].[SalesOrderDetail])'

)

此时，执行计划如图：

分页存储过程实现-SqlServer

2.2 sp_executesql

将整个delete语句提交到远程执行

DECLARE @sql nvarchar(max)

SELECT @sql ='

DELETE

FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]

WHERE SalesOrderDetailID=5

    AND EXISTS(SELECT TOP 1 1 FROM [AdventureWorks2008].[Sales].[SalesOrderDetail])

'

exec [LINKSERVERNAME].[AdventureWorks2008].dbo.sp_executesql @sql

profiler跟踪到的语句如下：

分页存储过程实现-SqlServer

如有不对的地方，欢迎拍砖；如有其他方法，求分享，谢谢！

分类: SQL Server

秒客网

分页存储过程实现-SqlServer

一个通用的分页存储过程实现-SqlServer（附上sql源码，一键执行即刻搭建运行环境）

SQLServer通过链接服务器远程删除数据性能问题解决

1. 问题发现

2. 问题解决

2.1 OpenQuery

2.2 sp_executesql

相关文章