Optimize Code - Terrible performance.

nverma

Hi Experts

I need your guidance. One of our clients wants to control what Contact cards the user can see. Each contact card is associated with certain Programs. I created a new table (Program Relationship) to keep track of different programs a contact is associated with. Also, I created a new form called UserSetup Line, where the admin can define the programs that the user will have access too.

I wrote the following code on Form- OnOpenForm trigger.

//Gives me a list of all the Programs that User has access too.
UserSetupLine.SETRANGE("User ID", USERID);
IF UserSetupLine.FINDSET THEN
BEGIN
  REPEAT
    IF AllUserProgram = '' THEN
      AllUserProgram := UserSetupLine."Program"
    ELSE
      AllUserProgram := UserSetupLine."Program" + '|' + AllUserProgram;

  UNTIL UserSetupLine.NEXT=0;
END;

//Gives me a list of All Programs of the contact
IF Contact2.FINDSET THEN
BEGIN
  REPEAT
    ProgramRelationship.SETCURRENTKEY(Type,"No.", "Program");
    ProgramRelationship.SETRANGE(Type, 'Contact');
    ProgramRelationship.SETRANGE("No.", Contact2."No.");
    IF ProgramRelationship.FINDSET THEN
    BEGIN
      REPEAT
        IF AllContactProgram = '' THEN
          AllContactProgram := ProgramRelationship."Program"
        ELSE
          AllContactProgram := ProgramRelationship."Program" + '|' + AllContactProgram;

      UNTIL ProgramRelationship.NEXT =0;

      //Intersection between AllContactProgram and AllUserProgram
      ProgramRelationship.SETCURRENTKEY("No.", "Program");
      ProgRelationship.SETRANGE("No.", Contact2."No.");
      ProgRelationship.SETFILTER("Program", '(' + AllUserProgram + ')' + '&' + '(' + AllContactProgram + ')');
      IF ProgRelationship.FIND('-') THEN
      BEGIN
        //Temporary Contact variable to store all the Contacts that match/can be seen by the user.
        TempContact.INIT;
        TempContact."No." := Contact2."No.";
        TempContact.INSERT;
      END;
    END;
  UNTIL Contact2.NEXT=0;
END;

Form - OnFindRecord

TempContact.COPY(Rec);
RecordFound := TempContact.FIND(Which);
Rec := TempContact;
EXIT(RecordFound);

Form - OnNextRecord

TempContact.COPY(Rec);
Found := TempContact.NEXT(Steps);
Rec := TempContact;
EXIT(Found);

This code works perfectly, but the performance is terrrible. It takes almost an min to open the Contact card. I am not sure how to make it any better. I tried creating SETCURRENTKEYS but all the filters that I am using are part of the Primary Key.

Any suggestions??

NixPt

Is not possible only to apply the filter

ProgRelationship.SETFILTER("Program", '(' + AllUserProgram + ')' + '&' + '(' + AllContactProgram + ')');

Then you should get all contacts without to need to loop each one.

nverma

The reason I cant do that is because each contact will be associated with different programs. So thats why I have to determine the Contact program values first and then try to find a intersect between Contact programs and User permission programs.

I had an idea, but I am not sure if this would work or even if it is possible. As soon as user opens NAV, I can run the code in the background and prefetch the filtered Contact list that the user should see so by the time user gets to Contact cards, the filter is already applied. Is this possible. If yes, how?

Juha

You can try to add a flowfield which calculate if there are any ProgRelationship records within a filter to the Contact table and filter on that.

{ 50000;  ;Program Filter      ;Code20        ;FieldClass=FlowFilter }
{ 50001;  ;Program Exists      ;Boolean       ;FieldClass=FlowField;
                                               CalcFormula=Exist(ProgRelationship WHERE (Type=CONST(Contact),
                                                                                         "No."=FIELD("No."),
                                                                                         Program=FIELD(FILTER(Program Filter)))) }

OnOpen Form you should still find the AllUserProgram from UserSetupLine and then set it in "Program Filter" and filter on "Program Exists"

//Gives me a list of all the Programs that User has access too.
UserSetupLine.SETRANGE("User ID", USERID);
IF UserSetupLine.FINDSET THEN
BEGIN
  REPEAT
    IF AllUserProgram = '' THEN
      AllUserProgram := UserSetupLine."Program"
    ELSE
      AllUserProgram := UserSetupLine."Program" + '|' + AllUserProgram;
  UNTIL UserSetupLine.NEXT=0;
SETFILTER("Program Filter",AllUserProgram);
SETRANGE("Program Exists",TRUE);

and remove the code from OnFindRecord and OnNextRecord

I'm not sure it will perform better....

/Juha

nverma

Thanks Juha for your reply.

First of all, thats a genius way of doing it. =D> =D>

I tried what you suggested and the performance is a lot better. But what is happening now is that lets say I open Contact List from the Contact Card, I get the following message:

Searching the Contact table
Counter ......XXXX
Table .........XXXX
Key............XXXX

OR, lets say the system finds the 3 contact that match the user permission. When I am on the 3 contact card and I hit the Next button, system gives me the Quote that I mentioned above and the message is displayed for almost a min before I get an empty contact record.

Any ideas or suggestions as to how I can combat this?

Juha

The popup box is Navision doing a sequential search to find the next matching record. You can't get rid of it, i'm afraid. Both our solutions loops the contact table to find the matching records. Your solution do the loop the form is opened, mine is looping a small number at the time when you change to next record.

I don’t think its possible to prefetch the records in background when user log in.
How often is the user setup and ProgramRelationship changed? If it’s somehow possible to prefetch the list, you wouldn’t be able to see changes made after user logged in.

Only other solution I can think of is to make a new table with UserID and ContactNo which is updated whenever the usersetup or programrelationship is changed. You can then use this table instead of the TempContact in OnFindRecord and OnNextRecord. It will make the update slow, so its a question of what is the least annoying, I think.
/Juha

nverma

hmmmm...so there is no way around this :?: . :shock: ](*,)

Let me give saving users id and contact no. on a separate table a try.