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" <[email protected]> > 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: [email protected] > Subject: [RBASE-L] - RE: Turbo V-8 vs. 7.5 performance
