David,
In reviewing the four views used to produce customer_shipments_acctng
viewconsider if they can be combined into a single view using nested
INNER JOIN and nested OUTER JOIN.
I am including a real life example from on of my applications
the key to efficiency is to apply the most restrictive joins firsti.e. left to
right as to minimize amounts of data needed for each
additional join.
CREATE VIEW `RVuXpMbrDtl01` (MbrFChrLastName,MbrSortSeqName,MbrFirstName,+
MbrMiddleName,MbrLastName,MbrFullNameLFM,MbrNumber,MbrChpLetterNbr,MbrChpNbrPfxWLtr,+
OrgChapLit,MbrOrgChpLtrNbr,MbrSalutation,MbrProfTitle,MbrDearBroGreet,+
MbrNickName,MbrSigOtherName,MbrBirthDate,MbrInitiated,MbrHSGradYear,+
HighSchoolName,MbrDateDiedText,MbrCntRecClgDeg,MbrChpMailStatus,+
MbrRegAudAct,MbrRegAudTS,MbrTXHqMailStatus,MbrRegDataSource,MbrSocialNetYN,+
MbrCHAdrSource,MbrCHPhoneNbr,MbrCHCellNbr,MbrCHExtraAdrLine,+
MbrCHMainAdrLine,MbrCHCityStateLn,MbrCHCountry,MbrCHAudAct,MbrCHAudTS,+
MbrCWAdrSource,MbrCWPhoneNbr,MbrCWFaxNbr,MbrCWJobTitle,MbrCWCompanyName,+
MbrCWExtraAdrLine,MbrCWMainAdrLine,MbrCWCityStateLn,MbrCWCountry,+
MbrCWAudAct,MbrCWAudTS,MbrClgDegList,DadFullNameLFM,CalcAge) +
AS SELECT MbrFChrLastName,MbrSortSeqName,MbrFirstName,MbrMiddleName,+
MbrLastName,MbrFullNameLFM,MbrNumber,MbrChpLetterNbr,MbrChpLetterNbr,+
(IFLT(MbrNumber,0,'Org
Chapter:',NULL)),(IFLT(MbrNumber,0,MbrChpLetterNbr,NULL)),MbrSalutation,+
MbrProfTitle,MbrDearBroGreet,MbrNickName,MbrSigOtherName,MbrBirthDate,+
MbrInitiated,MbrHSGradYear,HighSchoolName,MbrDateDiedText,MbrCntRecClgDeg,+
MbrChpMailStatus,MbrRegAudAct,MbrRegAudTS,MbrTXHqMailStatus,+
MbrRegDataSource,(IFGT(MbrCntRecSocialNet,0,'Yes','No')),MbrCHAdrSource,+
MbrCHPhoneNbr,MbrCHCellNbr,MbrCHExtraAdrLine,MbrCHMainAdrLine,+
(IFEXISTS(MbrCWCity,MbrCWCity+CHAR(32),NULL)+ IFNULL(MbrCHStateUSPSCode,+
NULL,IFEQ(MbrCHStateUSPSCode,(CHAR(90)+ CHAR(70)),+
NULL,MbrCHStateUSPSCode+ CHAR(32)))+ +
IFEXISTS(MbrCHPostalCode,MbrCHPostalCode,NULL)),MbrCHCountry,MbrCHAudAct,+
MbrCHAudTS,MbrCWAdrSource,MbrCWPhoneNbr,MbrCWFaxNbr,MbrCWJobTitle,+
MbrCWCompanyName,MbrCWExtraAdrLine,MbrCWMainAdrLine,+
(IFEXISTS(MbrCWCity,MbrCWCity+CHAR(32),NULL)+ IFNULL(MbrCWStateUSPSCode,+
NULL,IFEQ(MbrCWStateUSPSCode,(CHAR(90)+ CHAR(70)),NULL,MbrCWStateUSPSCode++
CHAR(32)))+ IFEXISTS(MbrCWPostalCode,MbrCWPostalCode,NULL)),MbrCWCountry,+
MbrCWAudAct,MbrCWAudTS,(SRPL(MbrClgDegList,CHAR(94),CHAR(10),0)),+
D.DadFullNameLFM,(FORMAT(IFNULL(MbrDeathYear,+
INT((.#DATE - IFEXISTS(MbrBirthDate,MbrBirthDate,IFEXISTS(MbrInitiated, +
ADDYR(MbrInitiated,-19),.#DATE)))/365.25),MbrDeathYear - +
IYR4(MbrBirthDate)),MbrAgeFmtMask)) FROM ((((MbrRegister r LEFT OUTER +
JOIN MbrCurHomeInfo h ON r.MbrNumber=h.MbrNumber) J1 LEFT OUTER +
JOIN MbrCurWorkInfo w ON J1.MbrNumber=w.MbrNumber) J2 LEFT OUTER +
JOIN MbrClgDegAsList B ON J2.MbrNumber=B.MbrNumber) J5 INNER +
JOIN DirHighSchools S ON J5.HighSchoolNbr=S.HighSchoolNbr) J6 LEFT OUTER +
JOIN MbrDadInfo D ON J6.MbrNumber = D.MbrNumber
Jim Bentley,
American Celiac Society
1-504-305-2968
From: David Gideon <[email protected]>
To: [email protected]
Sent: Friday, August 7, 2015 10:21 AM
Subject: [RBASE-L] - Re: Creating a table using PROJECT COMMAND
<!--#yiv4822958599 _filtered #yiv4822958599 {font-family:Wingdings;panose-1:5
0 0 0 0 0 0 0 0 0;} _filtered #yiv4822958599 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv4822958599
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv4822958599
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv4822958599
{font-family:"Bradley Hand ITC";panose-1:3 7 4 2 5 3 2 3 2 3;}#yiv4822958599
#yiv4822958599 p.yiv4822958599MsoNormal, #yiv4822958599
li.yiv4822958599MsoNormal, #yiv4822958599 div.yiv4822958599MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv4822958599 a:link, #yiv4822958599
span.yiv4822958599MsoHyperlink
{color:#0563C1;text-decoration:underline;}#yiv4822958599 a:visited,
#yiv4822958599 span.yiv4822958599MsoHyperlinkFollowed
{color:#954F72;text-decoration:underline;}#yiv4822958599
p.yiv4822958599MsoAcetate, #yiv4822958599 li.yiv4822958599MsoAcetate,
#yiv4822958599 div.yiv4822958599MsoAcetate
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;font-family:"Tahoma",
sans-serif;}#yiv4822958599 span.yiv4822958599BalloonTextChar
{font-family:"Tahoma", sans-serif;}#yiv4822958599
span.yiv4822958599EmailStyle19 {font-family:"Calibri",
sans-serif;color:#1F497D;}#yiv4822958599 span.yiv4822958599EmailStyle20
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv4822958599
span.yiv4822958599EmailStyle21 {font-family:"Calibri",
sans-serif;color:#1F497D;}#yiv4822958599 .yiv4822958599MsoChpDefault
{font-size:10.0pt;} _filtered #yiv4822958599 {margin:1.0in 1.0in 1.0in
1.0in;}#yiv4822958599 div.yiv4822958599WordSection1 {}-->Thanks to all of you.
It looks like I need to work on streamlining the views. There are four views
used to produce thecustomer_shipments_acctng view. These views extract data
from five difference SQL tables. David C. Gideon Controller/Treasurer
Builders Supply, Inc. 8198 E 44th St. Tulsa, OK 74145 PH: 918-628-1211 Fax:
918-627-3710 From: [email protected] [mailto:[email protected]]On Behalf Of
Ken Shapiro
Sent: Thursday, August 06, 2015 7:21 PM
To: David Gideon <[email protected]>
Subject: [RBASE-L] - Re: Creating a table using PROJECT COMMAND Hi David,
As Razzak suggested in his previous email, check how the tables are linked in
the view and what the indices are. You can post back here for the very best of
assistance money can’t buy. J I read your post and became interested in
timing how long it takes to project a table with 31 columns and 7.7 million
rows. I have a run of the mill older i7 desktop with not the very fastest in
hard drives..not even SSDs. Time to completion was 25 seconds meaning RBASE
went at a speed of 310,400 rows/second. That is FAAAAST. A little database
tuning can go a long way to resolve some performance issues. Regards, Ken
From:[email protected] [mailto:[email protected]]On Behalf Of David Gideon
Sent: Thursday, August 06, 2015 3:41 PM
To: [email protected]
Subject: [RBASE-L] - Re: Creating a table using PROJECT COMMAND R:BASE
eXtreme 9.5 (64) Version 9.5.5.20806 Thanks David C. Gideon
Controller/Treasurer Builders Supply, Inc. 8198 E 44th St. Tulsa, OK 74145
PH: 918-628-1211 Fax: 918-627-3710 From:[email protected]
[mailto:[email protected]]On Behalf Of jan johansen
Sent: Thursday, August 06, 2015 2:34 PM
To: David Gideon <[email protected]>
Subject: [RBASE-L] - Re: Creating a table using PROJECT COMMAND What version
of R:Base?
-----Original Message-----
From: David Gideon <[email protected]>
To: [email protected]
Date: Thu, 6 Aug 2015 19:22:46 +0000
Subject: [RBASE-L] - Creating a table using PROJECT COMMAND
I am using the following command to create a table named “CUST”. “Project
cust from customer_shipments_acctng using *” This command takes from 10 to 12
minutes to complete. The table created contains 9 columns and 40,659 rows.
The view takes about 1 second to open. I have also tried “Project temporary
cust from customer_shipments_acctng using *” which takes the same amount of
time. I have run a command file and also typed it in at the R prompt with the
same results. RBASE is on a Windows Server 2008 R2 Standard Processor:
Intel® Xeon™ CPU .320 GHz Install memory: 32.0 GB Does anyone know of a
faster way to create a table from a view? Any help will be appreciated.
Thanks David C. Gideon Controller/Treasurer Builders Supply, Inc. 8198 E 44th
St. Tulsa, OK 74145 PH: 918-628-1211 Fax: 918-627-3710
