Re: Slow running query with views...how to increase efficiency? with index?

On 28 Oct 2009, at 9:57, fox7 wrote:

> Views do not help or hurt performance. Views encapsulate complex
> queries.
>
> If you have a slow running query, the usual way to get help is to
> post:
> *) explain analyze results (most important)

You forgot to show us the most important part.

> *) the query (important)
> *) interesting tables/indexes (somewhat important)
> ----------------------
>
> These are 2 queries for example...
> The first runs with 55ms, the 2nd with views is executed in 4500ms...
> :confused:
>
> SELECT DISTINCT table_0.term1, table_1.term2 FROM TC table_0 , TO
> table_1
> WHERE table_1.term1='c' AND table_0.term2=table_1.term2
> UNION
> SELECT DISTINCT table_0.term1, table_1.term1 FROM TC table_0 , TB
> table_1
> WHERE table_0.term2=table_1.term1 AND table_1.term2='c'

If you're using a UNION you can drop the DISTINCTs, as the results of
UNION are guaranteed to be distinct. If you don't want that, use UNION
ALL instead.

> ---------Definition of tables and views involved-------------
> -- View: v2TC
> CREATE OR REPLACE VIEW v2TC AS
> SELECT DISTINCT TC.term1, TC.term2
> FROM TC
> ORDER BY TC.term1, TC.term2;
>
> -- View: v2TO
> CREATE OR REPLACE VIEW v2TO AS ( SELECT DISTINCT TO.term1, TO.term2
> FROM TO
> ORDER BY TO.term1, TO.term2)
> UNION
> SELECT TB.term2 AS term1, TB.term1 AS term2
> FROM TB;

Do you absolutely need to order the output of your views? You could
just order the results of your queries on your views instead. The way
you do it now the database needs to order results always, even if the
order doesn't actually matter to you. I suspect this is part of why
your query is slow.

Besides that, the order of your V2TO view is going to be determined by
the UNION clause anyway, as it needs to sort the results of the union
to make them unique. The order by in the first subquery of that view
can safely be removed I think.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4ae823b911071766412181!