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.
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.