Entity Framework + MySQL - Why is the performance so terrible?
- by Cyril Gupta
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.
