我经常发现，如果我在 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 条记录大约需要一秒钟。

是的，这是一个过于复杂的查询，在 9 个表之间有 8 个连接，我可能可以将它重组为几个更小的查询。或者我可以将它变成一个存储过程 - 但这会有帮助吗？

我想了解的是，高性能查询和开始难以实现的查询之间的红线在哪里？幕后发生了什么？如果这是一个 SP，我随后在内存中将其平坦结果操作成正确的形状，这会有帮助吗？

编辑:响应评论中的请求，这里是发出的 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:这是来自 ANTS Performance Profiler 的性能分析:

最佳答案

在 View 中放置大量连接或超常见连接通常是数据库的“好”做法。 ORM 不会让您忽略这些事实，它们也不会补充数十年来花费在微调数据库上以高效地完成这些事情的时间。如果从更大的应用程序角度来看更有意义，请将这些连接重构为单个 View 或多个 View 。

NHibernate 应该优化查询并减少数据，以便 .Net 只需处理重要部分。但是，如果这些域对象天生就很大，那仍然是大量数据。此外，如果就返回的行而言它是一个非常大的结果集，那么即使数据库能够快速返回集合，也会有很多对象被实例化。将此查询重构为仅返回您实际需要的数据的 View 也将减少对象实例化开销。

另一个想法是不执行 .ToList()。返回可枚举值并让您的代码延迟使用数据。

关于c# - Linq 缓慢具体化复杂查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31725010/