| Subject | Re: [IBO] Performance issues with Views |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-08-30T00:23:28Z |
At 11:03 AM 30/08/2004 +1200, you wrote:
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
>Hi AllOff-topic for the IBO list, but..
>
>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 ?
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
>
>
>
>