sqlserver2005 use temporary tables and @ @RowCount improve paging query stored procedure performance sample sharing _mssql2005

The recent discovery of the performance of common query stored procedures for existing frameworks is slow, and the following code is carefully studied:

Copy Code code as follows:

Alter PROCEDURE [dbo]. [Areaselect]
@PageSize int=0,
@CurrentPage Int=1,
@Identifier Int=null,
@ParentId Int=null,
@AreaLevel Int=null,
@Children Int=null,
@AreaName nvarchar (m) =null,
@Path nvarchar (MAX) =null,
@Status Int=null,
@Alt Int=null
As
BEGIN
SET NOCOUNT on;
IF (not @AreaName is NULL) SET @AreaName = '% ' + @AreaName + '% '
IF (not @Path is NULL) SET @Path = '% ' + @Path + '% '
IF (@PageSize >0)
BEGIN
DECLARE @TotalPage int
Select @TotalPage =count (Identifier) from the area Where
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
IF (@TotalPage% @PageSize =0)
BEGIN
SET @TotalPage = @TotalPage/@PageSize
End
ELSE
BEGIN
SET @TotalPage =round (@TotalPage/@PageSize, 0) +1
End
Select Top (@PageSize) Identifier,parentid,arealevel,children,areaname,path,status,alt, @TotalPage as Totalpage from Area Where
Identifier not IN (Select Top (@PageSize * (@CurrentPage-1)) Identifier from area Where
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
ORDER BY areaname ASC)
and
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
ORDER BY areaname ASC
End
ELSE
BEGIN
Select Identifier,parentid,arealevel,children,areaname,path,status,alt from the area Where
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
ORDER BY areaname ASC
End
End

found that each query needs to query the area table by condition, the performance is too low, so the temporary table will be eligible to take out the records, and then for the temporary table query, the code modified as follows:
Alter PROCEDURE [dbo]. [Areaselect]
@PageSize int=0,
@CurrentPage Int=1,
@Identifier Int=null,
@ParentId Int=null,
@AreaLevel Int=null,
@Children Int=null,
@AreaName nvarchar (m) =null,
@Path nvarchar (MAX) =null,
@Status Int=null,
@Alt Int=null
As
BEGIN
SET NOCOUNT on;
IF (not @AreaName is NULL) SET @AreaName = '% ' + @AreaName + '% '
IF (not @Path is NULL) SET @Path = '% ' + @Path + '% '


IF (@PageSize >0)
BEGIN
--Create a temporary table
Select
Identifier,parentid,arealevel,children,areaname,path,status,alt
Into #temp_Area
From Area Where
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
ORDER BY areaname ASC

DECLARE @TotalPage int
DECLARE @SumCount int

--Total
Select @SumCount =count (Identifier) from #temp_Area

IF (@SumCount% @PageSize =0)
BEGIN
SET @TotalPage = @SumCount/@PageSize
End
ELSE
BEGIN
SET @TotalPage =round (@SumCount/@PageSize, 0) +1
End
Select Top (@PageSize) Identifier,parentid,arealevel,children,areaname,
Path,status,alt, @TotalPage as Totalpage, @SumCount as Sumcount
From #temp_Area
Where
Identifier not IN (Select Top (@PageSize * (@CurrentPage-1)) Identifier from #temp_Area)
End
ELSE
BEGIN
Select Identifier,parentid,arealevel,children,areaname,path,status,alt from the area Where
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
ORDER BY areaname ASC
End
End

The use of temporary tables does improve performance, but there is a problem that count (Identifier) is very performance-intensive, and then it is modified
：

Alter PROCEDURE [dbo]. [Areaselect]
@PageSize int=0,
@CurrentPage Int=1,
@Identifier Int=null,
@ParentId Int=null,
@AreaLevel Int=null,
@Children Int=null,
@AreaName nvarchar (m) =null,
@Path nvarchar (MAX) =null,
@Status Int=null,
@Alt Int=null
As
BEGIN
SET NOCOUNT on;
IF (not @AreaName is NULL) SET @AreaName = '% ' + @AreaName + '% '
IF (not @Path is NULL) SET @Path = '% ' + @Path + '% '


IF (@PageSize >0)
BEGIN
--Number of records created
DECLARE @SumCount int

--Create a temporary table
Select
Identifier,parentid,arealevel,children,areaname,path,status,alt
Into #temp_Area
From Area Where
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
ORDER BY areaname ASC
--Set the total number of records to be just the number of record operations
SET @SumCount =@ @RowCount

DECLARE @TotalPage int

IF (@SumCount% @PageSize =0)
BEGIN
SET @TotalPage = @SumCount/@PageSize
End
ELSE
BEGIN
SET @TotalPage =round (@SumCount/@PageSize, 0) +1
End
Select Top (@PageSize) Identifier,parentid,arealevel,children,areaname,
Path,status,alt, @TotalPage as Totalpage, @SumCount as Sumcount
From #temp_Area
Where
Identifier not IN (Select Top (@PageSize * (@CurrentPage-1)) Identifier from #temp_Area)
End
ELSE
BEGIN

Select Identifier,parentid,arealevel,children,areaname,path,status,alt from the area Where
(@Identifier is NULL or identifier= @Identifier) and
(@ParentId is NULL or parentid= @ParentId) and
(@AreaLevel is NULL or arealevel= @AreaLevel) and
(@Children is NULL or children= @Children) and
(@AreaName is NULL or areaname like @AreaName) and
(@Path is NULL or Path like @Path) and
(@Status is NULL or status= @Status) and
(@Alt is NULL or alt= @Alt)
ORDER BY areaname ASC
End
End