Question

我的职能是从桌上订购,但并非全部。它的ski(Page* Rows on page)和带(Rows on page)。但存在非常大的问题(见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;
}

产生了q状:

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-200米的执行时间?

Answer 1

在检查你提供的样本执行时间之后,随着你打开更多的页数,它看上去的是线性增加(150->200->......->1005-> 1700)。因此,到那时,你到达第15页,询问必须处理更多的记录,以收集与第15页对应的数据。我的猜测不是第15页的问题,而是整个问题,随着记录数量的增加,问题本身的时间正在增加。你可以尝试通过调整表格的索引来优化询问。

友情链接