Could any one tell if there are multiple layer of views on core database of physical layer is create , how will it impanct the performance when fetching the data by reporting tool. The structure of the database look like
Core layer - phyical tables Layer A on core layer - views, - One to on mapping from Core Layer. layer B on layer A - view - one to one mapping from layer A. Layer C on layer B - Views - some are one to one mapping from A and some are with Join and union.
Reporting tool is fetching the data from layer C . Will it make any difference fetching data from layer C rather than layer A. and How will it impact the performance.
Any joins or set operations such as UNION, MINUS, EXCEPT in Layer C are going to impact performance. To what extent depends on the underlying tables that are being accessed, their data distribution, indexing, and available statistics. I would begin looking over the query plans for the problematic reports.