利用存储过程来实现分页性能比较
利用存储过程来实现分页性能比较
1.生成表
CREATE TABLE [T_User] (
[Id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Birthday] [datetime] NOT NULL CONSTRAINT [DF_User_Birthday] DEFAULT (getdate()),
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
2.生成1000000的数据库量
declare @i bigint, @n int, @name nvarchar(50)
set @i = 1000000
set @name = ''
while @i > 0
begin
set @n = 50
set @name = ''
while @n > 0
begin
set @name = @name + char(rand() * 74 + 49)
set @n = @n - 1
end
insert into t_user([name], [birthday]) values (@name, dateadd(day, rand() * 1000, getdate()))
set @i = @i - 1
end
select COUNT(*) FROM T_USER
3.存储过程
1. 使用Top
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND NAME = 'up_Profile_UsersGet1')
BEGIN
PRINT 'Dropping Procedure up_Profile_UsersGet1'
DROP PROCEDURE up_Profile_UsersGet1
END
GO
PRINT 'Creating Procedure up_Profile_UsersGet1'
GO
/*--------------------------------------------------------------------------------------------
[描述]
使用TOP分页
--------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[up_Profile_UsersGet1]
(
@Name NVARCHAR(50) = NULL,
@Sort NVARCHAR(50) = NULL,
@PageNo INT = 0,
@PageSize INT = 0
)
AS
DECLARE
@SQL NVARCHAR(4000),
@Condition NVARCHAR(4000),
@Order NVARCHAR(500),
@PageCount BIGINT
BEGIN
SET @Order = ''
IF (@Sort IS NOT NULL) AND (LTRIM(RTRIM(@Sort)) <> '')
SET @Order = ' ORDER BY ' + @Sort
SET @SQL = ' 1=1 '
IF (@Name IS NOT NULL) AND (LTRIM(RTRIM(@Name)) <> '')
SET @SQL = @SQL + ' AND Name LIKE ''%' + @Name + '%'''
SET @Condition = @SQL
-- 得到总记录数
SET @SQL = 'SELECT @PageCount = COUNT(*) FROM T_User WHERE ' + @Condition
EXECUTE SP_EXECUTESQL @SQL, N'@PageCount INT OUT', @PageCount OUT
IF @PageSize > 0
BEGIN
SET @PageCount = (@PageCount + @PageSize - 1) / @PageSize -- 得到总页数
END
ELSE
BEGIN
SET @PageCount = 0
END
IF @PageNo > @PageCount - 1
SET @PageNo = @PageCount - 1
ELSE IF @PageNo < 0
SET @PageNo = 0
--PRINT @PageNo
IF @PageNo >= 0 AND @PageSize > 0
BEGIN
SET @SQL = 'SELECT TOP ' + CONVERT(NVARCHAR(20), @PageSize) + ' * FROM T_User WHERE '
SET @SQL = @SQL + @Condition + ' AND Id NOT IN (SELECT TOP '
SET @SQL = @SQL + CONVERT(NVARCHAR(20), @PageSize * @PageNo)
SET @SQL = @SQL + ' Id FROM T_User WHERE ' + @Condition + @Order + ')'
END
ELSE
BEGIN
SET @SQL = 'SELECT * FROM T_User WHERE ' + @Condition
END
SET @SQL = @SQL + @Order
PRINT @SQL
EXEC(@SQL)
SELECT @PageCount
END
2. 使用sp_cursoropen
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND NAME = 'up_Profile_UsersGet2')
BEGIN
PRINT 'Dropping Procedure up_Profile_UsersGet2'
DROP PROCEDURE up_Profile_UsersGet2
END
GO
PRINT 'Creating Procedure up_Profile_UsersGet2'
GO
/*--------------------------------------------------------------------------------------------
[描述]
使用sp_cursoropen分页
--------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[up_Profile_UsersGet2]
(
@Name NVARCHAR(50) = NULL,
@Sort NVARCHAR(50) = NULL,
@PageNo INT = 0,
@PageSize INT = 0
)
AS
DECLARE
@SQL NVARCHAR(4000),
@p1 INT,
@Order NVARCHAR(500),
@PageCount INT
BEGIN
SET @Order = ''
IF (@Sort IS NOT NULL) AND (LTRIM(RTRIM(@Sort)) <> '')
SET @Order = ' ORDER BY ' + @Sort
SET @SQL = ' 1=1 '
IF (@Name IS NOT NULL) AND (LTRIM(RTRIM(@Name)) <> '')
SET @SQL = @SQL + ' AND Name LIKE ''%' + @Name + '%'''
SET @SQL = 'SELECT * FROM T_User WHERE' + @SQL + @Order
--初始化分页游标
EXEC sp_cursoropen
@cursor = @p1 OUTPUT,
@stmt = @SQL,
@scrollopt = 1,
@ccopt = 1,
@rowcount = @PageCount OUTPUT
SET @PageNo = @PageNo + 1
--计算总页数
IF ISNULL(@PageSize, 0) < 1
SET @PageSize = 10
SET @PageCount = (@PageCount + @PageSize - 1) / @PageSize
IF ISNULL(@PageNo, 0) < 1
SET @PageNo = 1
ELSE IF ISNULL(@PageNo, 0) > @PageCount
SET @PageNo = @PageCount
SET @PageNo = (@PageNo - 1) * @PageSize + 1
--显示指定页的数据
EXEC sp_cursorfetch @p1, 16, @PageNo, @PageSize
--关闭分页游标
EXEC sp_cursorclose @p1
SELECT @PageCount AS [PageCount]
END
3. 使用临时表，将主键放在临时表中
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND NAME = 'up_Profile_UsersGet3')
BEGIN
PRINT 'Dropping Procedure up_Profile_UsersGet3'
DROP PROCEDURE up_Profile_UsersGet3
END
GO
PRINT 'Creating Procedure up_Profile_UsersGet3'
GO
/*--------------------------------------------------------------------------------------------
[描述]
使用临时表分页
--------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[up_Profile_UsersGet3]
(
@Name NVARCHAR(50) = NULL,
@Sort NVARCHAR(50) = NULL,
@PageNo INT = 0,
@PageSize INT = 0
)
AS
DECLARE
@SQL NVARCHAR(4000),
@Condition NVARCHAR(4000),
@Order NVARCHAR(500),
@PageCount BIGINT
BEGIN
SET @Order = ''
IF (@Sort IS NOT NULL) AND (LTRIM(RTRIM(@Sort)) <> '')
SET @Order = ' ORDER BY ' + @Sort
SET @SQL = ' 1=1 '
IF (@Name IS NOT NULL) AND (LTRIM(RTRIM(@Name)) <> '')
SET @SQL = @SQL + ' AND Name LIKE ''%' + @Name + '%'''
SET @Condition = @SQL
-- 得到总记录数
SET @SQL = 'SELECT @PageCount = COUNT(*) FROM T_User WHERE ' + @Condition
EXECUTE SP_EXECUTESQL @SQL, N'@PageCount INT OUT', @PageCount OUT
IF @PageSize > 0
BEGIN
SET @PageCount = (@PageCount + @PageSize - 1) / @PageSize -- 得到总页数
END
ELSE
BEGIN
SET @PageCount = 0
END
IF @PageNo > @PageCount - 1
SET @PageNo = @PageCount - 1
ELSE IF @PageNo < 0
SET @PageNo = 0
--PRINT @PageNo
IF @PageNo >= 0 AND @PageSize > 0
BEGIN
SET @SQL = 'SELECT * FROM T_User WHERE ' + @Condition + ' AND Id > '
SET @SQL = @SQL + CONVERT(NVARCHAR(20), @PageSize * @PageNo)
SET @SQL = @SQL + ' AND Id < ' + CONVERT(NVARCHAR(20), @PageSize * (@PageNo + 1) + 1)
END
ELSE
BEGIN
SET @SQL = 'SELECT * FROM T_User WHERE ' + @Condition
END
SET @SQL = @SQL + @Order
PRINT @SQL
EXEC(@SQL)
SELECT @PageCount
END
4. 性能比较
在查询分析器中执行语句
declare @t1 datetime, @t2 datetime
set @t1 = getdate()
exec up_Profile_UsersGet1 'HXH', 'BIRTHDAY DESC', 678, 10
set @t2 = getdate()
select @t2 - @t1
|
执行语句 |
耗费时间 |
|
exec up_Profile_UsersGet1 'HXH', 'BIRTHDAY DESC', 678, 10 |
00:01:01.407 |
|
exec up_Profile_UsersGet2 'HXH', 'BIRTHDAY DESC', 678, 10 |
00:00:21.580 |
|
exec up_Profile_UsersGet1 null, 'BIRTHDAY DESC', 678, 10 |
00:00:55.453 |
|
exec up_Profile_UsersGet2 null, 'BIRTHDAY DESC', 678, 10 |
00:00:21.373 |
|
exec up_Profile_UsersGet1 null, null, 678, 10 |
00:00:03.047 |
|
exec up_Profile_UsersGet2 null, null, 678, 10 |
00:00:18.327 |
|
exec up_Profile_UsersGet1 null, null, 111678, 10 |
00:00:09.330 |
|
exec up_Profile_UsersGet2 null, null, 111678, 10 |
00:00:16.190 |
