Slow Only on role center

Belias

Hi everyone,
I've a pretty strange behavior here: i've tried to profile it and i identified a single, long running query, but i can't figure out what can have generated it.

Specs:
nav2009r2 build 32775
sql 2008r2

But first, a litlle explanation:
I have a list of serial numbers, and a factobox that shows every item ledger entry related to that serial no.
properties of the factbox part in my page:

subformview SORTING(Serial No.,Posting Date)
subformlink Serial No.=FIELD(Code)

if i run the page through obj designer (as explained, it's a list with a factbox list) everything works fine, but if i access the list through the rolecenter menu, the following query is fired. This query takes 30 seconds to execute, because it reads 310133 records.

SELECT TOP 1 * FROM mydb$Item Ledger Entry" WITH (READUNCOMMITTED) ORDER BY "Serial No_","Posting Date","Entry No_" OPTION (OPTIMIZE FOR UNKNOWN)

:-k I am figuring out something while i'm writing, just tell me if i'm talking s**t

"Serial No.,Posting date" key is not maintained on the item ledger entry (maintainsqlindex = false), but i have "Serial No.,Responsibility Center Code,Source No.,Location Code" key that is actually maintained. Actually, ordering by this latter key make the page works smoothly, but why sql can't see this key if i'm running the page through role center?!?!

Thanks in advance for your inputs! I hope I explained it clearly!

kine

It is interesting that there is no filter... than it reads all entries, sort them, and than it take the first one... and it seems that SQL select clustered index to read all th records, because it is much "cheaper" to read them and sort them (which needs because there is no index with same order which will solve it). OPTIMIZE FOR UNKNOWN - I think that it s used only when the page is reading the records. It means first time the page is displayed, it is trying to read the first record from all item ledger entries and display it. Try to set some default filter which will be than overwritten with the relation filter... but it is just tip, never tried that...

Belias

Yes, putting a

SETRANGE("entry no.",1,10000)

in the onopenpage of the factbox "solves" the problem, because sql fires the following query

SELECT TOP 1 * FROM mydb."dbo"."mycompany$Item Ledger Entry" WITH (READUNCOMMITTED)   WHERE (("Entry No_">=@P1 AND "Entry No_"<=@P2)) ORDER BY "Serial No_","Posting Date","Entry No_" OPTION (OPTIMIZE FOR UNKNOWN)

my conclusion is that nav tries to open the factbox like this:
1. does not apply the subformlink property
2. apply the Subformview property (but the key is not maintained and nav have to sort the entire table with an unexisting key)

----> fires a query. (long running)

3. apply the subformlink property.

----> fires a query. (fast even with an unmaintained key, because of a tiny recordset)

actually, the first query is useless, and is only fired when the main page is embedded in the role center...if you run the same page not "embedded", the process is:
1,2,3,query! That in my opinion is the correct procedure. This evening i'm installing the latest hotfix in our test environment and we'll see if it solves the problem. Otherwise, i'll report it to Microsoft.
Thanks for Now

kine

Than it looks like my initial guess hit the spot... :-) Good that it is working...

Belias

kine wrote:

Than it looks like my initial guess hit the spot... :-) Good that it is working...

yes, as always! :thumbsup:
i spent one hour trying to figure out where does that TOP 1 query was started (after giving it up :whistle: ,search the problem somewhere else and ask here)

kine

I think that the TOP 1 is specific for the FactBox, because RTC assumes that it is displaying one record only...

Belias

kine wrote:

I think that the TOP 1 is specific for the FactBox, because RTC assumes that it is displaying one record only...

maybe someone in MS have programmed a

FINDFIRST
REPEAT
UNTIL...

within factboxes

Belias

Nope, the latest HF has not solved the problem, i'm gonna report it to MS

Belias

posted on connect, "like it" if you want
https://connect.microsoft.com/dynamicssuggestions/feedback/details/689087/slow-factbox-on-rolecenter

Belias

We have isolated the problem and replicated on a cronus. it will probably be escalated as a bug (I'm waiting for confirmation about this), and hopefully hotfixed
Thanks to Duilio Tacconi of the italian CSS team for his effort and competence! =D>

In the meantime, I found a workaround to avoid the issue: set the sorting key through SETCURRENTKEY in the onaftergetrecord trigger, and remove every single sorting property (sourcetableview in the factbox and subformview in the FB part of the main page). It's weird, but this is the only way to make it work fast

IF NOT KeyIsSet THEN BEGIN
  SETCURRENTKEY("Serial No.","Posting Date");
  KeyIsSet := TRUE;
END;

PS: no, the onopenpage trigger does not work

einsTeIn.NET

Finally you found it. Well done, Mirko! These are the things you spend hours and hours on and get frustrated, but in the end the whole NAV world could participate from your work.

Belias

einsTeIn.NET wrote:

....but in the end the whole NAV world could participate from your work.

Well said! Ab-so-lutely!

Belias

MS guy told me that there won't be hotfix for this problem in the R2, but the performance issue won't happen in NAV 7.
Soooooooo, go for the workaround, for now