Common MS SQL paging stored procedures

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

Before writing a paging stored procedure, we first create a test table for the database, which shows that there are 3 fields, called Order, which are or_id,orname,datesta;

CREATE TABLE [dbo]. [Orders] (
    [or_id] [int] IDENTITY (1,1) not NULL,
    [orname] [nvarchar] not null,     [datesta] [datetime] Not NULL,
 constraint [pk_orders] PRIMARY KEY CLUSTERED
(
&nbs p;   [or_id] ASC
) with (pad_index  = off, statistics_norecompute  = off, Ignore_dup_key = off, all ow_row_locks  = on, allow_page_locks  = on) on [PRIMARY]
) on [PRIMARY]
Go
EXEC SYS.SP_ADDEXTENDEDPR Operty @name =n ' ms_description ', @value =n ' Write Time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @ Level1name=n ' Orders ', @level2type =n ' COLUMN ', @level2name =n ' Datesta '
go
SET identity_insert [dbo].[ Orders] on
INSERT [dbo].[ Orders] ([or_id], [ORName], [Datesta]) VALUES (1, N ' naoqiu.com ', CAST (0x0000a03700fef029 as DateTime))
INSERT [dbo].[ Orders] ([or_id], [ORName], [Datesta]) VALUES (2, N ' naoqiu.com ', CAST (0x0000a03700ff5be8 as DateTime))
INSERT[dbo]. [Orders] ([or_id], [ORName], [Datesta]) VALUES (3, N ' deposit ', CAST (0x0000a03700ff5bf6 as DateTime))
INSERT [dbo].[ Orders] ([or_id], [ORName], [Datesta]) VALUES (4, N ' cost ', CAST (0x0000a03700ff5bf6 as DateTime))
INSERT [dbo].[ Orders] ([or_id], [ORName], [Datesta]) VALUES (5, N ' http://naoqiu.com ', CAST (0x0000a03700ff5bf6 as DateTime))
SET IDE Ntity_insert [dbo]. [Orders] Off
/****** object:  Default [df_orders_datesta]    script date:04/18/2012 15:31:11 ******/
ALTER TABLE [dbo]. [Orders] add  CONSTRAINT [df_orders_datesta]  DEFAULT (getdate ()) for [Datesta]
Go

       Now let's look at the traditional paging stored procedures on SQL 2005:

Create Procedure [dbo]. [Prpagelist]
(
     @PageSize int=20,/* rows per page */
     @PageIndex int=1,/* pages */   & nbsp;
     @Field nvarchar (2000),/* Query field */
     @QueryString nvarchar (3000),/* Query statement */
     @Orderby nvarchar (1000)/* Sort field */
)
as
Set NoCount on
  Declare @list_id1 int,@ List_id2 int
  DECLARE @SqlQuery nvarchar (4000)
   Set @list_id2 = (@PageSize * @PageIndex)-- Current page record end
   Set @list_id1 = (@list_id2-@PageSize)--current page record start
 
  Set @SqlQuery =n ' Select * From (Select row_number () over
      + @Orderby
       +n ') as list_id, '
      + @Field
      + '
 & nbsp;    + @QueryString
      +n ') as Where a.list_id> '
       +cAST (@list_id1 as Nvarchar)
      +n ' and a.list_id<= '
       +cast (@list_id2 as Nvarchar)        
       Print @SqlQuery
    execute sp_executesql @SqlQuery
    
     Set @SqlQuery = ' Select count (*) ' + @QueryString
    execute sp_executesql @SqlQuery
  & nbsp; return

Call Example: Exec prpagelist 10,100, ' OrderID ', ' from Orders ', ' Datesta desc '

This writing has the following advantages:

Common Paging stored procedure Code concise, easy to understand

The call is simpler and has fewer parameters.

Disadvantage: The string exceeds 4000 the stored procedure executes abnormally, needs to perform two retrieval tables, spends resources, and when you select the second page, you need to perform two times, one for the list and one for the total number of statistics.

Now in the same situation in order to improve performance, improve statement execution efficiency. There is a need to adjust the logical thinking of the paging stored procedure above:

In the total number of statistics page totals, when the total number of excessive pages has been counted, you do not need to count the total number of paging.

In our first statistic page totals, we need to count the total page totals, and now we use temporary tables to insert qualifying record IDs into temporary tables for easy retrieval and statistics. So we avoid two scans of the entire table.

In the case of Multiple Table association filter data, the criteria we filter may be only in a few tables, and the results may need to be associated with more tables, then we can make the filter inserted into the temporary table with the conditions associated with the display list. To permit compatibility is stronger.

During the paging process, intermediate temporary tables may be generated to manage other tables.

And then we can use the temporary table directly to remove the freed resources, instead of waiting for SQL to help me with garbage collection.

With more ideas, now I'm going to post the code directly:

--Integrated common paging stored Procedures
Create procedure [dbo].[ Test_common_pagelist_temtablebyrelation]
@pageIndex int,--The current page index, the first page is: 1
@pageSize int,--Display the number of bars per page
@count int Total,--0
@id varchar,--primary key ID or associated field
@sortparam varchar,--sort
@selectParam nvarchar,--query field
@condition nvarchar,--query criteria
@temTable nvarchar (3000),--temporary table string
@dropTable varchar (s),--Delete temporary table strings
@relation varchar,--Association table
@insrtParam varchar,--inserted into the field of the statistic Total temporary table
@param varchar (100)-- Temporary table fields inserted into the page display
as
declare @sql nvarchar (4000), @min int, @max int, @date varchar, @tem1 varchar, @tem2 varchar (a), @part2 nvarchar (4000)
Set @date =replace (CONVERT (Varchar,getdate (), 114), ': ', ')
Set @tem1 = ' #page1 ' +@ Date
Set @tem2 = ' #page2 ' + @date
Set @min = (@pageIndex-1) * @pageSize +1
Set @max = @pageIndex * @pageSize
SET @ Sql= '
Select ' + @id + ' as Spkid ' + @insrtParam + ', row_number () over (order by ' + @sortparam + ") as RowNumber into ' + @tem1 + ' F Rom ' + @condition + '
Select Spkid,rOwnumber ' + @param + ' into ' + @tem2 + ' from ' + @tem1 + ' where RowNumber inclusive ' +cast (@min as varchar) + ' and ' +
CAST (@ma x as varchar
if @count =0 begin
    set @part2 = ' Declare @count int
      ;   set @count = (select Max (RowNumber) from ' + @tem1 + ')
        if @count is Nu ll set @count =0
        select  ' + @selectParam + ', @count as Num '
End ELSE begin
    set @part2 = ' select  ' + @selectParam + ', ' +cast (@count as varchar) + ' as Num '
End
Set @part2 = @part2 + ', RowNumber from ' + @tem2 + ' as PT join ' + @relation + ' ORDER by Pt. RowNumber
Truncate TABLE ' + @tem1 + '
truncate TABLE ' + @tem2 + '
drop table ' + @tem1 + '
drop table ' + @tem2 + '
' + @dropTable
--Close the number of rows affected
Exec (' Set NOCOUNT on
' + @temTable + @sql + @part2 + '
set NOCOUNT OFF)
--open affect rows
Go

Invoke Method Example:

SET STATISTICS io on-view disk IO
Set STATISTICS TIME on-View SQL statement parse compilation and execution times
DECLARE @count int
Set @count =0
exec [dbo]. [Erp_common_pagelist_temtablebyrelation] 1,50, @count, ' or_id ', ' Datesta desc ',
' * ', ' orders ', ', ', ' orders on spkid=or_id ', ', ', '

Summary: In the process of reading the data as much as possible to reduce the number of table logic read, as well as the number of scans, to reduce IO output is to improve performance assurance.