Currently, it is turned off. The small changes already suggested have increased the speed considerably.
From: [email protected] [mailto:[email protected]] On Behalf Of Emmitt Dove Sent: Tuesday, August 03, 2010 8:55 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Slow database Charlie, Are you running with MANOPT ON or OFF? Sometimes setting it ON (to override the internal optimizer) will have beneficial effect if you've built your query in the most efficient manner. Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of Charles Parks Sent: Tuesday, August 03, 2010 09:45 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Slow database 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]<mailto:[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]> [mailto:[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]<mailto:[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?
