Thread: Views of views, complexity and speed.
Hello list.
I have a question about complexity of queries and performance. I only
look for qualified opinions and comments.
In my database I have a pool of tables and views with rules for
inserting, updating and deleting.
In order to avoid complexity, some views looks in other views and
join them for getting data.
I see queries against that views result slower than queries against
plane tables or simple views by an order of magnitude (when not two).
My question is: if I would make complex views looking in plain tables
instead of other views, could I gain speed with the cost of more
difficult maintainability and readability?
Sorry for lazy data and arguments. If any of you think that detailed
tables and views may help, i can send them without problem.
Thanks for any opinion.
David
bombadil@wanadoo.es wrote:
> Hello list.
>
> I have a question about complexity of queries and performance. I only
> look for qualified opinions and comments.
>
> In my database I have a pool of tables and views with rules for
> inserting, updating and deleting.
>
> In order to avoid complexity, some views looks in other views and
> join them for getting data.
>
> I see queries against that views result slower than queries against
> plane tables or simple views by an order of magnitude (when not two).
>
> My question is: if I would make complex views looking in plain tables
> instead of other views, could I gain speed with the cost of more
> difficult maintainability and readability?
>
> Sorry for lazy data and arguments. If any of you think that detailed
> tables and views may help, i can send them without problem.
Asking for qualified opinions and comments "only" and then
beeing lazy on data and arguments, tztztz ... man!
The question I have is what do you really compare? You said
"looking in plain tables instead of other views". Does that
mean your query is faster when you build one big view against
all the base tables instead of cascaded views, or what? What
is the performance difference if you instead of using the
cascaded views query all the base tables in a big join
directly?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
El martes 05 de febrero, Jan Wieck escribió:
> bombadil@wanadoo.es wrote:
> > In order to avoid complexity, some views looks in other views and
> > join them for getting data.
> >
> > I see queries against that views result slower than queries against
> > plane tables or simple views by an order of magnitude (when not two).
> >
> > My question is: if I would make complex views looking in plain tables
> > instead of other views, could I gain speed with the cost of more
> > difficult maintainability and readability?
> >
> > Sorry for lazy data and arguments. If any of you think that detailed
> > tables and views may help, i can send them without problem.
>
> Asking for qualified opinions and comments "only" and then
> beeing lazy on data and arguments, tztztz ... man!
Emmmmm, sorry 0:)
> The question I have is what do you really compare? You said
> "looking in plain tables instead of other views". Does that
> mean your query is faster when you build one big view against
> all the base tables instead of cascaded views, or what? What
> is the performance difference if you instead of using the
> cascaded views query all the base tables in a big join
> directly?
Your comment resumes very well my essential question. I only want to
know if there is a reason for thinkink that a cascade of views can be
slower than a complex view that includes all tables. Is the planner
well tuned for working with this complex cases (cascade of views)?
Actually I am making experiments with all this stuff. Better idea
than sending a lazy question in the list.
Sorry again, and thanks for interest.
Greets.
David
bombadil@wanadoo.es wrote:
> El martes 05 de febrero, Jan Wieck escribió:
> > The question I have is what do you really compare? You said
> > "looking in plain tables instead of other views". Does that
> > mean your query is faster when you build one big view against
> > all the base tables instead of cascaded views, or what? What
> > is the performance difference if you instead of using the
> > cascaded views query all the base tables in a big join
> > directly?
>
> Your comment resumes very well my essential question. I only want to
> know if there is a reason for thinkink that a cascade of views can be
> slower than a complex view that includes all tables. Is the planner
> well tuned for working with this complex cases (cascade of views)?
>
> Actually I am making experiments with all this stuff. Better idea
> than sending a lazy question in the list.
In principle the penalty for cascading views should be
negligible, because the resulting queryplan after rewriting
ought to be comparable to what you would have to write as
query yourself, if you wouldn't have views at all. So after
some recursion in the rewriter, the planner and optimizer
should face the same join problem.
As long as your not gonna write triggers to maintain the
information you want from the views in plain tables, the
answer is that there is no significant performance
difference.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
El martes 05 de febrero, Jan Wieck escribió:
> > In order to avoid complexity, some views looks in other views and
> > join them for getting data.
> >
> > I see queries against that views result slower than queries against
> > plane tables or simple views by an order of magnitude (when not two).
> >
> > My question is: if I would make complex views looking in plain tables
> > instead of other views, could I gain speed with the cost of more
> > difficult maintainability and readability?
> >
> > Sorry for lazy data and arguments. If any of you think that detailed
> > tables and views may help, i can send them without problem.
>
> Asking for qualified opinions and comments "only" and then
> beeing lazy on data and arguments, tztztz ... man!
Emmmm, sorry 0:)
I know that message was very inappropiate.
> The question I have is what do you really compare? You said
> "looking in plain tables instead of other views". Does that
> mean your query is faster when you build one big view against
> all the base tables instead of cascaded views, or what?
Now I am experimenting with this. It is what I should have done before
asking lazily in list.
But...
> What is the performance difference if you instead of using the
> cascaded views query all the base tables in a big join
> directly?
This is real question in my mind. Do it exist any reason for views in
cascade being slower than a complex view with all tables joined? Is it
the planner well tuned for views in cascade?
I will continue making experiments. In fact, i have win a few seconds
in some of my views.
Sorry again for laziness (and my pathetic english) and thanks for
your interest.
Greets.
David