none
Performance issues with eager loading RRS feed

  • Question


  • We have some issues with performance and Entity Framework 4.0 when using eager loading with includes.
    I have seen some posts on this issue and it seems like lots of includes are not recomended, but I think my example is very basic.

    I will show one simple example where only two tables are involved. We have a Transaction-table where all transactions are stored.
    TransactionId is PK.

    We have a Settle table which settle one or more transactions.
    MainTransId and SettledTransId are FK related to TransactionId.

    One Tansaction have 0 to many Settles.
    A settle has always one MainTransaction and one SettledTransaction.


    The linq query:

    context.Transaction.Include("Settles.SettledTransaction").Where(t => t.TransactionId == 512);


    The query generated by EF:

    SELECT
    [Project1].[TransactionId] AS [TransactionId],
    [Project1].[SettleId] AS [SettleId],
    [Project1].[MainTransactionId] AS [MainTransactionId],
    [Project1].[SettledTransactionId] AS [SettledTransactionId]
    FROM
    (
     SELECT
        [Extent1].[TransactionId] AS [TransactionId],
        [Join1].[SettleId] AS [SettleId],
        [Join1].[MainTransactionId] AS [MainTransactionId],
        [Join1].[SettledTransactionId] AS [SettledTransactionId],

        CASE WHEN ([Join1].[SettleId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Transaction] AS [Extent1]
        LEFT OUTER JOIN 
        (
        SELECT [Extent2].[SettleId] AS [SettleId], [Extent2].[MainTransactionId] AS [MainTransactionId], [Extent2].[SettledTransactionId] AS[SettledTransactionId], [Extent3].[TransactionId] AS [TransactionId]
            FROM  [dbo].[Settle] AS [Extent2]
            INNER JOIN [dbo].[Transaction] AS [Extent3] ON [Extent2].[SettledTransactionId] = [Extent3].[TransactionId]
        )
            AS [Join1] ON [Extent1].[TransactionId] = [Join1].[MainTransactionId]
        WHERE 512 = [Extent1].[TransactionId]
    )  AS [Project1]
    ORDER BY [Project1].[TransactionId] ASC, [Project1].[C1] ASC


    Why is EF making nested selects like

    SELECT [Extent2].[SettleId] AS [SettleId], [Extent2].[MainTransactionId] AS [MainTransactionId], [Extent2].[SettledTransactionId] AS[SettledTransactionId], [Extent3].[TransactionId] AS [TransactionId]
            FROM  [dbo].[Settle] AS [Extent2]"
            INNER JOIN [dbo].[Transaction] AS [Extent3] ON [Extent2].[SettledTransactionId] = [Extent3].[TransactionId]

    instead of using joins all the way?

    This inner query gets all settle-rows in the db. These tables will have millions of rows in production.
    I will asume this is not very fast when we have millions of rows?

    Is there any way we can control how EF is joining tables?


    Tuesday, September 27, 2011 9:23 AM

Answers

All replies