Problem with slow query using predicate builder

Question

This question takes no time at all to execute, but if I uncomment the last line it takes 5 seconds. How could this affect the query that much? It's almost the exact same query.

return db.Parties.Where(predicate).Select(p => new SearchResult
{
    adressPerson = p.Contacts.SingleOrDefault(m => m.contact_type == "H").streetname,
    //adressOrg = p.Contacts.SingleOrDefault(m => m.contact_type == "W").streetname
});

How do I locate the problem?

Here's what the predicate looks like:

    predicate = predicate.Or(p => p.surname.EndsWith(keyword));
    predicate = predicate.Or(p => p.lastname.EndsWith(keyword));
    predicate = predicate.Or(p => p.organisation.EndsWith(keyword));
    predicate = predicate.Or(p => p.Contacts.Any(c => c.streetname.EndsWith(keyword)));
    predicate = predicate.Or(p => p.Memberships.Any(m => m.Congregation.congregation_name.EndsWith(keyword)));
    predicate = predicate.Or(p => p.PartyCategories.Any(m => m.Category.category_name.EndsWith(keyword) || m.Category.category_code.EndsWith(keyword)));

I know it's stupid to post a lot of code but here's the SQL-query anyway. Any clues?

SELECT (
    SELECT [t6].[streetname]
    FROM [dbo].[Contact] AS [t6]
    WHERE ([t6].[contact_type] = @p7) AND ([t6].[party_id] = [t0].[party_id])
    ) AS [adressPerson], (
    //SELECT [t7].[streetname]
    //FROM [dbo].[Contact] AS [t7]
    //WHERE ([t7].[contact_type] = @p8) AND ([t7].[party_id] = [t0].[party_id])
    //) AS [adressOrg]
FROM [dbo].[Party] AS [t0]
WHERE ([t0].[surname] LIKE @p0) OR ([t0].[lastname] LIKE @p1) OR ([t0].[organisation] LIKE @p2) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Contact] AS [t1]
    WHERE ([t1].[streetname] LIKE @p3) AND ([t1].[party_id] = [t0].[party_id])
    )) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Membership] AS [t2]
    LEFT OUTER JOIN [dbo].[Congregation] AS [t3] ON [t3].[congregation_id] = [t2].[congregation_id]
    WHERE ([t3].[congregation_name] LIKE @p4) AND ([t2].[party_id] = [t0].[party_id])
    )) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[PartyCategories] AS [t4]
    INNER JOIN [dbo].[Category] AS [t5] ON [t5].[category_id] = [t4].[category_id]
    WHERE (([t5].[category_name] LIKE @p5) OR ([t5].[category_code] LIKE @p6)) AND ([t4].[party_id] = [t0].[party_id])
))

SOLVED Turned out that the INNER JOIN in the last select statement didn't work. So I changed the last line of the predicate to:

predicate = predicate.Or(p => p.PartyCategories
    .GroupJoin(db.Categories, b => b.category_id, c => c.category_id, (b, c) => new { b, c })
    .SelectMany(c => c.c.DefaultIfEmpty(), (c, b) => new { c.c, b })
    .Any(m => m.b.category_name.EndsWith(keyword) || m.b.category_code.EndsWith(keyword)));

score 2 · Accepted Answer · answered Sep 12 '11 at 08:43

2

How do I locate the problem?

As always, the way to find out is to look at the generated SQL, and run it through SQL profiler.

My guess is that it changes from a simple inner join when you're only interested in a single contact type into something rather more complicated, or possibly even an "N+1-selects" problem. Anyway, looking into the SQL should make it a lot clearer.

answered Sep 12 '11 at 08:43

Jon Skeet

1,421,763
867
9,128
9,194

I guess this was the problem in a way. When I rewrote the predicate it turned the SQL into a LEFT OUTER JOIN instead of an INNER JOIN. – Niklas Sep 12 '11 at 12:35
@Niklas: Right - and did you see what the SQL profiler did in the two different cases? – Jon Skeet Sep 12 '11 at 12:37
@Niklas: Not sure, to be honest... it feels like if this is an important enough concern to warrant profiling, you should probably have at least one "full" licence :) – Jon Skeet Sep 12 '11 at 12:44

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

Niklas,

I would look at the SQL as suggested to see what's happening at that level. I'd also suggest doing an .Include() on the initial linq query as this will eagerly load the Contacts, Memberships and PartyCategories entities. Otherwise, you definately will be lazy loading on an N+1 select pattern. Check out the following link for .included usage:

http://blogs.msdn.com/b/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx

Linq-to-entities - Include() method not loading

http://www.singingeels.com/Articles/Entity_Framework_and_Lazy_Loading.aspx

cheers...

edited May 23 '17 at 11:55

Community

1
1

answered Sep 12 '11 at 09:28

jim tollan

22,305
4
49
63

hi niklas, ahh then none of the above would make any difference. .Include() is ef only. i would however say that on big datasets, the excessive use of the like statement on non indexed fields will have a major performance impact. – jim tollan Sep 12 '11 at 12:35
Thanks anyway! This will probably come in handy when I switch to EF later. – Niklas Sep 12 '11 at 12:37

Problem with slow query using predicate builder

2 Answers2