代码如下:
-
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 )
优化后,我首先利用临时表将条件查询的所有结果存储起来,然后再分页查询该临时表
代码如下:
-
SQL code
-
-- 查询结果到临时表
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
问题来了,没进行测试,不知道哪种的性能更好些?
尽管减少了三次查询的过程,但是建立临时表的过程依然需要插入、查询操作,感觉性能的确不好确定
希望各位帮忙解释一下哪种比较好?