LINQ2SQL selects orders and skips / accepts

Question

I have a function that accepts orders from a table, but not all. It skips (Page * Lines per page) and accepts (Lines per page). But there is a very big problem (looking at Linq2SqlProfiler).

• If I open 1 page (Skip = 0, Take = 50), I have a runtime: 150 ms.
• If I open 2 pages (Skip = 50, Take = 50), I have a runtime: 205 ms.
• ...
• If I open 10 pages (Skip = 450, Take = 50), I have a lead time: 1005ms .

• If I open 15 pages (Skip = 700, Take = 50), I have a execution time: 1700 ms !

  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 condition generated:

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 this really normal? Is there a way to keep the runtime about 100-200 ms?

Answer 1

, , , (150 → 200 → ... → 1005 → 1700) . , 15, , , 15. - . 15, , . , .