TOP N 和SET ROWCOUNT N 哪个更快?

讲不听不听

讲不听不听

2016-01-29 16:17

TOP N 和SET ROWCOUNT N 哪个更快?,TOP N 和SET ROWCOUNT N 哪个更快?
  懒得翻译了,大意:
在有合适的索引的时候,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?

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.


 
展开更多 50%)
分享

猜你喜欢

TOP N 和SET ROWCOUNT N 哪个更快?

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

TOP N 和SET ROWCOUNT N 哪个更快

SQLServer
TOP N 和SET ROWCOUNT N 哪个更快

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

讨论: SELECT TOP N 问题

SQLServer
讨论: SELECT TOP N 问题

讨论: SELECT TOP N问题 (2)

SQLServer
讨论: SELECT TOP N问题 (2)

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

MySQL如何实现Top N及M至N段的记录查询?

编程语言 网络编程
MySQL如何实现Top N及M至N段的记录查询?

MySQL中如何实现Top N及M至N段的记录查询?

MySQL mysql数据库
MySQL中如何实现Top N及M至N段的记录查询?

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

修复ADSL的Firmware之手记

修复ADSL的Firmware之手记

《怪物猎人3》雌火龙的小小攻略

《怪物猎人3》雌火龙的小小攻略
下拉加载更多内容 ↓