Charlie, The table order and condition order matter only if you SET MANOPT ON. Column order doesn't matter at all.
My habit is to put all the table-to-table join conditions after other data conditions, but I don't know for sure that that makes anything more efficient. Bill On Tue, Aug 3, 2010 at 9:44 AM, Charles Parks <[email protected]> wrote: > I made that change and switched the table order around to RecentPrint, > QICharter, LinkedReports, QIParagraphs. This is the order of which table > holds the least to most amount of data. Does the column order matter in the > view definition or is just the table order? > > > > It seems to have increase the speed in my test version. The test version > though seemed to be about twice as fast as the production version before the > change. > > > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Bill > Downall > *Sent:* Tuesday, August 03, 2010 8:14 AM > > *To:* RBASE-L Mailing List > *Subject:* [RBASE-L] - Re: Slow database > > > > Charlie, > > > > Test timing your report query against the temporary view in a SELECT > statement, to determine whether it is the view or the report that got slow. > > > > In your view definition, change the last line from: > > > > AND t2.PNo = t4.PNo AND t4.StfMbr = (.vUser) > > to: > > > > AND t2.PNo = t4.PNo AND (t4.StfMbr = .vUser) > > > > Your way, any index on StfMbr will not be used by R:BASE. With the > parenthesis around the whole condition, the index will be used, and the > variable will still not be interpreted at view creation, but only at access > to the view. (I'm assuming that's why you parenthicated the variable -- > because it was being saved in the view as "t4.StfMbr = 12345" if you used no > parens.) > > > > Bill > > > > > > > > On Tue, Aug 3, 2010 at 8:56 AM, Charles Parks <[email protected]> wrote: > > I have tried other databases on the server and they seem to be acting > normally. This report started having problems about 2 weeks ago before then > it was also working okay. Editing forms and BROwsing data from the database > works okay. > > I'm using V-8 and Windows XP with a scratch setting of (SCRATCH ) > C:\DOCUME~1\cpo\LOCALS~1\Temp --SCRATCH files location > > > This is the command block to get the data for the report: > SELECT COUNT (*) + > INTO vCount + > INDICATOR viCount + > FROM SYS_TABLES + > WHERE SYS_TABLE_NAME = 'ttallog' > > IF vCount > 0 THEN > DROP TABLE ttALLog > ENDIF *(vCount > 0) > > CREATE TEMP TABLE ttALLog (QINo TEXT 7, + > RPT TEXT 5, QIName TEXT 68, Priority INTEGER, + > SeqNo INTEGER, Status TEXT 6, TeamLeader TEXT 3, + > Team TEXT 60, StartDate DATE, QITarget DATE, + > ClosedDate DATE, Client TEXT 6, hrs INTEGER, + > MgmtRptDate DATE, Categories TEXT 60, MGR TEXT 3, + > PNo INTEGER, CheckBox TEXT 3, StfMbr TEXT 3, + > Paragraph NOTE, TargetDate DATE, SortNo INTEGER, + > Precno INTEGER, ALCode TEXT 10, ParagraphPriority text 20) > > SELECT COUNT (*) + > INTO vCount + > INDICATOR viCount + > FROM SYS_TABLES + > WHERE SYS_TABLE_NAME = 'tvalqino' > > IF vCount > 0 THEN > DROP VIEW tvALQINo > ENDIF *(vCount > 0) > > CREATE TEMPORARY VIEW `tvalqino` AS + > SELECT t1.Precno,t1.ALCode,t1.SortNo,t2.Precno,t2.QINo,t2.PNo, + > t2.CheckBox,t2.StfMbr,t2.Paragraph,t2.TargetDate, + > t2.ParagraphPriority, t3.QINo,t3.RPT, + > t3.QIName,t3.SeqNo,t3.Priority,t3.Status,t3.TeamLeader,t3.Team, + > t3.StartDate,t3.QITarget,t3.ClosedDate,t3.Client,t3.afi,t3.hrs, + > t3.MgmtRptDate,t3.Categories,t3.MGR,t4.QINo,t4.PNo + > FROM RecentPrint t4,QICharter t3,QIParagraphs t2,LinkedReports t1 > + > WHERE t2.QINo = t4.QINo AND t3.QINo = t4.QINo + > AND t2.QINo = t3.QINo AND t1.Precno = t2.Precno + > AND t2.PNo = t4.PNo AND t4.StfMbr = (.vUser) > > SELECT COUNT (Precno) + > INTO vACount + > INDICATOR viACount + > FROM tvALQINo > > IF vACount = 0 THEN > SET VAR vMessage = ('There are no links for theses paragraphs.') > --CLS > --PAUSE 3 USING .vMessage > ELSE *(vACount = 0) > INSERT INTO ttALLog (QINo, RPT, QIName, Priority, + > SeqNo, Status, TeamLeader, Team, StartDate, + > QITarget, ClosedDate, Client, hrs, MgmtRptDate, + > Categories, MGR, PNo, CheckBox, StfMbr, Paragraph, + > TargetDate, ParagraphPriority, SortNo, Precno, ALCode) + > SELECT QINo, RPT, QIName, Priority, SeqNo, Status, + > TeamLeader, Team, StartDate, QITarget, + > ClosedDate, Client, hrs, MgmtRptDate, Categories, + > MGR, PNo, CheckBox, StfMbr, Paragraph,TargetDate, + > ParagraphPriority, SortNo, Precno, ALCode + > FROM tvALQINo > ENDIF *(vACount = 0) > > IF vACount = 0 THEN > SET VAR v2Err = 1 > ENDIF *(vACount = 0) > > SELECT COUNT (*) + > INTO vCount + > INDICATOR viCount + > FROM SYS_TABLES + > WHERE SYS_TABLE_NAME = 'tvlogqino' > > IF vCount > 0 THEN > DROP VIEW tvLogQINo > ENDIF *(vCount > 0) > > CREATE TEMPORARY VIEW `tvlogqino` AS + > SELECT t1.Precno,t1.QINo,t1.PNo,t1.CheckBox,t1.StfMbr, > t1.Paragraph, + > t1.TargetDate, t1.ParagraphPriority, t2.QINo, t2.RPT, t2.QIName, + > t2.SeqNo, t2.Priority, t2.Status, t2.TeamLeader, t2.Team, + > t2.StartDate, t2.QITarget, t2.ClosedDate, t2.Client, t2.afi, > t2.hrs, + > t2.MgmtRptDate, t2.Categories, t2.MGR, t3.QINo, t3.PNo + > FROM RecentPrint t3,QICharter t2,QIParagraphs t1 + > WHERE t2.QINo = t1.QINo AND + > t1.QINo = t3.QINo AND t2.QINo = t3.QINo AND t3.PNo = t1.PNo + > AND t3.StfMbr = (.vUser) AND t1.Precno NOT IN + > (SELECT DISTINCT Precno FROM tvALQINo) > > SELECT COUNT (Precno) + > INTO vQCount + > INDICATOR viQCount + > FROM tvLogQINo > > IF vQCount = 0 THEN > --CLS > --PAUSE 3 USING 'All paragraphs have at least one link.' > ELSE > INSERT INTO ttALLog (QINo, RPT, QIName, Priority, + > SeqNo, Status, TeamLeader, Team, StartDate, QITarget, + > ClosedDate, Client, hrs, MgmtRptDate, Categories, + > MGR, PNo, CheckBox, StfMbr, Paragraph, TargetDate, Precno, + > ParagraphPriority) + > SELECT QINo, RPT, QIName, Priority, SeqNo, Status, + > TeamLeader, Team, StartDate, QITarget, ClosedDate, Client, + > hrs, MgmtRptDate, Categories, MGR, PNo, CheckBox, + > StfMbr, Paragraph, TargetDate, Precno, ParagraphPriority + > FROM tvLogQINo > ENDIF *(vQCount = 0) > > > SELECT COUNT (*) + > INTO vCount + > INDICATOR viCount + > FROM ttALLog > > IF vCount > 0 THEN > IF vReportOutput IS NULL THEN > --CLS > PAUSE 3 USING 'Displaying Print Options' > CLS > EDIT USING ReportOptions > ENDIF *(vReportOutput IS NULL) > --CLS > PAUSE 3 USING 'Creating Printout' > SET VAR vReportName = 'partialqiselp' > SET VAR vWhere = 'ORDER BY qino, pno, precno' > CLS > PAUSE 3 USING 'Creating Printout.' > EDIT USING PrintEep > &vreportsyntax > ELSE > CLS > PAUSE 2 USING 'No data exists for these paragraphs.' + > CAPTION ERROR ICON attention > ENDIF *(vCount > 0) > > > > > > > ------------------------------------------------------------------------------------------------------------ > From: [email protected] [mailto:[email protected]] On Behalf Of Bill > Downall > Sent: Tuesday, August 03, 2010 7:39 AM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - Re: Slow database > > Charlie, > > Is it just the report that is slow, or are other things slow, too? > > Can you share the structure of the table -- or the view and its component > tables -- that the report is based on, and the PRINT statement? > > Bill > > > On Tue, Aug 3, 2010 at 8:29 AM, Charles Parks <[email protected]> wrote: > > I have a database (186,428,778 bytes) on a 53 GB Server with 17.5 GB free. > > I have done a rebuild. > Unload structure > Unload data > Split structure file > Rename database > Run structure file > Run data file > Run 2nd structure file > > Verified StaticDB and Recycle are turned off. > > A simple report with a note field and a few textboxes takes nearly two > minutes to print the report. > > > Is there any way that I can speed up the report? > > >
