LINQ2SQL select orders and skip/take

2019-07-19 19:21发布

问题:

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).

  • If i open 1 page (Skip = 0, Take = 50) i have execution time: 150ms.
  • If i open 2 page (Skip = 50, Take = 50) i have execution time: 205ms.
  • ...
  • If i open 10 page (Skip = 450, Take = 50) i have execution time: 1005ms.
  • If i open 15 page (Skip = 700, Take = 50) i have execution time: 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;
    }
    

There is generated sql condition:

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]

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

回答1:

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.