I recently blogged on the use of ‘select distinct’ as a lazy coding practice to remove duplicate rows from a result set. I solicited opinions from my knowledgeable coworkers and was able to modify my thinking a bit on the subject. However, nested views are a problem that I think we can all agree on.
Just last week I received a report concerning a query that had been running at an acceptable 39 seconds the day before, but now was taking over two minutes. A cursory examination of the explain plan displayed multiple nested loops on the same handful of tables. I relayed this information to the developer reporting the issue, but was told over and over that it ran fine yesterday. However, I did not think that 39 seconds was all that great. I was not able to focus on it in greater depth at the time due to other performance issues I was working, all of which were caused by an underlying storage tier issue. Once that was resolved, all involved systems returned to normal.
The next day, I circled back around on the report query and discovered the reason for the multiple nested loops. This is the response I sent to the developer with redacted table names for security reasons:
“After yesterday’s crisis had passed, I was able to further review the report query and have enclosed the object mapping notes with the following observations. Nested views are used in this query. Nested views refer to views that include other views in their makeup. The problem with these types of views is that tables in one or more of the child views can sometimes be found in the parent views. This causes the same tables to be revisited over and over for similar data. Even if the tables where accessed for different data on subsequent revisits, the same data could have been retrieved during previous visits. Referring to the enclosed notes, some of the tables being visited numerous times include TAB1, TAB2, TAB3, and TAB4. Care should be taken when using views that contain other views so that table revisits or nested loops can be avoided. Even though the query’s performance is acceptable at this time, increased volume of information may not allow this query to continue to perform at acceptable levels. Also, two of the subsequent views use one or more UNION statements on the same objects. The use of UNION more than once may indicate another condition of unnecessary table revisits which could also negatively impact query performance. Thanks.”
The problem with nested views lies not only with the use of nested views, but with their creation as well. In this regard, the responsible DBA should resist the creation of views composed of other views unless determined that the same tables are not in the makeup of more than one view.
The way to avoid nested views in my opinion is to select from tables first, then from views and to avoid the joining of views unless it has been determined that they do not contain the same tables.