Large DB performance (code generation)

Developer
Jun 17, 2010 at 4:08 PM
Edited Jun 17, 2010 at 8:31 PM

I'm evaluating nHydrate for a Silverlight project that will be built on top of a large legacy database (~700 tables).

I have to say, I really appreciate the fine control over the generated output and the n-tier strategy.

However, it takes several hours to generate the basic model code, let alone the other layers I need :(

I have profiled the code and it appears not to be an nHydrate problem specifically but rather a VS API issue, namely the call to EnvDTE.ProjectItems.AddFromFile(...) which seems to take increasingly more time each time it's called.

I have modified the EnvDTEHelper class in an attempt to minimize the impact by creating a cache of existing items before the generation begins and checking against the cache and only calling AddFromFile if the file is not included in the project already.

This works OK for subsequent runs but (obviously) is irrelevant when starting from scratch.

Is this something anyone else has noticed?

I'm using VS2010 and .Net 4.0...

Coordinator
Jun 18, 2010 at 5:12 PM

yes we know about this; however this is just VS.NET like you said. If you have a change that may help the speed please send it to me at chrisd@nhydrate.org. Also as for 700 tables, we are working on a couple of things. The first is an Entity Framework DAL which may make generation smaller due to some new changes. Also a bigger feature for you is modules. You most likely do not need all 700 entities types in the same assembly. We are developing a way to create modules in which you can place tables/entities each compiled into its own assembly. That way you might have an accounting, customer, maintenance, etc assembly that uses its own store of entites to get its job done while not needing or depending on most of the other entities. Is this something you might need?

Developer
Jun 19, 2010 at 1:20 PM

I'll forward the changes although I'm not convinced it's best way to handle the problem.  Probably a faster solution (but certainly a hack) would be to write directly to the project file and force a reload at the end...

I like the idea of modules, however some of our table relationships would be 'cross-module' so that would need to be accommodated somehow.

I am currently using EF but I have some issues with it...

EF doesn't seem to handle large models very well, the first query on a my model (importing all 700 tables) takes over 40 minutes in a call to a 'ELinqQueryState.GetExecutionPlan' in the bowels of EF.  The result of that process appears to be cached so subsequent queries are not impacted...

 

Coordinator
Jun 22, 2010 at 1:47 AM

Modules. 

With modules we basically have a dependency idea.  The following user story represents the idea. It is thought out at a much deeper level. However, we are still in the process of defining how large a project it will be to get something useful up and running. If only we didn't have day jobs.

 

A system for tracking orders was built using nHydrate. This was the first module. Then someone came along and created another module for doing HR activities like managing each employees Health Policy Choice. Finally, someone decided to add a salesman tracking (Customer Relationship Module) application. In this way they could track what salesmen were assigned to what customers.

Things to recognize:

Relationship between customer and employee is owned by CRM.
CRM system has many dependencies on data and tables owned by other systems.
CRM system does not include Employee.Salary attribute in its list of dependency.
The foreign key is always owned by the same module that owns the relationship
The primary key for a table is always owned by the same module that owns the table.