EF query optimisation

We have a view that is very slow when you don’t use a filter on the name field. Everywhere in our stored procedures and (sub)queries we have this applied.

In our web applicatie we use Entity Framework. The business layer required the name filter, but the result always times out. The query looks like below.

-- simplified for clarity
SELECT [Extent1].[Name] as [Name]
FROM view as [Extent1]
WHERE [Extent1].[Name] = @p__linq__0

This is case of bad parameter sniffing. More about this in the References at the end of this post.

Reading this answer on stackoverflow made me look into the custom query execution against the Database object of your Context. And that solved the puzzle with a custom method instead of the generated IDbSet properties.

public IQueryable<viewRecordType> GetViewRecordsWhereNameIs(
string name) {
   // Danger! Sql injection possible
   var query = string.Format(
      "SELECT * FROM view WHERE name = '{0}'",
      name);
   return Database.SqlQuery<viewRecordType>(query).AsQueryable();
}

Please note the warning. Sql injection is possible when you don’t use parameters!

References

Entity Framework Performance and What You Can Do About It
How to Confuse the SQL Server Query Optimizer

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s