I have a procedure which can generate the correct result. But VERY VERY slow! (for example, even only output 2 records, it takes 3-5 mins!)

Physical view of the procedure:
Define -> report (output is “FOCUS database” , called “diary2” -> join [there are 4 tables. 3 of them I used the join of them before in other procedures. Run quickly. NOW, I only add 1 table called “diary” into the join. But VERY slow! ])
-> define -> report

I am thinking the reasons are maybe:
1. I am using “FOCUS database”
2. I defined several new fields in the table “diary”.
(“incident_id, priority” these fields were defined by me according to/from the field called “subject” )

"subject" example: "priority 1 incident 1999 process due "

Sincerely hope someone could give some help or suggestion!
Thanks!
Lisa

Performance of a report should not depend upon the type of database you are using, and specifically the FOCUS database is not slow by any means.

Performance really depends upon how the data is being accessed. As an example, if a X-referenced file has to be read from top to bottom for each record retrieved from the host, then this will cause performance issues. Are you screening on fields in the host (with or without an index if it is FOCUS), or fields in the x-reference file or on defined fields. The higher up the reporting structure you screen on real fields for the records you want the better the performance should (theoretically) be.

Now if you are screening on a table called diary, on a defined field that you created, then this would be a bad scenario.

So, what are the screening conditions you are applying, and where are they in the reporting structure?

That information would help.

A quick follow-up to Alan B's note:

1.) DEFINES are applied to each record in the data source. If you want to create new columns it is better to apply COMPUTES because these new columns are created on behalf of the retrieved matrix of data.

2.) It would be a good idea to show a sanitized version of the joins because there problem might be in your join code.

3.) A FOCUS database can have indexes (indices??) so it is best to filter on indexed columns whenever possible.

... and of equal importance, what are the types of DBMS for the other three tables to which you refer?

T

hi,
Thanks a lot!

the 1st procedure A,
then drill down to procedure B.
B runs VERY slow.


here is the code:
/////////
DEFINE FILE NOTIFY6
NEW_MESSAGE/A300=MESSAGE;
Z_COUNT/I5=1;
SEND_TO/A17=STRIP(17, SENDTO_ADDRESS, '@tx.com','A17');
PRIORITY_S/A1=EDIT(SUBJECT, '$$$$$$$$$9$');
INCIDENT_ID_S/A35=EDIT(SUBJECT, '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$99999$');
SEND_ADDR2/A250V=TRIMV('T', SENDTO_ADDRESS, 250, ' ', 1, SEND_ADDR2);
PSTART/I4=POSITV(SENDTO_ADDRESS, LENV(SENDTO_ADDRESS ,'I4'), '@',1, 'I4');
PLEN/I4=
IF PSTART NE 0 THEN LENV(SENDTO_ADDRESS, 'I4') - PSTART +1
ELSE 0;
PATTERN/A50V=SUBSTV(250, SEND_ADDR2, PSTART, PLEN, PATTERN);
NEW_SEND/A250=TRIMV('T', SEND_ADDR2, 250, PATTERN, LENV(PATTERN, 'I4'), NEW_SEND);
INC_ID_NUM/I11=EDIT(INCIDENT_ID_S);
END
TABLE FILE NOTIFY6
PRINT
NEW_SEND
PRIORITY_S
SUBJECT
BY INC_ID_NUM
WHERE SUBJECT CONTAINS 'Progress';

ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS DIARY2 FORMAT FOCUS INDEX INC_ID_NUM

/////////////////
JOIN
LEFT_OUTER HD_INCIDENT.HD_INCIDENT.INCIDENT_ID IN HD_INCIDENT TO UNIQUE
HD_INC_TXT.HD_INC_TXT.INCIDENT_ID IN HD_INC_TXT AS J10
END
JOIN
LEFT_OUTER HD_INCIDENT.HD_INCIDENT.TX_COUNTY_PK IN HD_INCIDENT TO UNIQUE
TX_COUNTY.TX_COUNTY.TX_COUNTY_PK IN TX_COUNTY AS J11
END
JOIN
LEFT_OUTER HD_INCIDENT.HD_INCIDENT.CONTACT_ID IN HD_INCIDENT TO UNIQUE
HD_CONTACT.HD_CONTACT.CONTACT_ID IN HD_CONTACT AS J12
END
JOIN
HD_INCIDENT.HD_INCIDENT.INCIDENT_ID IN HD_INCIDENT TO UNIQUE
DIARY2.SEG01.INC_ID_NUM IN DIARY2 AS J0
END
//////////////////////////////
TABLE FILE HD_INCIDENT
SUM
LOGGED_DT AS 'Logged Date'
INCIDENT_ID AS 'ID'
STATUS AS 'St'
PRIORITY AS 'Pri'
TX_ACKNOWLEDGED AS 'ACK'
Z_Short_Desc AS 'Short Description'
OWNER_REP AS 'Analyst'
OWNER_GRP AS 'Analyst Group'
Z_FullName AS 'Requestor Name'
Z_Title AS ' Title '
Z_Phone AS 'Phone'
TX_COUNTY_ID AS 'Cty Code'
COUNTY_DESC AS 'Cty Desc'
BY HIGHEST LOGGED_DT NOPRINT
WHERE PRIORITY_S EQ '&prt';
WHERE NEW_SEND EQ '&send_to';
WHERE SUBJECT CONTAINS 'Progress';
//////////////

TABLE FILE NOTIFY6
PRINT
NEW_SEND
PRIORITY_S
SUBJECT
BY INC_ID_NUM
WHERE SUBJECT CONTAINS 'Progress';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS DIARY2 FORMAT FOCUS INDEX INC_ID_NUM
///////

Sorry.
I just copied part of my code.

I think the problem is - join "diary2" with other tables.

WHERE PRIORITY_S EQ '&prt';
WHERE NEW_SEND EQ '&send_to';
WHERE SUBJECT CONTAINS 'Progress';

Now I am trying
what S.J. Kadish mentioned - using compute , instead of define.

Thanks!

the 3 from one table called diary.
PRIORITY_S , NEW_SEND are the new fields i defined.