Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. 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 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)
share|improve this question
I can tell you that views calling other views will be a performance problem in SQL Server as they have to fully materialize the lower views and they can't be indexed. Your problem is your designer thinnks like an OOP programmer and that is a very ineffective way to design databases and acces to the data. – HLGEM Jan 20 '11 at 15:10
@HLGEM it makes sense. – Zzz Jan 20 '11 at 16:01
up vote 4 down vote accepted

Have you considered building a datamart instead? Maybe it's what your colleague already did?

It highly depends on your specific case, but I understand you can't describe your whole business in your question.

If you are serious about that, I recommend you this great book which not only describe how to do it, but explain deeply all the problems you could encounter in such situations.

The Data Warehouse Toolkit

alt text

Check out others Ralph Kimball books as well.

share|improve this answer
+1 to the datamart concept and I think @igor has already identified the starting points for that, analysing your internal existing data structure and its relationships vs what external entities require is a good segregation to have. – Martijn Verburg Jan 20 '11 at 11:10

I think the former employee has been trying to create a logical view on the data that is separate from the physical representation.

When clients are attached to Views and/or stored procedures you have some room to refactor the physical representation without the clients requiring any modifications.

Of course this logical layer adds some indirection and may cost extra processing time. However this may still be worthwhile from a maintainability viewpoint though. Being able to juggle with tables and relationships below this logical layer can also help when dealing with performance issues.

Sometimes being slower is not necessarily a problem, is it still fast enough?

share|improve this answer

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.