question

GJRyan59 avatar image
GJRyan59 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.
sql 2008
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Views can be a bottleneck however until we can see a query and/or execution plan it's tough to tell. Can you post either and/or both of those by chance?
0 Likes 0 ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Also, the execution plans look exactly the same between querying the old ETL tables directly and the new views
0 Likes 0 ·
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.
0 Likes 0 ·
GJRyan59 avatar image
GJRyan59 answered
Execution plan

expl1.jpg (19.3 KiB)
expl2.jpg (50.9 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GJRyan59 avatar image
GJRyan59 answered
Execution plan 2

expl3.jpg (26.8 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.