Currently trying to figure out a performance issue we're having with our application running on EF5, connected with MySQL DB (DB first approach) inside an ASP.NET MVC project.
Nothing complicated with the DB - 10 or so tables with only a tiny bit of data (talking about under 50 rows). Each table has a PK and there's a few FKs around - nothing dramatic or unusual. My experience with EF or MySQL is not abundant, but I've had some previous mssql and plain sql experience and have not really had too many dramas there.
I've tried investigating the issue myself and this is what I've found when comparing EF using LINQ vs EF using plain SQL
- The higher the query complexity, the bigger the difference between LINQ vs Plain SQL executed through EF
- Originally I suspected that EF could be creating highly un-optimized queries, which does not seem to be the case (proven by intercepting the auto-generated query as plain SQL, which was picked up through MySql log)
- It looks like the time taken by EF LINQ queries seems to be taken up constructing the actual query, for the lack of a better explanation, but I have no way of actually figuring that out
My understanding is that unless there is something going seriously wrong with EF or our DB schema, then there should be no way that EF should be taken 200 ms to construct a basic query:
// EF LINQ ~250ms
var joinCountEf =
(from item in dbContext.item
join user in dbContext.user on item.userID
equals user.ID
where item.ID > 0
select item).Count();
// EF SQL ~5ms
var joinCountEfSql =
dbContext.Database
.Sqlitem<int>("SELECT count(*) FROM item INNER JOIN user ON user.ID = item.userID WHERE item.ID > 0;")
.First();
The EF generated SQL looks like this:
SELECT
`GroupBy1`.`A1` AS `C1`
FROM (SELECT
COUNT(1) AS `A1`
FROM `item` AS `Extent1` INNER JOIN `user` AS `Extent2`
ON `Extent1`.`userID` = `Extent2`.`ID`
WHERE `Extent1`.`ID` > 0) AS `GroupBy1`
Any ideas whatsoever?
