Copy codeThe Code is as follows: -- code 1 DECLARE @ cc INT
SELECT NewsId, ROW_NUMBER () OVER (order by SortNum DESC) AS RowIndex INTO # tb FROM news WITH (NOLOCK) WHERE NewsTypeId = @ NewsTypeId 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 codeThe Code is as follows: -- code 2
DECLARE @ cc INT
SELECT NewsId, ROW_NUMBER () OVER (order by SortNum DESC) AS RowIndex INTO # tb FROM news WITH (NOLOCK) WHERE NewsTypeId = @ NewsTypeId AND IsShow = 1
SET @ cc = @ ROWCOUNT
SELECT NewsId INTO # tb2 FROM # 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 2 is much higher than Code 1. In code 1, the bold code operation will cause a full table scan, because when the database engine considers that the WHERE expression meets the condition record is greater than a certain threshold value, it will not perform query optimization, directly use table scanning. View execution information ,:
Table 'News '. Scan count 1, logical reads 342, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
(Row 3 is affected)
(One row is affected)
(40 rows affected)
Table '# tb ________________________________________ 20170004c024 '. Scan count 1, logical reads 257, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
Table 'News '. 1 scan count, 2805 logical reads, 0 physical reads, 235 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads.
(One row is affected)
(One row is affected)
Originally, the code in the bold part of my execution plan should focus on index search, which improves the performance a lot. See Code 2:
Table 'News '. Scan count 1, logical reads 342, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
(Row 3 is affected)
(One row is affected)
Table '# tb ________________________________ 00000004BEEF '. Scan count 1, logical reads 257, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
(40 rows affected)
(One row is affected)
(40 rows affected)
Table 'News '. Scan count 0, logical read 131, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.
Table '# tb2 _______________________________ 00000004BEF0 '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
(One row is affected)
(One row is affected)
Obviously, the IO operations in Code 2 and code 1 are greatly reduced. In addition, code 1 increases with @ PageIndex and the efficiency will decrease. However, the efficiency of Code 2 will not change with @ PageIndex.