• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

TOP N 和SET ROWCOUNT N 哪个更快?_sqlserver

sqlserver 搞代码 3年前 (2018-06-17) 106次浏览 已收录 0个评论

懒得翻译了,大意:
在有合适的索引的时候,Top n和set rowcount n是一样快的。但是对于一个无序堆来说,top n更快。
原理自己看英文去。

Q. Is using the TOP N clause faster than using SET ROWCOUNT N to return a specific number of rows from a query?

http://www.gaodaima.com/?p=36428TOP N 和SET ROWCOUNT N 哪个更快?_sqlserver

A. With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator uses a small internal sorted temporary table in which it replaces only the last row. If the input is nearly sorted, the TOP N engine must delete or insert the last row only a few times. Nearly sorted means you’re dealing with a heap with ordered inserts for the initial population and without many updates, deletes, forwarding pointers, and so on afterward.

A nearly sorted heap is more efficient to sort than sorting a huge table. In a test that used TOP N to sort a table with the same number of rows but with unordered inserts, TOP N was not as efficient anymore. Usually, the I/O time is the same both with an index and without; however, without an index SQL Server must do a complete table scan. Processor time and elapsed time show the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must read all the rows either way.

欢迎大家阅读《TOP N 和SET ROWCOUNT N 哪个更快?_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址