CODEWITHSUNDEEP
c#asp.netdatabase-designentity-framework-4
citronas
citronas

Reputation: 19365

How to create a business model wrapper for a generic database approach?

I'm currently facing a performance problem with creating POCO objects from my database. I'm using Entity Framework 4 as OR-Mapper. The whole application is a prototype for now.

Let's assume I want to have some business objects like classes 'Printer' or 'Scanner'. Both classes inherit from a BaseClass called Product. The business classes exist.

I try to use a more generic database approach. I don't want to create tables for "Printer" nor "Scanner". I want to have 3 tables: One called Product, and the other Property and PropertyValue (which stores all assigned values to a specific Product).

In my business layer I do create a specific object like this:

public Printer GetPrinter(int IDProduct)
{
  Printer item = new Printer();
  // get the product object with EF
  // get all PropertyValues
  // (with Reflection) foreach property in item.GetType().GetProperties
  // {
  //   property.SetValue("specific value")
  // }
  return item;
}

This is how the EF model looks like: enter image description here

Works fine so far. For now I'm doing performance tests for retrieving multiple sets.

I've created a prototype and improved it several times to increase the performance. It is still far away from being usable. I takes 919ms to create 300 objects who only contain 3 properties.

The reason for choosing such DB design is to have a generic database design. Adding new properties should only be done in the business model.

Am I just being too stupid to create a performant way of retrieving xx objects or is my approach totally wrong? As far as I understand OR-Mapper, they are basically doing the same?

Upvotes: 3

Views: 1096

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

I think you missed whole point of ORM. The reason why people are using ORM is to be able to persist buisness objects and easily retrieve business objects. You are using ORM to get just data for your business objects' factories. Factories are using reflection to build business object from materialized classes retrieved by ORM. This will always be very slow because:

  • Query compilation is slow (you can precompile it)
  • Object materialization is slow (you can't avoid it)
  • Reflection is slow (you can't avoid it)

IMO if you want to follwo this DB design to have generic tables absolutely independent on your business objects you don't need ORM or at least you don't need EF.

The reason for your performance problems is that generic approach is not follwed in your business model. So you must somewhere convert generic data to specific data = slow operation.

If you want to improve performance define set of shared properties and place them into Product. Then either use your current PropertyValue and Property for additional non shared properties or use simply ExtendedProperties table storing key value pairs. Your entities will be of type Product with inner type property, shared properties and collection of extended properties. That is generic approach.

Upvotes: 5

Bayrat
Bayrat

Reputation: 161

Firstly, it's not clear to me what you have in the way of POCOs. Did you hand code these and your context or T4 generate them? There are some great articles here that benchmark performance with no POCO, T4 Generated POCOs/Context and hand coded POCOs/Context. As expected there is HUGE performance savings going with POCOs (more than a 15-fold boost in performance in his benchmark) going the POCO route over those generated by the Entity Framework. You don't say what DBMS...if MSSQL have you turned on the profiler and see what's being generated?

Upvotes: 1

Related Questions