Re: Views and Performance

Bill Bearden wrote:
>
> I have always been told that non-correlated sub-queries are executed once
> before the main query. On the other hand, correlated sub-queries are
> executed once for every row in the main query. This should be the same
> with or without a view.
>
> I have always heard that views should be slightly slower if anything. A
> little extra time should be needed to resolve 2 SQL statements (the SQL
> statement and the view), not just the one SQL statement.
>
> On your second question... If the select from the view was run directly
> after the single select statement, perhaps the data was still in the DB
> Buffer Cache when the select from the view was run (?).
>
> I would like to hear what others say. The behavior you describe is counter
> to my expectations.
>
> Bill Bearden

The first statement (uncorrelated subqueries) is true for EXISTS subqueries and for IN subqueries which return a single row which the optimiser treats as equivalent. (So if you have a primary key / uniqueness constraint, *always* declare it; otherwise te optimiser cannot make this distinction)

Correlated subqueries will be merged with the outer query block to form a join query if possible. This can be true even for an EXISTS subquery (using the optimiser's FIRST ROW logic), but many such subqueries are executed for each row returned by the outer block. However, it makes no sense to say that an EXISTS subquery is faster / slower than an IN subquery: it depends on the selectivity of each query block relative to itself, to the other query block and to the joint form.

As to whether pre-defining a view makes a difference to performance, the first point to make is that a view will be only be *necessary* if it is someting like a GROUP BY view joined to a single-row statement/view, in which case a single (join) SQL statement cannot be constructed.

In all other cases, the determining factor is convenience: I have never found *any* significant difference in performance which could not be attributed to the use or otherwise of a view causing the optimiser to choose a different evaluation stratagem. This, however, can happen, although in a perfect world, or at least in a product with a perfect optimiser, it should not.

The conclusions are:

• Always try it both ways (how much can it cost to try?)
• *Always* EXPLAIN your statements

Chrysalis. Received on Mon Feb 24 1997 - 00:00:00 CET