Comparison of paging Performance Using Stored Procedures

 

Comparison of paging Performance Using Stored Procedures 

 

1. Generate table

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. Generate 1000000 Database volume

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. Stored Procedure

1.UseTop

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

/* Configure /*--------------------------------------------------------------------------------------------

[Description]

UseTopPaging

Optional --------------------------------------------------------------------------------------------*/

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

--Obtain the total number of records

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 --Obtain the total number of pages

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.UseSp_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

/* Configure /*--------------------------------------------------------------------------------------------

[Description]

UseSp_cursoropenPaging

Optional --------------------------------------------------------------------------------------------*/

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

--Initialize paging cursor

Exec sp_cursoropen

@ Cursor = @ P1 output,

@ Stmt = @ SQL,

@ Scrolopt = 1,

@ Ccopt = 1,

@ Rowcount = @ pagecount output

Set @ pageno = @ pageno + 1

--Total number of pages

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

--Display data on a specified page

Exec sp_cursorfetch @ P1, 16, @ pageno, @ pagesize

--Disable paging cursor

Exec sp_cursorclose @ p1

 

Select @ pagecount as [pagecount]

End

 

3.Use a temporary table to place the primary key in the temporary table

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

/* Configure /*--------------------------------------------------------------------------------------------

[Description]

Use temporary table Paging

Optional --------------------------------------------------------------------------------------------*/

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

--Obtain the total number of records

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 --Obtain the total number of pages

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. Performance Comparison

Execute the statement in the query Analyzer

Declare @ T1 datetime, @ T2 datetime

Set @ T1 = getdate ()

Exec up_profile_usersget1 'hxh', 'birthday desc', 678, 10

Set @ t2 = getdate ()

Select @ t2-@ T1

execution statement	time-consuming
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