I'm not seeing any problem here. I went into Sesame Runtime and put "Calgary" in the "City" LE - I then went to the spec manager and saved that spec under a new name. I exited Runtime, went into SDesigner, opened the report "Calgary Area Report". The original attached spec had 10 LEs specified scattered across 5 subforms. I attached my new spec to the report. I exited SDesigner and ran the report in Runtime. It retrieved the "Calgary" records and generated the report immediately.
I then investigated whether the original spec (the one with 10 LE specified, "Calgary Clients") was locking up Sesame when generating a report. It isn't. It is, however taking a very long time to generate the report. This is because all of the subforms (9 of them) in this application are set to be "relational" rather than naturally linked. To do a search across 5 relationally linked subforms, these five:
Children (763) Life Insurance (1778) GIC (644) Daily Interest (311) Investments (0)
Forces Sesame to have to run through all the records in the application (10466) to find those that match the definition type (totals in parenthesis above), and run through those to find those with matching key fields, for each and every parent record. Assuming that it cannot find any matching records (the worst case, but true in the case), that amounts to:
operations = (1624 * (10466 - 1624))
or 14,359,408 operations just to determine that none of the records qualify.
If natural linking were used instead of relational for these forms, Sesame would only have to search the subrecords that actually are children of this parent record, without having to run through all the possible children to simply determine if they are *this* record's child.
Relational linking should only be used in cases where the information in the child record is likely to appear under more than one parent. In looking at your application, I would highly recommend that you change:
Children Life Insurance GIC Daily Interest LTCare Critical Illness Investments and Disabilities
to be naturally rather than relationally linked. The information in these records must be unique to the individual parent record they are linked to - as indicated by unique information such as policy numbers. The Group Insurance form maybe better served as relational in that many individuals might fall under a single policy number. But for simplicity's sake, and given that the information held by Group Insurance is unlikely to be changed "standalone" - it may well also be better off as naturally linked.
Additionally, if naturally linked, you would be able to reduce the SBasic code scattered through this application very significantly, in that much of it is there to generate and maintain unique key fields. In each case I could find, these are being used to generate a condition identical to natural linking but with significantly more overhead.
|