Database Views and ORM?

Hello everyone,

Perhaps someone can provide some direction for the problem that I face. We're building a standard web-based, 3-tier enterprise application using our own light-weight object-relational mapping layer.

Serveral web pages have been identified for optimization in our system, as it has been shown that they recieve a lot of user traffic, and they all contain a fair number of complex gridviews. On each page-load, there is a lot of database contention as it pulls out the necessary data and builds the domain object graph. For example, a number of the gridviews display data from entities nested within other entities, which results in a lot of lazy-loading going on behind the scenes.

I would like to optimize some of the grid views on these pages by constructing database views, which select only the necessary data columns that need to be displayed. I would then write entity/domain object classes for the view. These classes would be used primarily in displaying data on these pages, and so they are somewhat use-case centric.

However, I can't find any literature or examples anywhere that might give me some insight into how this might affect the rest of the system. How should I model the entity class for the view, as it contains fields duplicated in other entities Is there anything I need to look out for when introducing such disjoint entities into the system design

Alternatively, I could just suck it up, and simply bypass our domain logic and ORM layers, and just access the view the old-fashioned way by loading it into a DataTable using a DbCommand, or even by tying in SqlDataSource controls to the grid views in question. Afterall, we are on a tight-schedule to deliver a working product. But I fear that this will result in another axis of orthogonality, if you will, in the system design, and therefore it might lead to more maintenance headaches in the future.

Any comments on how I should proceed Thanks.

Answer this question

Database Views and ORM?

Sportsdude
I agree with a number of your points. Primarily that the .NET Framework, and ADO.NET in particular, are currently ill-suited for developing solutions that employ some form of O/R mapping. You have to expend a fair bit of effort or purchase a third-party component to get it to work. Regardless, your recommendation is a little late for me, as we have an established code base. If I could start over, I'd probably at least reconsider the path we took in building this application. This begs the question, why did we take the path we did, in the first place Why not just use the tools that are already there; they're good enough, after all, aren't they

I believe the answer to that question is rather subjective, and it depends upon the needs of your application, and in turn, on the needs of your clients. For us, we needed to develop a system that could withstand a significant amount of change in business rules and requirements. The system needed to allow for the potential for extensibility in a number of a subsystems. Well designed and highly decoupled OO-based architectures are well suited to handling this. I kind of wish this aspect of object-orientation was emphasized over that of the so called benefit of code re-use in colleges and universities. Too many instructors and professors say partial truths such as "object-orientation is good, because it makes your code more reusable," while failing to point the other benefits, as well as the disadvantages (more internal complexity, more time spent up-front to get a working system, etc.). I mean, come on, in reality, you can write some pretty darned unreusable, object-oriented code.

As it stands now, it's really simple to add in a new set of business services into our application, without it impacting the rest of the existing system. That was one of our primary goals, and I think we accomplished it. The trade off that we're paying for now is in performance.

I suppose one of the problems with O/R mapping, and entity-driven design in general, is that their is a risk that you can end up focusing too much on the entities and their relationships. It's easy to forget about the wealth of other patterns, idioms, etc. that you have in your OO toolkit, available for disposal. What about behavioral and creational type objects

I have to admit, I got sucked into this rut while on this project. I let O/R mapping blind me. I did a lot of soul searching over the weekend, and tried to figure out what I should do come Monday. I certainly can't delete all of the code we worked hard at developing. But what I am going to do is this:

First, I'm going to refactor some of our existing controller code on the troublesome pages into service-layer classes, while employing dependency inversion. I can then write specialized versions, where needed, that access an optimized database view. Yes, our application doesn't currently implement much of a service-oriented architecture; the domain logic is tightly coupled to the presentation layer. Looking back, I really think we could have reaped a lot of benefits from seeking an SOA design, but it's too late now. Perhaps for the second iteration.

Second, I'm going to implement secondary caching in these service-layer objects.

Hopefully, with a bit of experimentation, I can bring the performance penalty down to neglegible levels.
KingKarter
Jesse

I hope I am useful with my thoughts here. I thank you because you are useful to me giving me the example I needed to explain why O/R-M is a 2-blade knife

First of all, the idea behind using OR mappers is not to loose time writing long data access logic (surely not so complex but lengthy in lines of code and, that way, in time). So, if understanding the good, the bad and the ugly about OR mappers is complex and takes too much time, I guess you understand that you are just replacing an old, known problem with another one new, unknown

There are some keys I rescue from your post:
- Database contention building object graphs
- Wish of optimization through database views
- And the fundamental (you said it, not I): use-case centric
Simplicity is the answer. Simplicity is one of the pillars of eXtreme Programming methodology (a software development process with emphasis in the outcomes, similar than your context). I usually model the domain logic in classes more related with the process than the entities. I mean, I prefer the data-driven approach. This approach implies to model entities mostly reflecting the database schema (pls, not an exact copy, but considering how data is organized in the persistence repository)

This approach has its drawbacks because is in certain way coupled, but the alternative -in my opinion- is worse. The alternative is the object-driven approach, that implies to model entities without consider the database schema (solving the gap in the data access layer, either through coding, or through OR mappers, either through persistence frameworks like ADO.NET, or through another mechanism). Object-driven approach is not coupled

The smaller the gap between entities and database schema, the thinner the data access logic and that the reason why I prefer the data-driven approach

If you follow that pattern, you can associate grid views straight to your database tables (and that task is for free in Visual Studio 2005). Visual Studio 2005 generates all the necessary lines of code and is very fast. I admit that, from an architectural point of view, there's not too much code reuse. But if time matters, Jesse, align the technology with the business first, before the business takes over technology

This essay is obsolete in terms of applied technologies, but useful in terms of concepts, and in the recently is being recommended several times in this forum

Designing Data Tier Components and Passing Data Through Tiers

I hope I'm giving you solutions and not new problems
Malcy

In most of the applications I write, the UI pages will either classify themselves as interfaces for CRUD, or they would be like reports - with complex queries and perhaps computations.

It is usually these report pages which are the performance bottlenecks. I tend to address them by fetching data for these by custom queries/stored procedures returing a recordset which I then may load into the same set of objects.

Looking at CRUD pages, the summary "retrieve list " pages may also tend to be a bit slow - but its usually possible to address them by defining appropriate indexes and other database wizardry. They are hardly ever non-addressable.

The grey area is where the "list" pages also display the properties of their child or associated objects. There is usually no straightforward answer about handling them. Denormalizing (or bringing those attributes into the primary datatable) - either for the table or by using a "materialized view" often helps, but impacts data update mechanisms. They are not complex per se but are problemetic in maintaining the application.

The approach which I prefer to use in such case is to "paginate" the screens and display only lets say 20 records at a time and fetching the details only for those 20 child records as the screen is displayed. It is easy to have lazy loading implementations either via OR mapping or otherwise to achieve it.

Ajax based UIs can handle it even better by fetching this data by seperate requests from the UI, either as a part of page load or On demand - like on mouse over.

In short - in my opinion - apart from "report like" pages, everything else can perform in OO manner with or without OR mapping. Treat the report like pages seperately and forget Object orientedness there for better performance at a cost of poorer maintainability.

Pranshu
Alexander Schwerdtner

If your grids are for reporting purposes I don't see any harm for by passing the O/R model. In fact that is a way to go.

Also I usually don't try to make my UI layer objects (ASP.NET in your case) the same as the ones in business layers since objects in the different layers usually have different needs

Arnon
Database Views and ORM?

Answer this question