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?