Super Slow Single Table...Weird Issue

Steve

I'm helping out a friend with a weird issue.

There customer table is super slow. I wrote a simple findset repeat and update a single field on the customer table from a lookup using a codeunit just to test. as shown:

IF Cust.FINDSET THEN
REPEAT
IF PayTerm.GET(Cust."Payment Terms Code") THEN
BEGIN
Cust.VALIDATE("Payment Method Code", PayTerm."Payment Method Code");
Cust.MODIFY;
END;
Window.UPDATE(1, Cust."No.");
UNTIL Cust.NEXT=0;


We timed the window updates and its easily 1.75-2.25 seconds per update and the process takes hours.

Important stuff
- Customer table 40k records
- Nav 2009sp1
- Other tables respond "with normal speed"
- Sql 2008
32gig mem
logs - striped across 4 drives
db - striped across 8 drives
Sql DB's seperate 4 drives
Same speed with out without other users

bbrown

The first thing I notice is you are updating a record retrieved with a default FINDSET. Which is a FINDSET(FALSE, FALSE). You should use a FINDSET(TRUE) when you intend to update. With a large table like this you'd probably be better with a FIND('-')

Kishorm

The other thing to check is what code you have in the OnValidate trigger of the "Payment Method Code" field - it could be this that is taking up the time.

ppavuk

Why not to investigate with Client Monitor? At least you can exclude bad keys and ugly code after running client monitor...

kriki

Try this:

PayTerm.RESET;
IF PayTerm.FINDSET THEN
  REPEAT
    PayTermTemp := PayTerm; // use a temptable for the payment terms
    PayTermTemp.INSERT(FALSE);
  UNTIL PayTerm.NEXT = 0;

IF Cust.FINDSET(TRUE,TRUE) THEN
  REPEAT
    IF PayTermTemp.GET(Cust."Payment Terms Code") THEN BEGIN
      IF Cust2.Payment Method Code" <> PayTermTemp."Payment Method Code" THEN BEGIN
             // why modifying the table if it is already ok?
        Cust2 := Cust; // best use another buffer to do your dirty work.
        Cust2.VALIDATE("Payment Method Code", PayTermTemp."Payment Method Code");
        Cust2.MODIFY(FALSE);
      END;
    END;
// DO NOT USE UPDATE HERE!  Window.UPDATE(1, Cust."No.");
UNTIL Cust.NEXT = 0;

Steve

Solved, but raised a new question.

I found a line of code that was a add-on piece of code which checks user field level permissions. So i commented that out and the process flies.


The issue that it raised is why is testing the windows access control table so slow?

Filters are roleid , userid


When you open the table its not slow, but a simple query runs slow.

***Could "maybe a slow Active Directory" slow down virtual tables?

bbrown

Could you post the code you removed? I use the "allow this only is member of role X" stuff in many situations and have never experienced a performance issue. That's not to say I just haven't run into one yet. But maybe we'll spot something.

kriki

If I remember correctly, the user name in the table is a flowfield and the security check is on that table, so it is slower.
And if you do that for (each field in) each record, it adds up.

Miklos_Hollender

Change the code so that it tests an "Allow editing customers" checkmark you create in the User Setup. Frankly the whole roles and permissions stuff is such a mess, so I rather I start with everybody being SUPER(DATA) restricted on the MenuSuite only, and when and if people go "Gaah! Leslie changed a Credit Limit to sixty billion! Can you stop him doing so?" then I add a boolean to the User Setup and a very simple short code whole thing is 5 mins.

rmv_RU

Steve wrote:

Solved, but raised a new question.

I found a line of code that was a add-on piece of code which checks user field level permissions. So i commented that out and the process flies.


The issue that it raised is why is testing the windows access control table so slow?

Filters are roleid , userid


When you open the table its not slow, but a simple query runs slow.

***Could "maybe a slow Active Directory" slow down virtual tables?

You can cache access rights. Simple implementation looks like:
1. Create single instance codeunit Security Management.
2. Inside the codeunit create a boolean variable IsInited and a function InitCodeunit - copy every data that you need into temporary tables.
3. Transfer all security checks into the codeunit functions.

ppavuk

Good approach, only one thing - admins should be aware of this customization. Normally when you change security setting for an user the changes come to play immediately, but with single instance cashing codeunit the changes will be applied only after restart of client.