Association properties, nightmare performance (Entity Framework)

Question

I have a fairly large EF4 model, using POCO code gen. I've got lots of instances where I select a single entity from whichever table by its ID.

However on some tables, this takes 2 minutes or more, where on most tables it takes less than a second. I'm out of ideas as to where to look now, because I can't see any reason. It's always the same tables that cause problems, but I can query them directly against the database without problems, so it must be somewhere in Entity Framework territory that the problem is coming from.

The line is the quite innoccuous:

Dim newProd As New Product
Product.ShippingSize = Entities.ShippingSizes.Single(Function(ss) ss.Id = id)

• id is simply an integer passed in from the UI, Id on my entity is the primary key, which is indexed on the database
• Entities is a freshly created instance of my entity framework datacontext
• This is not the first query being executed against the Context, it is the first query against this EntitySet though
• I have re-indexed all tables having seen posts suggesting that a corrupt index could cause slow access, that hasn't made any difference
• The exact same line of code against other tables runs almost instantly, it's only certain tables
• This particular table is tiny - it only has 4 things in it

Any suggestions as to where to even start?

--edit - I'd oversimplified the code in the question to the point where the problem disappeared!

Answer 1

Seems like this is a function of the POCO template's Fixup behaviour in combination with lazy loading.

Because the entity has already been loaded via Single, subsequent operations seem to be happening in memory rather than against the database. The Fixup method by default makes Contains() calls, which is where everything grinds to a halt while 10s of thousands of items get retrieved, initialised as proxies, and evaluated in memory.

I tried changing this Contains() to a Where(Function(x) x.Id = id).Count > 0 (will do logically the same thing, but trying to force a quick DB operation instead of the slow in-memory one). The query was still performed in-memory, and just as slow.

I switched from POCO to the standard EntityGenerator, and this problem just disappeared with no other changes. Say what you will about patterns/practices, but this is a nasty problem to have - I didn't spot this until I switched from fakes and small test databases to a full size database. Entity Generator saves the day for now.

Answer 2

Where to start?

1. Print or log the actual SQL string that's being sent to the database.
2. Execute that literal string on the server and measure its performance.
3. Use your server's EXPLAIN plan system to see what the server's actually doing.
4. Compare the raw SQL performance to your EF performance.

That should tell you whether you have a database problem or an EF problem.