LinQ query has long duration because of View recalculation

Question

Hello,

I've created a View in the database that gives me a representation of an organisation structure AT THIS POINT IN TIME. Indeed, I use a getdate() in this view. I've imported the view in my edmx and wrote queries based on this view. Most queries join a table with the view and return a DTO. These queries work fine and are fast (200ms) when I profile them in SQL profiler.

When I do a group by on the joined table and view, then the duration is much longer. Strangely enough, when I execute the LinQ query the duration is very long, but when I execute the generated query myself in SQL Server Management Studio, it is realy fast. I've made sure this wasn't caused by caching or execution plans that have already been calculated. The LinQ query also gives me a lot of Reads and Writes, while the query via SQL Mgmt Studio gives me no Writes and a lot less Reads.

We have a presumption that the view needs to be recalculated when the query is executed via LinQ and not when it is executed via SQL Mgmt Studio.

Another approach I've tried, is to create a stored procedure in the database. This stored proc executes exactly the same query that LinQ generated. I've mapped this stored proc in the edmx. When I call the stored proc via LinQ again, the duration is very long (7000 ms) and lots of reads and writes. When I execute it via SQL Mgmt Studio, than duration is as expected (200ms) and little reads and no writes.

Any suggestions? Thanks

Answer 1

It sounds like Management Studio is doing some sort of query plan optimisation that LINQ can't do as it executes a series of separate commands.

What I'd suggest is to try to bring a reasonable set of records into memory and do the join there. It's quite easy to do.

Say you had this query:

var pcs =
    from p in dc.People
    join c in dc.Colors on p.FavColor equals c.Name
    where p.Name == "James"
    select new { p.Name, c.ColorId };

This would generate a series of queries to SQL which could cause the type of delay that you're experiencing.

So try this instead:

var pcs =
    from p in dc.People.Where(x => x.Name == "James").ToArray()
    join c in dc.Colors.ToArray() on p.FavColor equals c.Name
    select new { p.Name, c.ColorId };

This should reduce the queries to one or two and be much much faster. You just need to make sure that this won't try to load half a million records into memory. If that happens your performance issues will shift from the database to memory.

I hope this helps.