SQL表分页PERFORMANCE ...是EF4 + Linq SKIP + TAKE在性能上与使用TSQL参数请求“寻呼”相同。在SQL表上？ [英] SQL Table Paging PERFORMANCE ...is EF4 + Linq SKIP +TAKE equal in performance than using TSQL params to request "paging" on a SQL Table?

问题描述

如果我要从MSSQL表中检索20到39或从40到59的记录。因为我正在使用MVC和EF4，所以如果我只是执行一个Linq查询和Skip（）和Take（）过程来请求分页，性能就会相同）如果你执行这个linq查询：

解决方案

/ p>

  var data = context.Posts.OrderBy（p => p.Id）.Skip（20）.Take（20） .ToList（）; 
  

它将产生此SQL：

  SELECT TOP（20）
 [Extent1]。[Id] AS [Id]，
 [Extent1]。[Text] AS [Text] 
 FROM （SELECT 
 [Extent1]。[Id] AS [Id]，
 [Extent1]。[Text] AS [Text]，
 row_number（）OVER（ORDER BY [Extent1] ID] AS [Extent1] 
 WHERE [Extent1]。[row_number]> 20 
 ORDER BY [Extent1]。[Id] ASC 
  

这不是很好您将在存储过程中编写自定义SQL，但在性能的意义上它是一样的。分页在数据库上完成。

If I want to retrieve records from 20 to 39, or from 40 to 59 from an MSSQL Table.

Since I'm using MVC and EF4, is performance the same if I just do a Linq query and Skip() and Take() procedure to request paging ....or is it better to do it on a GetList() Stored Procedure itself?

解决方案

If you execute this linq query:

var data = context.Posts.OrderBy(p => p.Id).Skip(20).Take(20).ToList();

It will produce this SQL:

SELECT TOP (20) 
  [Extent1].[Id] AS [Id], 
  [Extent1].[Text] AS [Text]
FROM ( SELECT 
         [Extent1].[Id] AS [Id], 
         [Extent1].[Text] AS [Text], 
         row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
    FROM [dbo].[Posts] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 20
ORDER BY [Extent1].[Id] ASC

It is not such nice like custom SQL you would write in your stored procedure but in the meaning of performance it is the same. Pagining is done on database.

这篇关于SQL表分页PERFORMANCE ...是EF4 + Linq SKIP + TAKE在性能上与使用TSQL参数请求“寻呼”相同。在SQL表上？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！