Thanks Helen

We will look at doing it different if we can, we were hoping for a magic bullet :)

Sorry about the off-topic ness of the email, I wasn't thinking clearly.

Rob Martin
Software Engineer

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

----- Original Message -----
From: Helen Borrie
To: IBObjects@yahoogroups.com
Sent: Monday, August 30, 2004 12:23 PM
Subject: Re: [IBO] Performance issues with Views


At 11:03 AM 30/08/2004 +1200, you wrote:

>Hi All
>
>We have a number of Views which are unions on two tables. i.e.
>
>AllInvoiceLines is a union of InvoiceLines and HistoricInvoiceLines.
>
>These views are, mainly, used for reporting. However we have a number of
>reports (crystal 8.5) that now run incredibly slowly. We have traced the
>issue too reports the join two (or more) views to each other. Extracting
>the SQL from Crystal and running it directly to Firebird shows that the
>SQL takes tens of minutes to run. The data being used is not large.
>
>Is this problem caused by views not having indexes etc? Is there a way to
>improve the performance of viewings in JOINs ?

Off-topic for the IBO list, but..

No, not the way you're doing it. Once you create a view that is a union,
all references to indexes disappear because the abstraction behind the
cardinality of the index key columns is gone. Joining two unioned sets is
about the slowest thing you can do in SQL. You have two flat sets that
must be created in memory FIRST and then joined across the board by walking
the right-hand set for *each* match from the left side of the join to the
right

In cases where you have to join unioned sets, one approach is to limit the
sizes of the union sets as early as possible in the game and let a stored
procedure do the walking in a FOR SELECT loop. Write a stored proc that
parameterises the two unions. If ordering is needed, choose the set that
dictates the ordering as the lefthand set. Let all of the selection and
ordering happen *before* the walking begins, i.e. design the output in such
a way that the invocation of the SP does not need WHERE or ORDER BY clauses.

The other approach, of course, is to flip the logic and do the joining in
the views and perform the union at run-time.

Helen






>Rob Martin
>Software Engineer
>
>phone 03 377 0495
>fax 03 377 0496
>web www.chreos.com
>Wild Software Ltd
>
>[Non-text portions of this message have been removed]
>
>
>
>
>___________________________________________________________________________
>IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
>___________________________________________________________________________
>http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>keyword-searchable FAQ, community code contributions and more !
>Yahoo! Groups Links
>
>
>
>



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !


Yahoo! Groups Sponsor
ADVERTISEMENT





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

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

b.. To unsubscribe from this group, send an email to:
IBObjects-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]