Charlie,
I am a big fan of views rather then views on specially created
tables. That said the the decision is often dictated by the
application. In several of my applications I also used VIEWS of
VIEWS. I have a master table with many sub tables that have
subsets of the records on the master table. For example I have
a Home Address table and a work address table. Home and work
address do not exist for each So if I need to see a combination
of these tables I need views of views where the views are OUTER
JOIN of Master and Home address and an OUTER JOIN of Master and
Work address. THese are then OUTER JOINS. RBase at this time
does not support nester OUTER JOINS.
The key to efficency is to have the optimizer process the most
restricted select first (return smallest number of records) and
proceed to process the second most restrictive select etc.
Take advantage of the recently added INNER JOIN syntax as these
can be nested.
Sometimes rearranging the order of select statements can have a
profound effect on processing speed. To gage how the optimizer
processes your select statements created the following as a RMD
file:
ET DEBUG ON
SET VAR MICRORIM_EXPLAIN = 47
RETURN
*(MICRORIM_EXPLAIN
This variable shows the decision the optimizer made when
executing a
particular command. The optimizer results are placed in the
file
EXPLAIN.DAT. The DEBUG command must be on to use microrim_
explain.
The available options for microrim_ explain are
1 - output the table order.
2 - force optimization of joins over five tables. This
option is
ignored if microrim_fullopt is set.
4 - output the command file name and next byte offset.
8 - output the current date and time.
32 - display the sort technique used.
To use multiple options, add the option numbers together and set
the
variable to the result. For example:
SET VAR microrim_explain = (1 + 4 + 8)
SET VAR microrim_explain = 13
)
run this command then executed your views. The explanation of
how RBase optimizer processes your actions are in the file
EXPLAIN.DAT. Review this file and see if changes to your views
can improve performance. Also, I believe Razzak has and article
in FTE that discusses how to optimize SELECT statements.
When you are considering replacing views with building tables
don't forget to include the processing time to CREATE and LOAD
your temporary tables.
Jim Bentley
--- Charlie Parks <[EMAIL PROTECTED]> wrote:
>
> In my database views built on other views seem to process much
> more slowly than views based on tables. Is there anyway to
> speed up the processing of view based views or is it better to
> use table based views?
>
> View based views have the benefit as new rows are inserted
> into table then they are automatically inserted into the
> selection where as wouldn't table based views need to have the
> table dropped and reprojected each time new data is added or a
> different selection is desired?
>
> These are views based on group by statements with counts so
> there may be two or more views nested\paired together before
> arriving at the view definition that I'm wanting to use.
>
> Is there a better way to do this?
>
_________________________________________________________________
> Spell a grand slam in this game where word skill meets World
> Series. Get in the game.
>
http://club.live.com/word_slugger.aspx?icid=word_slugger_wlhm_admod_april08
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
