Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My company's database, I am working with, makes available a 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 has implemented many long chain 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 some function, then funcs 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 good approch? Is it cause of 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)

Thank you.

share|improve this question
@Leigh Riffel sql-server 2k5, 2k8 – garik Jan 20 '11 at 14:46
up vote 4 down vote accepted

Is it a cause of degradation of performance? Nobody can really tell until you have measured it. From your description, it is certainly possible, but before I'd get going on the tasks I'd make sure if this is really the case. I'd also weigh in that reimplementing an existing setup doesn't come for free.

As for the steps to do: I'd put in place test cases that make sure that the external applications get the same data when you change something.

Lastly, I'd also get the commitment of management before you start doing that. It wouldn't be the first time that someone tries to put the fault on you if something with the database doesn't, even if you're not involved (and even if it is only marginally related to the database) just for the fact that someone heard that "this guy" is "chaning the system".

share|improve this answer
thanks for suggestions, I have backed up all old objects to compare with new ones. Yes, my changes were accepted by management, although I am disagree on any changes on "thing that works" without the serious reasons. Any way while I was doing this I have learnt the system and found other problems – garik Jan 22 '11 at 22:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.