Jim,
Whenever I am not happy with the performance delivered by a view I issue the
following commands:
SET 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
)
I then do
SELECT * FROM viewname
I then use
RBEDIT EXPLAIN.DAT
The contents of the EXPLAIN.DAT file have several cryptic elements and I can
not find any documentation to explain what the various parts mean.
With that in mind it shows the various steps it takes to retrieve you data.
It will state whether a sequential read of table occurs or if an index was
used. It also shows the order in which tables and data is processed. I have
sometime been surprised by the choices made. There is a field COST= I am not
sure how it is calculated or how it should b evaluated. But when a particular
COST= is way greater than the other COST= that is the retrieval I evaluate
first.
An item to be aware of is that the Optimizer is very dependent on several
fields in the SYS_INDEXES table. SYS_DUP_FACTOR, SYS_ADJ_FACTOR. These two
items are only updated when you do a PACK or a RELOAD. If you have heavy
databse activity of INSERT/DELETE in an INDEXED table (remember PRIMARY and
UNIQUE and FOREIGN keys are implemented as an index) the values can get out of
wack and cause the optimizer to produce non-optimaol results.
In any SELECT/BROWSE statement the object of the game is for the most
restrictive criteria (return the lest number of rows) to be executed first.
Also, note whenever you do a SELECT colnames|* FROM viewname WHERE ...
RBase may have to construct a large result set before it can apply the WHERE
clause conditions. Depending on the structure of your database some times it
is more efficient to CREATE a temporary view directly incorporating some or all
of your where conditions rather using a more generalized view.
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
--- On Thu, 8/14/08, Jim Belisle <[EMAIL PROTECTED]> wrote:
> From: Jim Belisle <[EMAIL PROTECTED]>
> Subject: [RBASE-L] - RE: Turbo V-8 vs. 7.5 performance
> To: "RBASE-L Mailing List" <[email protected]>
> Date: Thursday, August 14, 2008, 2:10 PM
> James,
>
> I was not in on this conversation, but your explanation
> from the RBASE
> help was very good. I have experienced the same problem in
> the past and
> was wondering if the view I made might be the problem.
> This may have
> given me the answer. I still use 6.5 mostly and am trying
> to learn 7.6.
>
>
> Jim Belisle
>
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED] On
> Behalf Of James
> Bentley
> Sent: Thursday, August 14, 2008 2:03 PM
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - RE: Turbo V-8 vs. 7.5 performance
>
> Mike,
>
> Here is explanation from Rbase Help
> "Syntax: SET MANOPT ON/OFF
> Default: OFF
>
> MANOPT disables the automatic table-order optimization that
> R:BASE
> performs when running queries. This gives maximum control
> over the order
> in which columns and tables are assembled in response to a
> query. With
> MANOPT on, R:BASE uses the order of the tables in the FROM
> clause and
> the order of the columns in the column list of the SELECT
> clause to
> construct the query."
>
> Since you are using a view (I assume from naem ProdView)
> something is
> confusing/affecting the RBase Optimizer when you use
> default MANOPT OFF.
>
> To the best of my knowledge unless you are using an large
> number of
> tables in the view running with the default is best course
> of action.
> Normally the optimizer is very good at selecting best
> method of
> retrieving data. However it is not fool proof hence the
> MANOPT ON
> switch.
>
> Sever things. If indexes are in view have you packed the
> indexes
> lately.
> Have you attempted to use MICRORIM_EXPLAIN system variable.
>
> "System variable that displays optimization
> information.
>
> 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 listed in Help
> file."
>
> If you post the code for the view perhaps we can spot why
> some problem.
>
> Remember with VIEWS and the selection of a subset of data
> from the VIEW
> you can experience significant performance derogation from
> an ill
> constructed view.
>
>
> Jim Bentley
> American Celiac Society
> [EMAIL PROTECTED]
> tel: 1-504-737-3293
>
>
> --- On Thu, 8/14/08, Dan Goldberg
> <[EMAIL PROTECTED]> wrote:
>
> > From: Dan Goldberg <[EMAIL PROTECTED]>
> > Subject: [RBASE-L] - RE: Turbo V-8 vs. 7.5 performance
> > To: "RBASE-L Mailing List"
> <[email protected]>
> > Date: Thursday, August 14, 2008, 1:24 PM
> > Just curious, when the MANOPT is ON and you say you
> get the
> > similar
> > performance. Is the same as the few seconds you say in
> > paragraph two??
> >
> > Dan Goldberg
> >
> > _____
> >
> > From: [email protected] [mailto:[EMAIL PROTECTED] On
> > Behalf Of Ramsour Mike
> > Sent: Thursday, August 14, 2008 11:12 AM
> > To: RBASE-L Mailing List
> > Subject: [RBASE-L] - Turbo V-8 vs. 7.5 performance
> >
> >
> >
> > Here's the situation:
> >
> >
> >
> > Query: Bro * from prodview where
> unit_no='1709'
> > and grade='1215'
> >
> >
> >
> > Results in a few seconds against 7.5 database with
> 490,305
> > rows
> >
> >
> >
> > Results 10+ minutes and waiting from 8.0 database
> upgraded
> > from previously
> > mentioned 7.5 database (only with 171,000 fewer
> records)
> >
> >
> >
> > All things being equal, table structures, indexes and
> view
> > structures are
> > the same between both versions.
> >
> >
> >
> > Using a SELECT statement gives decent results in both
> > versions but the
> > BROWSE just dies in V-8. Interestingly if I set
> MANOPT ON
> > then I get
> > similar BROWSE performance between both versions.
> >
> >
> >
> > Why is there such a difference between the versions
> and
> > more importantly
> > what can I do about it? Is it better to run with
> MANOPT
> > ON?
> >
> >
> >
> > Thanks for any feedback.
> >
> > Mike Ramsour
> > AK Steel Coshocton Works
> > Quality Department
> >
> > Phone/VMS: 740-829-4340
