Linq медленность материализации сложных запросов

Я часто обнаружил, что если у меня слишком много объединений в запросе Linq (независимо от использования Entity Framework или NHibernate) и/или форма полученного анонимного класса слишком сложна, Linq занимает очень много времени, чтобы материализовать результат помещается в объекты.

Это общий вопрос, но здесь приведен конкретный пример с использованием NHibernate:

var libraryBookIdsWithShelfAndBookTagQuery = (from shelf in session.Query<Shelf>()
    join sbttref in session.Query<ShelfBookTagTypeCrossReference>() on
         shelf.ShelfId equals sbttref.ShelfId
    join bookTag in session.Query<BookTag>() on
         sbttref.BookTagTypeId equals (byte)bookTag.BookTagType
    join btbref in session.Query<BookTagBookCrossReference>() on
         bookTag.BookTagId equals btbref.BookTagId
    join book in session.Query<Book>() on
         btbref.BookId equals book.BookId
    join libraryBook in session.Query<LibraryBook>() on
         book.BookId equals libraryBook.BookId
    join library in session.Query<LibraryCredential>() on
         libraryBook.LibraryCredentialId equals library.LibraryCredentialId
    join lcsg in session
         .Query<LibraryCredentialSalesforceGroupCrossReference>()
          on library.LibraryCredentialId equals lcsg.LibraryCredentialId
    join userGroup in session.Query<UserGroup>() on
         lcsg.UserGroupOrganizationId equals userGroup.UserGroupOrganizationId
    where
         shelf.ShelfId == shelfId &&
         userGroup.UserGroupId == userGroupId &&
         !book.IsDeleted &&
         book.IsDrm != null &&
         book.BookFormatTypeId != null
    select new
    {
        Book = book,
        LibraryBook = libraryBook,
        BookTag = bookTag
    });

// add a couple of where clauses, then...
var result = libraryBookIdsWithShelfAndBookTagQuery.ToList();

Я знаю, что это не выполнение запроса, потому что я помещал сниффера в базу данных, и я вижу, что запрос принимает 0 мс, но код занимает около секунды, чтобы выполнить этот запрос и вернуть все 11 записей.

Итак, это слишком сложный запрос, имеющий 8 объединений между 9 таблицами, и я мог бы, вероятно, перестроить его на несколько небольших запросов. Или я могу превратить его в хранимую процедуру - но это поможет?

Я пытаюсь понять, где эта красная линия пересекается между исполняемым запросом и тем, что начинает бороться с материализацией? Что происходит под капотом? И поможет ли это, если бы это был SP, плоские результаты которого я впоследствии манипулировал в памяти в правильной форме?

EDIT: в ответ на запрос в комментариях, здесь вызывается SQL:

SELECT DISTINCT book4_.bookid                 AS BookId12_0_, 
                libraryboo5_.librarybookid    AS LibraryB1_35_1_, 
                booktag2_.booktagid           AS BookTagId15_2_, 
                book4_.title                  AS Title12_0_, 
                book4_.isbn                   AS ISBN12_0_, 
                book4_.publicationdate        AS Publicat4_12_0_, 
                book4_.classificationtypeid   AS Classifi5_12_0_, 
                book4_.synopsis               AS Synopsis12_0_, 
                book4_.thumbnailurl           AS Thumbnai7_12_0_, 
                book4_.retinathumbnailurl     AS RetinaTh8_12_0_, 
                book4_.totalpages             AS TotalPages12_0_, 
                book4_.lastpage               AS LastPage12_0_, 
                book4_.lastpagelocation       AS LastPag11_12_0_, 
                book4_.lexilerating           AS LexileR12_12_0_, 
                book4_.lastpageposition       AS LastPag13_12_0_, 
                book4_.hidden                 AS Hidden12_0_, 
                book4_.teacherhidden          AS Teacher15_12_0_, 
                book4_.modifieddatetime       AS Modifie16_12_0_, 
                book4_.isdeleted              AS IsDeleted12_0_, 
                book4_.importedwithlexile     AS Importe18_12_0_, 
                book4_.bookformattypeid       AS BookFor19_12_0_, 
                book4_.isdrm                  AS IsDrm12_0_, 
                book4_.lightsailready         AS LightSa21_12_0_, 
                libraryboo5_.bookid           AS BookId35_1_, 
                libraryboo5_.libraryid        AS LibraryId35_1_, 
                libraryboo5_.externalid       AS ExternalId35_1_, 
                libraryboo5_.totalcopies      AS TotalCop5_35_1_, 
                libraryboo5_.availablecopies  AS Availabl6_35_1_, 
                libraryboo5_.statuschangedate AS StatusCh7_35_1_, 
                booktag2_.booktagtypeid       AS BookTagT2_15_2_, 
                booktag2_.booktagvalue        AS BookTagV3_15_2_ 
FROM   shelf shelf0_, 
       shelfbooktagtypecrossreference shelfbookt1_, 
       booktag booktag2_, 
       booktagbookcrossreference booktagboo3_, 
       book book4_, 
       librarybook libraryboo5_, 
       library librarycre6_, 
       librarycredentialsalesforcegroupcrossreference librarycre7_, 
       usergroup usergroup8_ 
WHERE  shelfbookt1_.shelfid = shelf0_.shelfid 
       AND booktag2_.booktagtypeid = shelfbookt1_.booktagtypeid 
       AND booktagboo3_.booktagid = booktag2_.booktagid 
       AND book4_.bookid = booktagboo3_.bookid 
       AND libraryboo5_.bookid = book4_.bookid 
       AND librarycre6_.libraryid = libraryboo5_.libraryid 
       AND librarycre7_.librarycredentialid = librarycre6_.libraryid 
       AND usergroup8_.usergrouporganizationid = 
           librarycre7_.usergrouporganizationid 
       AND shelf0_.shelfid = @p0 
       AND usergroup8_.usergroupid = @p1 
       AND NOT ( book4_.isdeleted = 1 ) 
       AND ( book4_.isdrm IS NOT NULL ) 
       AND ( book4_.bookformattypeid IS NOT NULL ) 
       AND book4_.lightsailready = 1

РЕДАКТИРОВАТЬ 2: Здесь анализ производительности от ANTI Performance Profiler:

Ответы

Ответ 1

Часто база данных "хорошая" практика заключается в размещении большого количества объединений или супер общих объединений в представлениях. ORM не позволяют вам игнорировать эти факты и не дополняют десятилетия времени, потраченного на точные настройки баз данных, чтобы эффективно выполнять эти действия. Рефакторинг тех, кто присоединяется к сингулярному представлению или парам, если это будет иметь больший смысл в большей перспективе вашего приложения.

NHibernate должен оптимизировать запрос вниз и уменьшать данные, чтобы .Net только приходилось возиться с важными частями. Однако, если эти объекты домена просто естественны, это еще много данных. Кроме того, если это действительно большой результат, заданный в терминах возвращаемых строк, то многие объекты получают экземпляр, даже если БД может быстро вернуть набор. Рефакторинг этого запроса в представление, которое возвращает только те данные, которые вам действительно нужны, также уменьшит издержки на создание объектов.

Другая мысль заключалась бы в том, чтобы не делать .ToList(). Верните перечислимый и пусть ваш код лениво использует данные.

Ответ 2

Согласно профилирующей информации, CreateQuery занимает 45% от общего времени выполнения. Однако, как вы упомянули, запрос был занят 0ms при выполнении непосредственно. Но этого недостаточно, чтобы сказать, что есть проблема с производительностью, потому что

Вы выполняете запрос с профилировщиком, который оказывает значительное влияние на время выполнения.
Когда вы используете профилировщик, это повлияет на то, что каждый код будет профилироваться, но не время выполнения sql (потому что это происходит на SQL-сервере), поэтому вы можете видеть, что все остальное медленнее по сравнению с оператором SQL.

поэтому идеальным сценарием является измерение продолжительности выполнения всего блока кода, измерение времени для SQL-запроса и вычисление времени, и если вы это сделаете, вы, вероятно, закончите с разными значениями.

Однако я не говорю, что реализация NH Linq to SQL оптимизирована для любого запроса, который вы придумали, но в NHibernate есть другие способы для решения таких ситуаций, как QueryOverAPI, CriteriaQueries, HQL и, наконец, SQL.

Где эта красная линия пересекается между запросом, который является тот, который начинает бороться с материализацией. Что происходит под капотом?

Этот вопрос довольно сложный и без подробного ознакомления с провайдером NHibernate Linq to SQL трудно дать точный ответ. Вы всегда можете попробовать различные механизмы и посмотреть, какой из них лучше всего подходит для данного сценария.

И помогло бы это, если бы это был SP, чьи плоские результаты я впоследствии манипулировать в памяти в нужную форму?

Да, использование SP поможет работать очень быстро, но с использованием SP добавит больше проблем с обслуживанием на вашу базу кода.

Ответ 3

У вас есть общий вопрос, я скажу вам общий ответ:)

Если вы запрашиваете данные для чтения (а не для обновления), попробуйте использовать анонимные классы. Причина в том, что они легче создавать, у них нет навигационных свойств. И вы выбираете только нужные вам данные! Это очень важное правило. Итак, попробуйте заменить ваш select с помощью smth следующим образом:

select new { Book = new { book.Id, book.Name}, LibraryBook = new { libraryBook.Id, libraryBook.AnotherProperty}, BookTag = new { bookTag.Name} }
Сохраненные процедуры хороши, когда запрос сложный, а linq-provider генерирует неэффективный код, поэтому вы можете заменить его на простой SQL или хранимую процедуру. Это не офсетный случай и, я думаю, это не ваша ситуация.
Запустите свой SQL-запрос. Сколько строк оно возвращает? Это то же значение, что и результат? Иногда провайдер linq генерирует код, который выбирает гораздо больше строк для выбора одного объекта. Это происходит, когда объект имеет отношение один к другому с другим выбранным объектом. Например:

class Book { int Id {get;set;} string Name {get;set;} ICollection<Tag> Tags {get;set;} } class Tag { string Name {get;set;} Book Book {get;set;} } ... dbContext.Books.Where(o => o.Id == 1).Select(o=>new {Book = o, Tags = o.Tags}).Single(); I Выберите только одну книгу с Id = 1, но поставщик сгенерирует код, который возвращает количество строк, равное сумме тегов (инфраструктура сущности делает это).

Разделить сложный запрос на набор простых и присоединиться к стороне клиента. Иногда у вас сложный запрос со многими условностями, и в результате sql становится ужасным. Таким образом, вы разделяете большой запрос на более простой, получаете результаты каждого из них и присоединяетесь/фильтруете на стороне клиента.

В конце я советую вам использовать анонимный класс в качестве результата выбора.

Ответ 4

Не используйте Linqs Join. Перейдите!

в этом сообщении вы можете видеть:

Если в базе данных есть соответствующие ограничения внешнего ключа, свойства навигации будут созданы автоматически. Их также можно добавить вручную в ORM-дизайнере. Как и во всех приложениях LINQ to SQL, я считаю, что лучше всего сосредоточиться на правильном использовании базы данных и точно отразить код в структуре базы данных. При правильном определении отношений как внешних ключей код может смело делать предположения о ссылочной целостности между таблицами.

Ответ 5

Я согласен на 100% с чувствами, выражаемыми всеми остальными (в отношении их двух частей для оптимизации здесь, а выполнение SQL является большим неизвестным и, вероятно, причиной низкой производительности).

Другая часть решения, которая может помочь вам получить некоторую скорость, - это предварительная компиляция ваших операторов LINQ. Я помню, что это была огромная оптимизация на крошечном проекте (высокий трафик), который я работал много веков и веков назад... похоже, что это будет способствовать замедлению клиентской стороны, которое вы видите. Сказав все это, хотя я не нашел необходимости использовать их с тех пор... так что сначала внимайте всем остальным предупреждениям!:)

https://msdn.microsoft.com/en-us/library/vstudio/bb896297(v=vs.100).aspx