Hi Helen

I have the following views setup that fix my performance problem.

CREATE VIEW vwSupplierIncompleteOrders (SupOrdrRef) AS
SELECT DISTINCT SupOrdrRef FROM SupplyLine WHERE CancelFlag = 'F' AND SupTranRef IS Null AND PackSlpRef IS Null^


CREATE VIEW vwAllIncompleteSupplierOrders (SupOrdrRef, OrderNo, OrderDate, Comment, DelAddr, PtdFlag, sEntityRef, EntityRef) AS
SELECT SupplyOrder.SupOrdrRef, OrderNo, OrderDate, Comment, DelAddr, PtdFlag, sEntityRef, EntityRef
FROM SupplyOrder
JOIN vwSupplierIncompleteOrders ON SupplyOrder.SupOrdrRef = vwSupplierIncompleteOrders.SupOrdrRef^

In my code I do something like the following..

SELECT SupOrdrRef, OrderNo, OrderDate, Comment, DelAddr, PtdFlag, sEntityRef
FROM vwAllIncompleteSupplierOrders
WHERE EntityRef = 8
ORDER BY OrderNo;

There is an index on EntityRef in the underlying data. I am thinking about putting one on OrderNo. From what I understand of your email this might be beneficial ? By using these two views instead of 1 SQL my performance has improved from 3 sec to 35 ms. Seems pretty good !


Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com
Wild Software Ltd

----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Wednesday, November 24, 2004 2:39 PM
Subject: Re: [firebird-support] Re: SQL Performance problem


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



Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]