Temporary table performance problems

Source: Internet
Author: User
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?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.