First, we will compare two paging SQL statements. For example, the news table has 0.15 million records, NewsTypeId10 has 90 thousand records, and NewsTypeID10 is currently queried. Which SQL statement will be considered as highly efficient?
First, compare the two paging SQL statements. Assume that the news table has 0.15 million records, NewsTypeId = 10 has 90 thousand records, and the current query NewsTypeID = 10. Which SQL statement will be considered as highly efficient?
The 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
The 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.
