Tuesday, August 19, 2008

Entity Framework Instantiation Times with a 120-Table Database

Updated 9/30/2008: Added association and properties (fields) count, and LINQ to SQL model generation times.

Julie Lerman discusses in Entity Framework Designer and Large Databases of 8/19/2008 how the number of tables in a database affects the time to generate Entity Framework (EF) v1 RTM’s Entity Data Model (EDM). She says:

According to Noam Ben-Ami who is on the team that works on the designer, the performance for the designer itself should have “typically reasonable” performance for up to “about 120 tables, after which things begin slowing down.”

Her test with a 400-table database took the EDM Wizard about 20 seconds to generate the visual model. “Saving the model took about 1/2 minute as it generated the classes.”

I noted in my LINQ and Entity Framework Posts for 8/12/2008 post, Kristofer Andersson of Huageti Systems gave up after the EDM Wizard spent three hours attempting to create and EDM of a 1,000-table SQL Server database.

A 120-table database (from Huageti Systems), which is typical of those used by a reservation system for small airlines but contains no data, took 50 seconds to generate the model in the EDM Designer. The same operation with LINQ to SQL takes 22 seconds.

Update 9/30/2008: The entities have a total of 205 associations and 1190 scalar properties (9.9 properties/entity.)

What’s more important, in my opinion, is the time to generate the EDM at runtime. The following table lists the times to instantiate and cache the runtime EDM with LINQ to SQL and Entity Framework in a WinForm client.

Times to Instantiate a 120-Table Entity Data Model in a Windows Form Client

Comparison

LINQ to SQL

Entity Framework

Data/ObjectContext initiation time (first), s.

0.394

9.426

ObjectContext initiation time (precompiled), s.

N/A

2.063

Data/ObjectContext recreation time (next), s.

0.004

0.021

Clearly, 9.5 seconds to cache the EDM is excessive and waiting 2 seconds for a precompiled version to open isn’t satisfactory. (The preceding times were generated with EF v1 Beta; there’s no significant difference with EF v1 RTM. See my Comparison of Entity Framework and LINQ to SQL Projects Using a 120-Table Database post of July 12, 2008.)

Update 8/19/2008: ObjectContext initiation consumes about 60% to 80% of CPU time for the full 9+ seconds. The metadata cache size (without data) is 21 MB.

I wonder how long it would take a 400-table EDM to open. Perhaps 30 seconds, if that’s what Julie meant by “saving the model.”

6 comments:

Anonymous said...

I'll keep an eye out for your metadata loading comparison between models of different sizes. :-) That metadata load is a one time hit for the application, so I'm thinking that you might even want to do it before the first query.

Roger Jennings (--rj) said...

@Julie,

Yeah, caching seems to work correctly, but 9 seconds of 60% to 80% CPU is a lot of resources to spend on loading metadata. The cache size for this database without data is about 21 MB.

You don't have much choice but to cache the metadata before the first query. ;)

--rj

Anonymous said...

Roger and or Julie, I am interested to know what impact this metadata caching time would have if I was using ADO.net data services on an EF model ? For example at the moment I am using on learning and have created a 3 table database to do so and the response is good. But are you saying if I move my 200 table database eventually then there will be a significant performance issue and can you explain how this caching would work with astoria and a diconnected silverlight client. thanks in advance.

Anonymous said...

9 seconds is a major issue. In our v2.5 we introduced dependency injection with auto-discovery of injectable types. This took for example 2-3 seconds at startup of the app (we don't have any other delays at startup). People weren't happy about that at all and asked for a parameter to switch it off.

9 seconds or more is way too much. People expect to have an app load instantly, not have to stare for 9 seconds or more at a splashscreen when nothing happens with for example the harddisk. It takes that long because it has to verify the model. Why that takes that much time is beyond me, as a lot of algorithms are available to efficiently traverse graphs.

The saving of the model takes a lot of time because they then also do code generation.

What the EF designer team forgets to tell everyone with their 400 table system is that even though it might not take half a day to load the model, the designer itself is unusable, due to the 400 elements on screen.

Anonymous said...

May I ask what you are using for measuring the according timespans?

Thanks

Anonymous said...

We all know that LINQ to SQL is an extremely performant OR/M (here's waiting for Francis Bouma to step in), but I have to admit that the reported performance of Entity Framework is quite low. Perhaps the Entity Framework team should bring on the team members from LINQ to SQL (did they try already?), now that Microsoft "presumably" have stopped working on LINQ to SQL.

Roger, did you make specific benchmarks on different types of queries? (not 100% sure on how to read that from your numbers)

Anders Borum / SphereWorks