asked
Reading Data From View Substantial performance hit from reading directly from Table
We have a new system that ETLs data from a third party Data source. The old product (Home Grown) copied data to tables in a Datamart. Because the new system has different table names and some field differences. (the third party system is the same however the only difference is the ETL tool) So we wrote compatibly views using the same names as the old tables (Now Renamed) with same ordinal of fields for access to the data. All the views have a NOLOCK hint as well. Also the new tables basically have the same Primary key and indexes on them as the old ones. So basically the views look just like the old tables with the same data. The problem is that Running a query against the View is a factor of 10 slower than reading from the original tables.
I hope you didn't use the View Designer in SSMS? It has been known to include TOP and ORDER BY in a view, effectively crippling performance.
Without seeing the code and the execution plans, it's hard to say. But, a few points. If you have added JOINs to your views, you may be seeing execution plan timeouts caused by the added complexity of views joining to views which, internally, already have a number of joins defined. This is a very common code smell. Adding NOLOCK hints all over the place is absolutely not a query tuning mechanism. In fact, it's a sure way to hit data issues caused by page splits leading to missing data or data duplication. Please stop using NOLOCK in this manner. Third, the use of the word "basically" when describing the indexes and tables is an indicator. If the constraints, indexes and keys have changed, you will see differences in the execution plans which could be leading to poor performance. Finally, have you checked to ensure your statistics are up to date? Out of date stats can certainly cause poor performance.
The views are simple select statements. no top or order by clauses and no joins within the views. the views are used for compatibility between an old home grown etl method and a new vendor supplied one. Both pointed to the same underlying data system. so the difference between the old table structure and the new one is minimal however there are ordinal differences and some table names and field names are different. hence the need for the views. A test query pulled out of one of our external systems will run in production with the old process in about 3 sec and takes 25 secs in the test system with the views.
Pictures of execution plans are not execution plans. The important information is all contained within the properties of the operators. Other than the fact that you're getting a lot of scans and late filtering, I can' tell you much else about the queries or what's causing them to be slow. If the plans are identical, then performance should be the same. If it's not there has to be an external cause such as blocking or some other resource contention.
Execution plan
Execution plan 2
17 People are following this question.
