Execution time problem

the filtered patients are 20001, but they'll be more in the real database
20001 patients are processed in 20 seconds THROUGH CLASSIC CLIENT (a lot of time for such a small set)
but "only" 12 seconds THROUGH ROLE TAILORED CLIENT!!!!
are there more suggestions?

First of all: note that i have tried to comment also

IF NOT TBPatientSuspension.ISEMPTY THEN BEGIN

instruction without any change: if all isempties are commented, everything works in 5-6 secs; if just one is activated, the whole thing takes 20 secs

now, i'll answer to pdj

pdj wrote:

1) Disable these lines, as they have no effect: (besides taking time)
CLEAR(TBActPatientTemp);
CLEAR(TBSuspPatientTemp);

those ensures that there are no filters/dirty fields on each loop...moreover, these lines of code does not affect performance: as i said, if i comment all (and only) the isempty instructions, the process takes about 5 seconds
EDIT: yes, it's superficial because after the clear, i assign the variable, but i don't know if some developer in future will modify some of these instructions, screwing up my code in an unwanted modification :roll:

pdj wrote:

2)Make sure you have the three ISEMPTY in the optimal order. First the one that is most likely to be empty and so on.

this was my first thought, too...and the reason of why i tried to comment the isempty instructions one by one: in order to find the more selective and set it as the first.

pdj wrote:

If the RTC seems is faster than the Classic client with this code, it is most likely because the NAV server has a better connection to the SQL server than your pc has.

yes, correct, but why just one (one at a choice) isempty makes the whole process 4x slower?

pdj wrote:

But why are you shouting?

to make evidence of the 2 phrases, and activating capslock is easier than clicking underline or bold
yes, i'm lazy! :whistle:
thanks to everyone for your effort, waiting for further suggestions :thumbsup:

pdj wrote:

Belias wrote:

but why just one (one at a choice) isempty makes the whole process 4x slower?

Because the find-repeat-until-next part only makes one SQL transaction for each 20-50 records. Each isempty is an SQL transaction, so even if none of the records have an TBContactRC you have atleast 20 times as many SQL transactions.

Belias wrote:

yes, i'm lazy! :whistle:

Being lazy and curios is a great combination for developers! 8) :thumbsup:

It might help making 3 new flowfields of type exists (HasContactRC & HasSystemPatient & HasPatientTherapy) and a flowfield (DateFilter) and then set the filters directly on the main record. That might reduce the number of SQL transactions, but will for sure increase the workload on the SQL server! But are you curius enough?

i'm gonna try this solution, and i'll let you know.
NOTE: i think that here

Each isempty is an SQL transaction, so even if none of the records have an TBContactRC you have atleast 20 times as many SQL transactions.

you misunderstood my post (or i misunderstood yours): i know that isempty does a top1null even if there are no records (this is obvious). The behaviour i expected was that increasing the number of ISEMPTY instruction would have increasingly make performance worse, but this is not true, because if i comment all isempties, execution is 5 seconds; if i uncomment one "random" isempty, execution is 20 secs. If i uncomment all 4 isempties, the execution is still 20 secs :-k
ehy wait!!!while writing, a thing got in my mind...i'll let you know...thanks for now

Just because was easy to test, i tried the flowfield solution, but the result is the same with just one flowfield beign filtered on the top of the loop; i expect worse performance after adding a further flowfield as a filter of the patient table...
my only option is to investigate if i can start the loop from one of the related tables, because it can have less record than the one i'm looping up to now...otherwise i've just to hope that the resources are fair enough on the real servers :-k

//This function checks which patients have to be considered in the process, and checks whether the patient is suspended or active
//This function will take a lot of time, due to the high number of patients. it is advisable to warn the user before launching it
CLEAR(TBActPatientTemp);
CLEAR(TBSuspPatientTemp);
TBActPatientTemp.DELETEALL;
TBSuspPatientTemp.DELETEALL;
IF FNTCheckTempTable THEN BEGIN
  TBPatient.SETRANGE("Customer Type",TBPatient."Customer Type"::Patient);
  IF TBPatient.FIND('-') THEN BEGIN
    lINTProgressTotal := TBPatient.COUNTAPPROX;
    TBContactRC.RESET;
    TBSystemPatient.RESET;
    TBPatientTherapy.RESET;
    TBPatientSuspension.RESET;

    TBContactRC.SETFILTER("Starting Date",'..%1',TBFlyWheelSummTemp."Analysis Date");
    TBContactRC.SETFILTER("Ending Date",'%1|%2..',0D,TBFlyWheelSummTemp."Analysis Date");
    TBContactRC.SETRANGE("Responsibility Center",TBFlyWheelSummTemp."Responsibility Center");
    IF TBContactRC.FINDSET THEN BEGIN
      REPEAT
        lTBContactRCTemp := TBContactRC;
        IF lTBContactRCTemp.INSERT THEN;
      UNTIL TBContactRC.NEXT = 0;
    END;
    TBSystemPatient.SETFILTER("Starting Date",'..%1',TBFlyWheelSummTemp."Analysis Date");
    TBSystemPatient.SETFILTER("Ending Date",'%1|%2..',0D,TBFlyWheelSummTemp."Analysis Date");
    IF TBSystemPatient.FINDSET THEN BEGIN
      REPEAT
        lTBSystemPatientTemp := TBSystemPatient;
        IF lTBSystemPatientTemp.INSERT THEN;
      UNTIL TBSystemPatient.NEXT = 0;
    END;
    TBPatientTherapy.SETFILTER("Starting Date",'..%1',TBFlyWheelSummTemp."Analysis Date");
    TBPatientTherapy.SETFILTER("Ending Date",'%1|%2..',0D,TBFlyWheelSummTemp."Analysis Date");
    IF TBPatientTherapy.FINDSET THEN BEGIN
      REPEAT
        lTBPatientTherapyTemp := TBPatientTherapy;
        IF lTBPatientTherapyTemp.INSERT THEN;
      UNTIL TBPatientTherapy.NEXT = 0;
    END;
    TBPatientSuspension.SETFILTER("Starting Date",'..%1',TBFlyWheelSummTemp."Analysis Date");
    TBPatientSuspension.SETFILTER("Ending Date",'%1|%2..',0D,TBFlyWheelSummTemp."Analysis Date");
    IF TBPatientSuspension.FINDSET THEN BEGIN
      REPEAT
        lTBPatientSuspensionTemp := TBPatientSuspension;
        IF lTBPatientSuspensionTemp.INSERT THEN;
      UNTIL TBPatientSuspension.NEXT = 0;
    END;
    
    DLGWindow.OPEN('#1##############\@2@@@@@@@@@@@@@@@@@@@@@@@@@\',TXCProcessPatients,lINTProgress);
    REPEAT
      lINTLineNo += 1;
      IF lTMTimeProgress < TIME - 1500 THEN BEGIN           //update the dialog box every second
        lTMTimeProgress := TIME;
        lINTProgress := ROUND(lINTLineNo/lINTProgressTotal * 10000,1);
        DLGWindow.UPDATE;
      END;
      lTBContactRCTemp.SETRANGE("Contact No.",TBPatient."No.");
      lTBSystemPatientTemp.SETRANGE("Contact No.",TBPatient."No.");
      lTBPatientTherapyTemp.SETRANGE(Code,TBPatient."No.");
      lTBPatientSuspensionTemp.SETRANGE(Code,TBPatient."No.");

      IF TBPatient."Contact Status Card" <> TBPatient."Contact Status Card"::Closed THEN BEGIN
        IF NOT lTBContactRCTemp.ISEMPTY THEN BEGIN
          IF NOT lTBSystemPatientTemp.ISEMPTY THEN BEGIN
            IF NOT lTBPatientTherapyTemp.ISEMPTY THEN BEGIN
              IF NOT lTBPatientSuspensionTemp.ISEMPTY THEN BEGIN
                CLEAR(TBActPatientTemp);
                TBActPatientTemp := TBPatient;
                TBActPatientTemp.INSERT;
              END ELSE BEGIN
                CLEAR(TBSuspPatientTemp);
                TBSuspPatientTemp := TBPatient;
                TBSuspPatientTemp.INSERT;
              END;
            END;
          END;
        END;
      END;
    UNTIL TBPatient.NEXT = 0;
    DLGWindow.CLOSE;
  END;
END;

I think that i'm too addicted to temptables...anyway, this code works all the way better than the previous version (elapsed time = about 2 seconds for the same 20000 records).
What I've done:
1. set the date range on the (real) tables on which the isempty will be done.
2. populate temptables with the records resulting from point 1
3. loop the patient table and use ISEMPTY on the temptables.
4. added a nice progressbar which is always cool 8)

In this way i dropped the number of dbreads, thus decreasing a lot the data exchange between middle tier and db tier

POSSIBLE DRAWBACK: an excessive growth of the four temptables on which i do the isempty, can hang the client and eventually crash navision if the temptable reach 2GB.