Entity Framework + MySQL - Why is the performance so terrible?

Question

When I decided to use an OR/M (Entity Framework for MySQL this time) for my new project I was hoping it would save me time, but I seem to have failed it (for the second time now).

Take this simple SQL Query

SELECT * FROM POST ORDER BY addedOn DESC LIMIT 0, 50

It executes and gives me results in less than a second as it should (the table has about 60,000 rows).

Here's the equivalent LINQ To Entities query that I wrote for this

var q = (from p in db.post
            orderby p.addedOn descending
             select p).Take(50);

    var q1 = q.ToList(); //This is where the query is fetched and timed out

But this query never even executes it times out ALWAYS (without orderby it takes 5 seconds to run)! My timeout is set to 12 seconds so you can imagine it is taking much more than that.

• Why is this happening?
• Is there a way I can see what is the actual SQL Query that Entity Framework is sending to the db?
• Should I give up on EF+MySQL and move to standard SQL before I lose all eternity trying to make it work?

I've recalibrated my indexes, tried eager loading (which actually makes it fail even without the orderby clause)

Please help, I am about to give up OR/M for MySQL as a lost cause.

Answer 1

I think the first step would be figuring out what SQL is being sent to MySQL. This article explains how to turn on logging in MySQL. If possible you might want to see if .NET 4.0 beta 1 improves the generated SQL.

Answer 2

You can also get the SQL from the EF provider via ToTraceString.

Answer 3

All my research finally culminated into the conclusion that while EF in general is bad for performance, MySql+EF is downright shoddy. SO's choice of L2S over EF is a good move and if I had access to a MS Sql database instead of MySQL I would have moved in that direction too.

Unfortunately I am stuck with MySql cause it's free and that has forced me to give up EF. I am now back to hard-coding my SQL queries the old, tested, efficient way and the results are good.

I gave MYSql + EF a pass, but I would love to hear from people who have successfully used it in a non-trivial project.

Answer 4

Hello, i have successfully used MySql with Linq to SQL, using the open source project DBLinq. I know its not Entity Framework but the probramming models are familiar. Hope this helps anyone!