0

I'm writing an application that uses SQLite as its database, and since I'm doing it with C#, I thought using Entity Framework to handle all my database access would be a good idea. It was working fine when I was using a small (around 10k rows in the Identifiers table) dataset to test things, but when I started using a real dataset(around 950k rows in the Identifiers table), it became way too slow. For instance, this statement

id = domain.Identifiers.FirstOrDefault(i => i.ExternalId.Equals(externalId));

takes about 55 seconds to run, while the equivalent SQL Query

//database is my dbContext
id = database.Identifiers.SqlQuery("SELECT * FROM Identifiers WHERE DomainId = @p0 AND ExternalId = @p1", domain.Id, externalId).FirstOrDefault();

takes miliseconds.

I'm assuming (and hoping) I'm somehow misusing LINQ, or Entity Framework, or both, because LINQ, Entity Framework and SQLite were all made by people much smarter than me, and I'd rather not have to re-write all my data access code to use SQL queries. Can anyone help me figure out why the LINQ statement takes so long to execute?

Edit: I tried

id = database.Identifiers.FirstOrDefault(i => i.DomainId.Equals(domain.Id) && i.ExternalId.Equals(externalId));

and it was almost as fast as the SQL query, which solves my problem. Still, is there some reason for the massive difference in performance between calling the FirstOrDefault method on an entity and calling it on a dbContext?

António Poças
  • 1
  • 1
  • 3
    What does the generated SQL look like in your first query? – Yuval Itzchakov Jun 08 '15 at 11:26
  • Use tools like Profiler Express to find the exact query that you are producing with LINQ. – jonas Jun 08 '15 at 11:27
  • 1
    It also looks like your first query only searches on one field, where the second searches on two fields. Do you have the appropriate indexes? – Blue Jun 08 '15 at 11:27
  • Lazy loading turned on? – Kye Jun 08 '15 at 11:28
  • @jonas `FirstOrDefault` should generate a WHERE statement. – Blue Jun 08 '15 at 11:28
  • @FrankerZ I know, that's why I edited my post right before you commented on it ;-) – jonas Jun 08 '15 at 11:29
  • 2
    `WHERE DomainId = @p0` could allow the database to massively narrow the search in the 2nd snippet. Or it could be that `DomainId` is indexed while `ExternalId` is not. Please compare apples to apples. – Special Sauce Jun 08 '15 at 11:29
  • @SpecialSauce in the first snippet I'm searching the domain entity for the Identifier with the ExternalId I want, which already narrows down the search to the Domain I want. Or at least it should, anyway :/ – António Poças Jun 08 '15 at 11:34
  • Please use the accepted answer of this post to get the precise SQL generated by the 1st snippet: http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework It will probably make the problem clear. You might also want to try `==` instead of `.Equals()`; LINQ does not always translate calls intuitively and may add in extraneous null checks, etc. – Special Sauce Jun 08 '15 at 11:38
  • I rewrote it to run the equivalent of the query on the dbContext object instead of the domain entity and it was much faster. Apparently if I run it on domain it first queries the Database for all the identifiers where DomainId = domain.Id, and then runs a LINQ query on the result. – António Poças Jun 08 '15 at 11:57

0 Answers0