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?
>
>
>
