My company's database makes available data to a lot of external applications. So I need to transform the same data to a lot of dynamic views. I can see that a former database developer had implemented many long chains of view-function-procedure call sequences to do transformation more common to all external applications. I think, this architecture and so long requests (stored proc calls a function, then function calls some view and this view based on other one and so on) are a performance problem, at least query optimizer does not resolve these issues (please confirm my guesses).
Is it a good approach? Does it cause degradation of performance? If yes, how can I reimplement objects of the database.
At this moment I see these steps to do this:
- analysis of source data structure (own data)
- analysis of all external systems (what formats does database have to provide for)
- separate views, functions, stored procs for every external subsystems (I have to avoid long chains, common to many subsystems DB objects, if it is a cause of problem)