John
I will look at that but it appears that my Indexes are not too bad according to
the
Explain thing and Browse all from View is fast. Also I may not have much
choice on some of them.
I wonder if the Report engine sorts the tables differently or if using Vista 32
with V8 64 is part of the problem?
I really appreciate all the suggestions. This view is a bear and I would have
to redo it.
Marc
From: John Minyo II
Sent: Thursday, May 07, 2009 8:53 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: 7.5 vs V8 speed
Hello Marc,
It appears that it is time for you to check the efficiency of your table
indexes. You can check the efficiency for your database indexes by browsing the
R:BASE system table SYS_INDEXES. There are two system columns that store the
duplicate factor and adjacency factor values for all indexed columns. The
column names are SYS_DUP_FACTOR and SYS_ADJ_FACTOR.
At the R> Prompt, type:
BROWSE ALL FROM SYS_INDEXES
SYS_DUP_FACTOR is the computed duplicate factor, used by R:BASE during
retrieval to guess the fastest way to find the result set. This number is the
average number of times each value appears in the column. A number of 1.0 means
that values are never duplicated, or always unique. Zero means that the value
is unknown. The higher the number, the less efficient the index.
SYS_ADJ_FACTOR is the computed adjacency factor, used by R:BASE during
retrieval to guess the fastest way to find the result set. It is the estimate
of the probability that two rows with similar index values will be physically
located together in the .RB2 file. A higher number means more efficient
retrieval when reading rows in index order. Zero means that the value is
unknown.
The values within these two columns are computed when indexes are rebuilt or
reloaded, for example, during a PACK or RELOAD.
It is recommended that you reconsider the use of indexes with a high
SYS_DUP_FACTOR value. But, there is not an exact SYS_DUP_FACTOR benchmark for
which to warrant its value "too high". It's value is calculated based upon the
number of duplicate values that are within the column, which would be directly
related to how many rows are in the table.
A SYS_DUP_FACTOR with a value of 500 within a table containing 750 rows would
be an example of a poorly implemented index. On the other hand, a
SYS_DUP_FACTOR of 500 within a table containing 785,000 rows would be
considered productive. The SYS_DUP_FACTOR value is used by R:BASE to guess the
fastest way to find your results. The recommendation to reconsider the use of
indexes with a high SYS_DUP_FACTOR value is just that; a recommendation. It is
up to the developer to decide if they are truly taking advantage of the index,
or hurting their system's performance.
Hope that helps.
John
At 08:54 AM 5/7/2009, you wrote:
Hi Jim, Larry, Emmitt, Razzak and all
It looks the internal processing order is different in V8 compared to 7.5
Browse from the view is fast and Unload all did not make any difference
It appears I need to force RBase to sort the data in the same order as 7.5
This view is used to print this report using several Where statements.
So is there a way to do this on the Print Statement?
Thanks for the help
Marc
This is the Table order and Where clause in my VIew
FROM tran_hist t_1,ptinfo t_+
2,ptdiag t_3,ch_cod t_4,drinfo t_5,inscomp t_6 WHE+
RE (t_1.tr_type = '1') AND t_1.custnum = t_2.custn+
um AND t_1.custnum = t_3.custnum AND t_1.date_con +
= t_3.date_con AND t_1.ch_code = t_4.ch_code AND t+
_1.treat_dr = t_5.dr_num AND t_4.feesch = t_6.fee_+
sch
* there are only 2 rows in Drinfo
In 7.5 I get
R> prompt
05/07/2009 7:28 AM
SelectCost=20.53947 (OptimizationTime=0ms)
PTINFO (ColumnName=insco1,Type=F) VeryRandom Dup=11.83461 Adj=0.1472802
INSCOMP (ColumnName=COMPnum,Type=P) VeryRandom Dup=1 Adj=0.8371711
DRINFO Sequential
tran_hist (ColumnName=custnum,Type=I) VeryRandom Dup=61.73317 Adj=0.5723843
ch_cod (ColumnName=CH_CODE,Type=I) VeryRandom Dup=2.929204 Adj=0.336858
PTDIAG (ColumnName=date_con,Type=I) Random Dup=2.500381 Adj=0.9794695
8.0
C:\RBTI\RBG8\Samples\RRBYW14\tt 183
05/07/2009 7:17 AM
SelectCost=7.697846 (OptimizationTime=0ms)
PTINFO (ColumnName=insco1,Type=I) VeryRandom Dup=11.81472 Adj=0.4676692
INSCOMP (ColumnName=COMPnum,Type=P) VeryRandom Dup=1 Adj=0.8519737
tran_hist (ColumnName=custnum,Type=I) VeryRandom Dup=61.74523 Adj=0.6838978
DRINFO (ColumnName=Dr_num,Type=P) Random Dup=1 Adj=1
PTDIAG (ColumnName=date_con,Type=I) Random Dup=2.499111 Adj=0.9919748
ch_cod (ColumnName=CH_CODE,Type=I) VeryRandom Dup=2.929204 Adj=0.3655589
