Дисклеймер: Под Linq to Database здесь и далее понимаем самые распространенные .NET библиотеки для доступа к реляционным СУБД с использованием Linq запросов. Такие как Linq2SQL, Entity Framework, NHibername, Linq2db и подобные
С момента появления IQueryable провайдеров для доступа к базам данных писать запросы стало гораздо легче. С одной стороны это хорошо, потому что приложения стало делать гораздо легче. С другой стороны качество запросов сильно упало. И дело не в том, что провайдеры генерируют плохой SQL, а в том, что люди пишут запросы, не понимая как оно работает под капотом. Для многих C# программистов IQueryable является магией, и появляется естественное желание уменьшить влияние магии на приложения, изолировать магию от остального кода. Такой подход не только не исправляет ситуацию, а усугубляет её…
Пример кода
В одном из недавних холиваров один из участников опубликовал потрясающий пример кода:
public IEnumerable<DocumentDto> GetDocumentsToday()
{
foreach (var item in Db.Documents.Where(x => EntityFunctions.TruncateTime(x.CreatedOn) == DateTime.Today))
{
yield return Mapper.Map<Document, DocumentDto>(item);
}
}
Предполагалось что этот код сможет изолировать «сложность» IQueryable провайдеров от потребителя. Но именно этот, с виду простой, код содержит 3 плохие вещи при работе с Linq to Database.
Итак хит-парад:
Третье место
Долгая обработка результатов запроса. Большинство провайдеров удерживают открытым DataReader пока идет перечисление IQueryable. Это приводит к тому, что все ресурсы, выделенные сервером для этого запроса, удерживаются до окончания обхода и закрытия DataReader. В данном примере еще используется yield return, то есть обработка каждого элемента может идти очень долго.
В случае использования SQL Server (или другой блокировочной БД) при уровне изоляции Repeatable Read блокировки будут также держаться до конца обработки результатов запроса, а если будет эскалация, то и на всю таблицу.
Как исправить
Всегда явно выполнять материализацию результатов, например с помощью .Load() или .ToList() или .ToArray(). Если же результаты запроса очень большие, а надо втянуть все, то лучше всего организовать пейджинг блоками, которые не вызовут эскалацию блокировок.
Второе место
Неэффективный предикат. В данном примере предикат преобразуется в SQL в нечто вроде такого в зависимости от провайдера:
...
WHERE cast(CreatedOn as Date) = cast(GETDATE() as Date)
Естественно ни одни индекс не сможет оптимизировать такую выборку и будет полный обход таблицы.
Даже если в этом конкретном запросе такой предикат не будет проблемой, то код имеет тенденцию быть скопированным в другие места вместе со всеми проблемами.
Как исправить
Не использовать функции и преобразования типов для значений колонок в предикатах и джоинах. Этот пример можно переписать так:
Db.Documents.Where(x => x.CreatedOn >= DateTime.Today && x.CreatedOn < DateTime.Today.AddDays(1))
Теперь можно будет сделать индекс по полю CreatedOn.
Первое место
Отсутствие проекции. Казалось бы банальная вещь — запрос тянет все поля таблицы, а потом мапит на DTO, который может иметь гораздо меньше полей… Если в таблице есть LOB поля, то это может дать очень негативно повлиять на скорость выполнения запроса. В некоторых ORM можно настроить отложенную загрузку LOB полей, но даже в этом случае наличие проекции эффективнее. При наличии проекции в запросе можно построить покрывающий индекс, то есть индекс, включающий в себя все колонки в проекции. То есть проекции позволяют не только сделать запрос быстрее, но и дать возможность дальнейшей оптимизации на уровне базы данных.
Как исправить
Всегда делать проекции только необходимых для полей, кроме случаев когда нужно получить объект, а потом изменить его и записать в базу данных.
Исправленный пример
public IEnumerable<DocumentDto> GetDocumentsToday()
{
return Db.Documents
.Where(x => x.CreatedOn >= DateTime.Today && x.CreatedOn < DateTime.Today.AddDays(1))
.Select (x => new DocumentDto {...})
.Load();
}
Можно также поправить класс Mapper, чтобы он генерировал проекции.
Заключение
В этом хит-параде собраны три самые частые проблемы, которые возникают при использовании Linq. Я оптимизировал много приложений и почти всегда находил эти проблемы.
Что касается конкретного примера, то скорее всего во многих случаях не будут использоваться все поля DocumentDto, а только часть из них. Также будут отображаться не все записи, а с разбиением по страницам. Поэтому выгодно из метода GetDocumentsToday отдавать IQueryable, и применять фильтры и ограничения ближе к месту использования результатов запроса. Но об этом в следующий раз.
Автор: gandjustas
