[RBASE-L] - RE: Turbo V-8 vs. 7.5 performance

[Albert Berry]

I have found that sometimes using a temp table and doing a primary fill 
of the restricted selection can speed some things up.

PROJECT TEMP tProdMast FROM ProdMast USING ALL WHERE (your ProdMast 
selection criteria)
CREATE INDEX tProdMast ON tProdMast (..col list here for all matching 
columns in the order you match them in the view..)

Use tProdMast in the view rather than ProdMast. You will not need the 
limiting where clause in the new view, only the linking columns.

Albert.

James Bentley wrote:
Mike,

Without seeing the actural list of items retrieved.  My first impression is internally 
RBase is constructing a 220,437 row table.  Since none of the where clause statements 
seem to restrict the number of rows returned from PRODMAST.  All your where clause seem 
to be doing is adding to or translating fields from the PRODMAST. Note the number of 
items where T1.colname is on left side of "=".

Here is your statement.
Bro * from prodview where unit_no='1709' and grade='1215' 

Not knowing the location of unit_no and grade (the true restrictive part of you 
browse statement) I can't advise you on where to make the improvement.  It 
would seem that a temporary view where these two items are incorporated in the 
view would vastly improve performance.

Don't rule out views of views as a possible solution.

Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293


--- On Thu, 8/14/08, Ramsour Mike <[EMAIL PROTECTED]> wrote:

  
From: Ramsour Mike <[EMAIL PROTECTED]>
Subject: [RBASE-L] - RE: Turbo V-8 vs. 7.5 performance
To: "RBASE-L Mailing List" <rbase-l@rbase.com>
Date: Thursday, August 14, 2008, 2:31 PM
James:

Thank you for your reply and others as well.  I had
reviewed the MANOPT
topic but have not pursued the MICRORIM_EXPLAIN path.  The
tables are
reloaded on a daily basis.

That having been said, here is the info on the PRODVIEW
view:

Tables in PRODVIEW:

T1 PRODMAST  220,437 rows, index on PRODDATE DATE column
T2 LKUPDATE    3,287 rows, index on DAY_DATE DATE column
(many-to-one)
T3 CUSTACCT    4,802 rows, index on DUNS INTEGER column
(many-to-one)
T4 LKUPWEEK      834 rows, index on SEQNUM INTEGER column
(one-to-one)
T5 LKUPUNIT      559 rows, index on FAC_TYPE TEXT (2)
column (many-to-many)
                           index on FAC_NO TEXT (2) column
(many-to-many)
                          (FAC_TYPE + FAC_NO combination is
unique)
T6 LKUP_MELTCODE 199 rows, index on MELTCODE TEXT (6)
column (many-to-one)
T7 COQ_RATES      50 rows, No indexes (many-to-one based on
COQ_YEAR,
GRADTYPE and DISPCODE)

View SELECT statement:

FROM PRODMAST T1, LKUPDATE T2, CUSTACCT T3, LKUPWEEK T4,
LKUPUNIT T5, +
LKUP_MELTCODE T6, COQ_RATES T7 +
WHERE T1.PRODDATE = T2.DAY_DATE AND T1.DUNS = T3.DUNS +
AND T1.FAC_NO = T5.FAC_NO AND T1.FAC_TYPE = T5.FAC_TYPE +
AND T1.MELTCODE = T6.MELTCODE AND T2.SUN_SAT_SEQ =
T4.SEQNUM +
AND T4.FISC_YEAR = T7.COQ_YEAR AND T6.GRADTYPE =
T7.GRADTYPE +
AND T7.DISPCODE = 'P'

I try to arrange the sequence of tables in my views in the
correct sequence
for greatest number of rows down to lowest number of rows.

Thank you much to anyone who can suggest a better way to
join the tables.

Mike Ramsour
AK Steel Coshocton Works
Quality Department

Phone/VMS:  740-829-4340
-----Original Message-----
From: James Bentley [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 14, 2008 3:03 PM
To: rbase-l@rbase.com
Subject: [RBASE-L] - RE: Turbo V-8 vs. 7.5 performance