Оптимизация высоконагруженных ASP.NET приложений, работающих с MS SQL Server с помощью LINQ

добавлено: 15 июл 16
понравилось:0
просмотров: 2238
комментов: 2

теги:

Автор: gandjustas

Доклад с таким длинным и непонятным названием я читал на SQL Server User Group 10 сентября в Москве. Ниже слайды запись доклада:

К сожалению, как обычно я не показал все что хотел, часть материала не попала на видео запись. Но я восполню этот недостаток.
Как вы думаете, можно ли на Linq делать запросы, которые работают быстрее рукопашных? Оказывается да, и очень просто.
Например надо сделать функцию, которая отбирает заказы по дате отгрузки. Если параметр указал, то выбрать заказы за эту дату. А если не указана дата, то выбрать все заказы, у которых дата отгрузки пустая. Обычный разработчик напишет такую процедуру:

CREATE PROCEDURE [dbo].[GetTransactionsByShipDate]
    @shipDate datetime
AS
    SELECT t.Id, t.ProductId, t.TransactionDate 
       from Transactions t
    where
        (@shipDate is not null 
          and t.ShippedDate = @shipDate) 
     or (@shipDate is null 
          and t.ShippedDate is null)

Эта процедура подвержена parameter sniffing problem. Проблема заключается в том, что план процедуры генерируется один раз при первом вызове с учетом фактических параметров при вызове. Если при первом вызове ShipDate был NULL (низкая селективность), то сгенерируется план с Index Scan. Если же первый вызов был с конкретным значением даты, то получится Index Seek, который будет неэффективно работать для значений с низкой селективностью.
Простой тест:
DBCC FREEPROCCACHE
GO

EXEC    [dbo].[GetTransactionsByShipDate] NULL
GO

declare @shipdate datetime = getdate()
EXEC    [dbo].[GetTransactionsByShipDate] @shipdate
GO

DBCC FREEPROCCACHE
GO

declare @shipdate datetime = getdate()
EXEC    [dbo].[GetTransactionsByShipDate] @shipdate
GO

EXEC    [dbo].[GetTransactionsByShipDate] NULL
GO

Результаты:
image
Как видите план оказывается далеко не оптимальным в обоих случаях.
А с помощью Linq можно написать так:
private static IQueryable<Transaction> GetValues(
    IQueryable<Transaction> query, 
    DateTime? dateTime)
{
    if (dateTime.HasValue)
    {
        return query.Where(t => t.ShippedDate == dateTime.Value);
    }
    else
    {
        return query.Where(t => t.ShippedDate == null);
    }
}
Тогда буду сгенерированы два разных запроса, каждый со своим, оптимальным для данного запроса, планом.
Более того, такая оптимизация для null value встроена в провайдер Linq2DB. Там можно непосредственно nullable значения подставлять в linq.
Более того, можно использовать filtered index, когда больше 2% значений по индексируемому полю равны NULL.

Комментарии


  • Наверное для Вас будет открытием, но в T-SQL тоже есть IF и обычный разработчик напишет:

    CREATE PROCEDURE [dbo].[GetTransactionsByShipDate]
    @shipDate datetime
    AS
    if (@shipDate is null)
    SELECT t.Id, t.ProductId, t.TransactionDate
    from Transactions t
    where t.ShippedDate is null)
    else
    SELECT t.Id, t.ProductId, t.TransactionDate
    from Transactions t
    where t.ShippedDate = @shipDate)

  • Для вас наверное будет открытием (с), но проблемы в случае с IF ровно такие же.

    Построение плана запроса учитывает фактические параметры на момент первого вызова процедуры. Поэтому IF нормально работает только с OPTION(RECOMPILE), который заставляет перекомпилировать план при каждом вызове. Только перекомпиляция плана съест больше времени, чем if в коде программы.



Необходимо войти на сайт, чтобы оставлять комментарии