In the paging part of my stored procedure, I used three queries with the same conditions to obtain the final data.
The Code is as follows:
-
SQL code
-
DECLARE @TotalPage int
SELECT @TotalPage=Count(Identifier) FROM View_BbsInfo WHERE
(@Identifier IS NULL OR Identifier=@Identifier)AND
(@IsTop IS NULL OR IsTop=@IsTop)AND
(@IsPlacard IS NULL OR IsPlacard=@IsPlacard)
IF(@TotalPage%@PageSize=0)
BEGIN
SET @TotalPage=@TotalPage/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@TotalPage/@PageSize,0)+1
END
SELECT TOP (@PageSize) Identifier,IsTop,IsPlacard,@TotalPage as totalPage FROM View_BbsInfo WHERE
Identifier NOT IN (SELECT Top (@PageSize*(@CurrentPage-1))Identifier FROM View_BbsInfo WHERE
(@Identifier IS NULL OR Identifier=@Identifier)AND
(@IsTop IS NULL OR IsTop=@IsTop)AND
(@IsPlacard IS NULL OR IsPlacard=@IsPlacard))
AND
(@Identifier IS NULL OR Identifier=@Identifier)AND
(@IsTop IS NULL OR IsTop=@IsTop)AND
(@IsPlacard IS NULL OR IsPlacard=@IsPlacard)
After optimization, I first store all the results of the conditional query using a temporary table, and then query the temporary table by page.
The Code is as follows:
-
SQL code
-
-- Query Result to temporary table
Select top (@ PageSize) Identifier, IsTop, IsPlacard INTO # tempTable FROM View_BbsInfo WHERE
(@ Identifier is null or Identifier = @ Identifier) AND
(@ IsTop is null or IsTop = @ IsTop) AND
(@ IsPlacard is null or IsPlacard = @ IsPlacard)
DECLARE @ TotalPage int
SELECT @ TotalPage = Count (Identifier) FROM # tempTable
IF (@ TotalPage % @ PageSize = 0)
BEGIN
SET @ TotalPage = @ TotalPage/@ PageSize
END
ELSE
BEGIN
SET @ TotalPage = Round (@ TotalPage/@ PageSize, 0) + 1
END
Select top (@ PageSize) Identifier, IsTop, IsPlacard, @ TotalPage as totalPage FROM # tempTable WHERE
Identifier not in (SELECT Top (@ PageSize * (@ CurrentPage-1) Identifier FROM # tempTable
The problem is that I didn't perform the test. I don't know which kind of performance is better?
Although the process of three queries is reduced, the process of creating a temporary table still needs to be inserted and queried, and the performance is indeed poor.
I hope you can explain which one is better?