Entity Framework: What would cause slow queries after adding a table?

Question

I have an entity framework 5 project hooked up to a SQLite database. I did the model first approach and I was able to query 30,000 records from Table_A in roughly 3 seconds.

Now all I did was a another Table_B which has 0 to 1 references to a parent record from Table_A. It takes over 3 minutes to run the SAME query on Table_A. Table_B has ZERO records in it.

It's also worth noting that the EDMX added Navigation Properties to Table_A and Table_B. However it only added the foreign key column to Table_B. What would cause Entity Framework to slow down that much? When I revert my changes back to the old model, it runs fast.

Update

For reference the query is a standard linq to sql query.

var matches = Table_A.Where(it => it.UserName == "Waldo" || it.TimeStamp < oneMonthAgo);

Is this some kind of a guessing game? As a bare minimum you should post the query you're trying to run... — walther, Aug 22 '14 at 22:29
@walther No. it's a straightforward, try to give a constructive helpful answer game. The query hasn't changed and is thus irrelevant. I'm hoping that someone who understands how EF works underneath when converting LINQ to SQL can offer something helpful here. EF is not my strong suit. — C. Tewalt, Aug 22 '14 at 22:37
also: have you run SQL Profiler to see the actual T-SQL queries being generated by EF? They might point to something in your data model (possibly somewhere you want an index). — Michael Edenfield, Aug 22 '14 at 22:43
Are you talking about what this guy did? http://stackoverflow.com/a/1412902/2437521 — C. Tewalt, Aug 22 '14 at 22:46
I did the sql trace from the post above. Turns out Entity framework added an extra LEFT OUTER JOIN in the query. I'll post the results in an answer. @MichaelEdenfield, thanks for the suggestion to check the t-sql queries! — C. Tewalt, Aug 22 '14 at 23:09
He is using SQLite, sql profiler for sql server wont be of use. Did you try the link you posted to get the t-sql query generated in both cases,and match them together or post them here so we can help. — Samer Aburabie, Aug 22 '14 at 23:10
@matrixugly do you know which EF template you used? It sounds like EF is not doing lazy loading the way I'd expect... — Michael Edenfield, Aug 22 '14 at 23:20

score 2 · Answer 1 · edited May 23 '17 at 11:57

I just ran the ToTraceString() to find the generated SQL query that this guy suggested in his answer here:

Turns out Entity Framework tried to be "smart" anticipating that I would use data from the child record. This is actually pretty cool! Just slows down my query a bit, so I might find a faster workaround.

Please note that this query is identical in LINQ syntax. This is just the underlying SQL that is generated as soon as I added another Table into the EDMX diagram.

Here is the FAST query: (abbreviated for clarity)

SELECT *

FROM   [Table_A] AS [Extent1]
INNER JOIN  (SELECT 
[Extent2].[OID] AS [K1], 
[Extent2].[C_Column1] AS [K2], 
Max([Extent2].[Id]) AS [A1]
FROM [Table_A] AS [Extent2]
GROUP BY [Extent2].[OID], [Extent2].[C_Column1] ) AS [GroupBy1] ON [Extent1].[Id] = 
[GroupBy1].[A1]
INNER JOIN [OtherExistingTable] AS [Extent3] ON [Extent1].[C_Column1] = [Extent3].[Id]

After adding Table_B this was the new query that was generated which made things much much slower.

SELECT *
FROM    [Table_A] AS [Extent1]
LEFT OUTER JOIN [Table_B] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Table_B_ForeignKey_To_Table_A]
INNER JOIN  (SELECT 
    [Join2].[K1] AS [K1], 
    [Join2].[K2] AS [K2], 
    Max([Join2].[A1]) AS [A1]
    FROM ( SELECT 
        [Extent3].[OID] AS [K1], 
        [Extent3].[C_Column1] AS [K2], 
        [Extent3].[Id] AS [A1]          
        FROM  [Table_A] AS [Extent3]            
        LEFT OUTER JOIN [Table_B] AS [Extent4] ON [Extent3].[Id] = [Extent4].[Table_B_ForeignKey_To_Table_A]
    )  AS [Join2]
    GROUP BY [K1], [K2] ) AS [GroupBy1] ON [Extent1].[Id] = [GroupBy1].[A1]
INNER JOIN [FeatureServices] AS [Extent5] ON [Extent1].[C_Column1] = [Extent5].[Id]

This must be an implementation-specific "feature" (read: bug) of the Sqlite query provider. It doesn't make any sense to outer join B when you don't include it in your query (twice even!). Moreover, now it generates a subquery, while Sqlite performs notoriously bad with subqueries. — Gert Arnold, Aug 23 '14 at 18:04
@JonathanAllen Do you have any preferences for data providers other than EF? or an alternative to sqlite? — C. Tewalt, Aug 23 '14 at 23:19
I usually use reflection-based wrappers over ADO.NET that add application-specific features. Look at the micro-ORMs (e.g. Dapper) for an example of what I usually start with. You're typical EF LINQ queries usually end up being just as verbose as inline SQL, if not worse because of the lambda expressions. — Jonathan Allen, Aug 25 '14 at 21:38

Entity Framework: What would cause slow queries after adding a table?

1 Answers1