【SQL】查询200行效率比较

admin

declare @d datetime
set @d=getdate()
SELECT TOP 200 * From FreeHost.FreeHost_Product_Domain where id in (SELECT TOP 200 id From FreeHost.FreeHost_Product_Domain Order By endtime ASC) Order By endtime DESC
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
//416ms，30ms(两次执行)

set @d=getdate()
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY endtime asc) AS [ROW_NUMBER], * FROM FreeHost.FreeHost_Product_Domain) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN 0 AND 200 order by endtime asc
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
//1116ms，986ms(两次执行)

很明显用了ROW_NUMBER效率低下很多，但ROW_NUMBER] BETWEEN 0 AND 200优势可以指定任意行之间的表数据获取，逻辑上更清晰。
declare @d datetime
set @d=getdate()
select * from FreeHost.FreeHost_Product_Domain where id in (SELECT TOP 190 id From FreeHost.FreeHost_Product_Domain where id in (SELECT TOP 190 id From FreeHost.FreeHost_Product_Domain Order By endtime ASC) Order By endtime DESC) order by endtime asc
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
//6,6,3
set @d=getdate()
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY endtime asc) AS [ROW_NUMBER], * FROM FreeHost.FreeHost_Product_Domain) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN 0 AND 190 order by endtime asc
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
//3,3,6

以上效率差不多，但ROW_NUMBER多了一个排序，逻辑略清晰