At 11:59 PM 23/11/2004 +0000, you wrote:

> > I have just solved the problem by doing the following ....
> >
> > ORIGINAL SQL
> >
> > SELECT SupOrdrRef, OrderNo, OrderDate, Comment, DelAddr, PtdFlag,
>sEntityRef
> > FROM SupplyOrder WHERE EntityRef = 8 AND SupOrdrRef IN (SELECT
>DISTINCT SupOrdrRef FROM SupplyLine WHERE SupTranRef IS Null AND
>PackSlpRef IS Null AND CancelFlag = 'F' );
> >
> > FIX
> >
> > Create a View
> >
> > CREATE View TEST2 (SupOrdrRef) AS
> > SELECT DISTINCT SupOrdrRef FROM SupplyLine WHERE CancelFlag = 'F'
>AND SupTranRef IS Null AND PackSlpRef IS Null
> >
>
>
>Just be aware that views don't seem to use underlying table indexes
>even if they are available.

Not true. If the view outputs indexed columns from the underlying tables,
then the indexes are used. Read on...

>We found an issue a while back where some
>reports openned within seconds at our office, but onsite where there
>was a lot more data, they were sluggish.
>
>Running IBPlanalyzer, you can see that any query on a view wont take
>advantage on an index on a field of that view, and so it suffers big
>performance penalties with lots of data.

Where the underlying fields' indexes become unavailable is where you define
views that convert natural columns into derived columns, by way of
expressions and, by extension, any SQL constructs by which you separate the
output from the underlying data, such as DISTINCT and GROUP BY.

>We now use stored procedures
>for a lot of things, as they can be made to use the underlying index.

Internally, yes. Searched selects, joins, updates, etc. will use indexes
during execution of the SP. But indexes are not used on the
output...hence, don't design selectable SPs with the intention of using
WHERE or ORDER BY clauses as part of the invocation call. Use input
parameters for determining search criteria; and perform ordering as part
of the execution of the SP.

./heLen