Copy Code code as follows:
--code One declare @cc INT
SELECT Newsid,row_number () over (order by Sortnum DESC) as RowIndex into #tb from news with (NOLOCK) WHERE newstypeid= @NewsT Ypeid and Isshow=1
SET @cc = @ @ROWCOUNT
SELECT n.* from News as N with (NOLOCK) #tb as T WHERE t.rowindex> @PageIndex * @PageSize and t.rowindex<= (@PageIndex +1 ) * @PageSize and T.newsid=n.newsid
SELECT @cc
DROP TABLE #tb
Copy Code code as follows:
--code Two
DECLARE @cc INT
SELECT Newsid,row_number () over (order by Sortnum DESC) as RowIndex into #tb from news with (NOLOCK) WHERE newstypeid= @NewsT Ypeid and Isshow=1
SET @cc = @ @ROWCOUNT
SELECT NewsId into #tb2 #tb as T WHERE t.rowindex> @PageIndex * @PageSize and t.rowindex<= (@PageIndex + 1) *@ PageSize
SELECT * FROM News with (NOLOCK) WHERE NewsId in (SELECT * from #tb2)
SELECT @cc
DROP TABLE #tb
DROP TABLE #tb2
The answer is that code two is much higher than code one. The operation of bold code in code one causes an entire table scan, because the database engine stops query optimization when it considers that the where expression satisfies a condition record greater than a certain threshold, and uses table scans directly. Look at the execution information:
Table ' News '. Scan count 1, logical read 342 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(98361 rows affected)
(1 rows affected)
(40 rows affected)
Table ' #tb________________________________________00000004C024 '. Scan count 1, logical read 257 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' News '. Scan count 1, logical read 2,805 times, physical read 0 times, read 235 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(1 rows affected)
(1 rows affected)
Originally, I think of the execution plan, the bold part of the code should be focused index lookup, so performance is improved a lot. See Code two:
Table ' News '. Scan count 1, logical read 342 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(98361 rows affected)
(1 rows affected)
Table ' #tb____________________________________00000004BEEF '. Scan count 1, logical read 257 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(40 rows affected)
(1 rows affected)
(40 rows affected)
Table ' News '. Scan count 0, logical read 131 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' #tb2___________________________________00000004BEF0 '. Scan count 1, logical read 2 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(1 rows affected)
(1 rows affected)
Obviously, the number of IO operands in code two and code one is significantly reduced. and code one as @pageindex more and more, the efficiency will be lower and higher, but the efficiency of code II will not change with @pageindex changes.
