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
– walther, Commented Aug 22, 2014 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
– C. Tewalt, Commented Aug 22, 2014 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
– Michael Edenfield, Commented Aug 22, 2014 at 22:43
Are you talking about what this guy did? stackoverflow.com/a/1412902/2437521 — C. Tewalt
– C. Tewalt, Commented Aug 22, 2014 at 22:46
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
– Samer Aburabie, Commented Aug 22, 2014 at 23:10

Community · Accepted Answer · 2017-05-23 11:57:42Z

2

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]

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Aug 22, 2014 at 23:37

C. Tewalt

2,5072 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gert Arnold Over a year ago

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.

C. Tewalt Over a year ago

@JonathanAllen Do you have any preferences for data providers other than EF? or an alternative to sqlite?

Jonathan Allen Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related