LINQ2SQL选择订单，并跳过/取 [英] LINQ2SQL select orders and skip/take

问题描述

我有功能，从表中获取订单，但不是全部。这是跳过（页页*行），并采取（页面行）。但是有非常大的问题（在Linq2SqlProfiler看）。

I have function that takes orders from table, but not all. It's skip (Page * Rows on page) and take (Rows on page). But there is very big problem (looking in Linq2SqlProfiler).

• 如果我打开第1页（跳过= 0，取= 50）我有执行时间：150毫秒


• 如果我打开第2页（跳过= 50，取= 50）我有执行时间：205ms


• ...


• 如果我打开第10页（跳跃= 450，取= 50）我有执行时间：
  1005ms 。



• 如果我打开第15页（跳跃= 700，取= 50）我有执行
  时间： 1700ms

public IEnumerable<Order> GetAllConfirmedOrders(DateTime firstDay, int? ProviderId = null, Guid? ManagerId = null, DateTime? date1 = null, DateTime? date2 = null, int iSkip = 0, int iTake = 50)
{
    var predicate_order = PredicateBuilder.True<Order>();
    var predicate_orderlist = PredicateBuilder.True<OrderList>();

    if (ProviderId != null) predicate_orderlist = predicate_orderlist.And<OrderList>(ol => ol.ProviderAn == ProviderId);
    if (ManagerId != null) predicate_order = predicate_order.And<Order>(o => o.UserId == ManagerId);
    if (date1 != null && date2 != null) predicate_order = predicate_order.And<Order>(o => o.DateAdd >= date1 && o.DateAdd <= date2);
    else predicate_order = predicate_order.And<Order>(o => o.DateAdd >= firstDay);

    var orders = (from o in _dataContext.Orders
                  join ol in _dataContext.OrderLists.Where(predicate_orderlist) on o.Analit equals ol.OrderAn
                  where o.Status == 1
                  orderby o.DateAdd descending
                  select o).Where(predicate_order).Skip(iSkip).Take(iTake);

    return orders;
}

有生成SQL条件：

WHERE  [t2].[ROW_NUMBER] BETWEEN 50 /* @p2 */ + 1 AND 50 /* @p2 */ + 50 /* @p3 */
ORDER  BY [t2].[ROW_NUMBER]

WHERE  [t2].[ROW_NUMBER] BETWEEN 450 /* @p2 */ + 1 AND 450 /* @p2 */ + 50 /* @p3 */
ORDER  BY [t2].[ROW_NUMBER]

WHERE  [t2].[ROW_NUMBER] BETWEEN 700 /* @p2 */ + 1 AND 700 /* @p2 */ + 50 /* @p3 */
ORDER  BY [t2].[ROW_NUMBER]

是不是真的正常吗？有没有办法救近100-200ms执行时间？

Is it really normal? Is there a way to save execution time near 100-200ms?

推荐答案

在看样品执行时间，您提供后，它看起来像时间线性增加（150 - > 200 - > - > 1005 - > 1700）为你开启更多的页数。因此，通过的时候，你到达15页，查询必须处理更多数量的记录，以获取相应的第15页。我的猜测是它不是问题，第15页但总体查询本身需要时间作为数数据记录的增加。您可以尝试通过调整表的索引优化查询。

After looking at the sample execution time, that you have provided, it looks like the time is increasing linearly (150 -> 200 -> ... -> 1005 -> 1700) as you open more number of pages. So by the time, you reach page15, the query has to process more number of records, to fetch the data corresponding to page 15. My guess is its not the problem with page 15th but overall the query itself is taking time as the number of records increases. You can try to optimize the query by tuning the indexes of the tables.

这篇关于LINQ2SQL选择订单，并跳过/取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！