Comparison between the TOPN clause and SETROWCOUNTN _ MySQL

Q: Is the TOPN clause faster than the SETROWCOUNTN statement to return a specified number of rows from a query? A: When the index is correct, the TOPN clause and the SETROWCOUNTN statement are as fast. However, if the data is not sorted, the TOPN is faster. When the input is not sorted, use a sorted question during the top n operation: to return a specified number of rows from the query, is using a TOPN clause faster than using a set rowcountn statement?

A: When the index is correct, the top n clause and the set rowcount n statement are as fast. However, if the data is not sorted, the top n statement is faster. When the input is not sorted, the top n operation uses a small intermediate temporary table, and only replaces the last row of the table. If the input is approximately sorted, the top n engine must delete or insert the last row, but only a few operations are required. The approximate sorting means that the heap you are processing can be inserted in an orderly manner during initial construction, without many operations such as updating, deleting, and moving the pointer forward.

Sorting an approximate sorting heap is more efficient than sorting a huge table. In a test, top n is used to sort a table created by the unordered insert operation and containing the same number of rows. it is found that the top n is not efficient. Generally, the I/O time is the same when the index is performed and the index is not performed. However, if the index is not performed, the SQL Server must perform a full table scan. The processor time and actual consumption time indicate that the approximate sorting of Stacks should be more efficient. However, the I/O time is the same, because no matter how SQL Server reads all rows.