Optimising MySQL queries when using Entity Framework

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Posts: 1
Joined: Wed 14 Oct 2009 12:36

Optimising MySQL queries when using Entity Framework

Post by timmartin » Wed 14 Oct 2009 12:43

I'm evaluating transferring our code to use Entity Framework via the Devart provider, connecting to a MySQL database. In the past we've found that naive MySQL queries don't always optimise well, for example when doing a join among several tables MySQL picks a poor join order.

Using raw SQL it's easy enough to add hints to the MySQL optimiser to override the default table ordering or index choice, and getting this right can often improve query performance significantly. Is this level of control available to us if we use entity framework? Presumably one solution is to add views that are backed by custom queries with the right optimisation hints, is this the only way to achieve it?

Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 15 Oct 2009 08:24

You have two alternatives - the first one is to create views in your database implementing the
desired behaviour, and the second one is to manually create Defining Query in the Storage model
and entities in your conceptual model corresponding to these Defining Queries.
We cannot advise you any other appropriate way to deal with the described situation.

Post Reply