Views are great. They simplify design, makes code look more elegant and hides complexity. They also enables reuse by putting complex code in just one place instead of in every accessing piece of code.

But…

There once was a large project that has been churning away for a long time. The performance of having more than one user on the system was horrendous. But it was chalked up to misconfiguration.

I am asked to spend a day giving said configuration a quick glance.

I start looking and am simultaneous asked to write code to make it possible to generate large volumes of data. These two efforts leads deep into their design.

It turns out a creative way to model the data was hidden in a layer of views. On top of those views there were other views. Something happened here with performance and it was painful beyond belief.

The data model had to be completely changed to allow SQL to properly navigate it in a relational fashion. But the views was a much bigger problem. It had ended up being an object oriented persons wet dream. Multiple things was subclassed that in the relational world would be one. Here is a completely made up example, the data was of a different kind but to make it less obvious for outsiders what I’m referring I’ve changed what was represented.

There was different kinds of people, lets say managers, project leaders and individual contributors. Each of these were in their own table. Those people had access to one or more vehicles, tractor, bicycles and cars. Each vehicle was placed in it’s own table.

These subclassed tables were essentially identical. It could have been one table. In fact the code needed to be able to deal with people as one entity and vehicles as another. To achieve this one view for each was created that put them back together via a union all + order by.

Next up was the access for these things together (what vehicles a certain person has), to achieve this a new view was created that did a join of the persons view with the vehicles view. Now this work just fine in development with some 30 persons and 30 vehicles.

When the data grows, it does not work at all. The reason being that a unioned dataset cannot be joined with another unioned dataset using indexes, especially when the data being unioned does not reside in the same table.

What ended up happening was that all views were fully materialized and sorted, then the join happened and out of it we pulled a single record. And then it was done again. This caused a massive performance issue and not even a big EXADATA helped the performance.

Of course to make matters worse, an ORM was put between actual code and the database. So what really happened was even more hidden. The developers did not understand the database and the database folks could not read and understand the SQL passed in by Hibernate.

My report stated that I saw no way for this to work and I could not see a way to fix it without doing a major redesign. It finished off with saying I realize it will not happen as it will be months upon months of rework for the whole project and that cost was not going to be accepted.

I come in the morning after and am told to drop everything. I get to redesign it the way I want and no limits are placed on the design. Nine months later the application has completed a rewrite for the data model and access model I have built. It is by far the best performing application at the site.

Did we learn from this to design data model before code. No, that is not the hip way to do it. What people took away even though I explained why this happened time after time is “Views are bad, you cannot get good performance with them.”.