Entity Framework Designer and Large Databases

Tuesday, August 19 2008 - Data Access

The Northwind database has 27 tables and views. AdventureWorksLT has 17. Sound like your database? Probably not.

What have people who have been pushing the designer with databases with hundreds or even thousands of tables & views been experiencing?

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."

I created a model from a database with about 400 tables & views, still small for an enterprise database.

The wizard itself took about 20 seconds to generate the visual model. And this is not even a very fast machine. Saving the model took about 1/2 minute as it generated the classes.

Visually the designer has tools that make it easier to view a large model and the fact that the association names are no longer displayed is really helpful.

I have seen in the forums and some blogs reports of attempts to use 1000 or 2000 tables in the designer where the wizard failed miserably.

There are also features of the model that are not supported by the tools. You can implement these in the XML, but when you have hundreds or thousands of tables, this could really make you feel a little crazy.

What about API Performance?

Testing the impact of the large model on the API performance feels like a crap shoot to me. I Know that the command compilation has to work through the model, but is that so hard? ANd of course the # of items in the tables you are querying, the # returned that have to be materialized, the structure of the entity or graph being returned also makes a difference and I can see myself getting lost for a whole day or more looking at that.

Tagged as:

#1 John Rusk on 8.19.2008 at 4:02 PM

Julie,I wondered about the same problem when I started using LINQ-to-SQL.So I wrote this: www.codeplex.com/.../Close2Poco .It lets you write fully-functional LINQ-to-SQL entites with (concise) hand-written C#, so you don't have to depend on a generator or designer.It is at proof-of-concept stage at present.The same approach could also be extended slightly to allow you to design different parts of the data model in different designer diagrams.In that scenario, most of the generation still uses the normal designer, but the entities from _different_ diagrams are "stitched together" using Close2Poco.So you can use lots of moderate-size diagrams instead of one big one.Anyway, just thought I'd mention this.I've never looked at the entity framework, but I wonder if something similar might be useful there too.

#2 Julia Lerman on 8.19.2008 at 4:11 PM

Cool, John. I wasn't' aware of it. The EDM is a much different beast than linq to SQL. L2S is basically classes with attributes whereas an Entity Data Model is a full3 part model) with a set of classes that taps into it.

#3 Jon Rista on 8.19.2008 at 7:10 PM

"There are also features of the model that are not supported by the tools. You can implement these in the XML, but when you have hundreds or thousands of tables, this could really make you feel a little crazy."Well, I guess ranting and raving is one way to become famous...or rather, infamous. ;) I'm not sure "crazy" is a strong enough word to describe my response to the painful limitations I've encountered with the EDM design tools, and EDM in general, when trying to make a practical and logical conceptual model from an existing database. Our model isn't actually that big...we have maybe 200 or so tables and a couple dozen views. When we import the model without any modifications, it seems to be fine...its when we attempt to mold the model to fit our conceptual requirements that things fall apart.I am looking forward to see what more you have to say about v1 of the Entity Framework. Your one of my primary sources of information and help (I tack on "Julie Lerman" to most of my EF searches these days), and I've become a regular visitor of your blog. So thanks.

#4 Troels Larsen on 8.20.2008 at 9:04 AM

I for one can say that either the Wizard or the OraDirect provider does not perform at all given enough tables. We have roughly 700 tables in our schema, but the wizard displays ALL schemas on the server. I do not know if it checks foreign keys for all of these, but it shouldn't.After 17 hours, the designer had still not loaded the list of tables from the server. As such, we are unable to use it.

#5 Julia Lerman on 8.20.2008 at 9:19 AM

TroelsI looked hard for a way to filter out specific schemas - especially SYS to I don't have to see all of the diagram tables and sprocs.I know this may seem like a backwards idea, but what about creating a temporary database that only has the schemas you want for your model. Create the model from there, then change the connection string to point back to the real database?I haven't tried this so it's just a idea, not proven.julie

#6 Troels Larsen on 8.21.2008 at 6:39 AM

Julie,That is exactly what I was intending on trying, but I was delayed as the SP1 RTM broke Oracle support for the OraDirect provider. I'll be trying that out soon.Oh, and the SYS tables don't actually show for me - only the tables from other user schemas. At least not on Oracle XE which I've installed locally to perform above trick. I did have to create a new user to log on as, of course. Logging is as SYS naturally and correctly shows the SYS tables.Oh, and thanks for your help on the MSDN EF forums.Btw. will your book be available as a rough cut on safaribooks.com?

#7 KMan on 3.05.2010 at 4:49 AM

Thanks for sharing, Julie.

Leave a Comment