Table Locking

camgates

We have a problem with the Job Ledger Entries form locking when uses navigate to it. We are using Nav 4.0 sp3.

The issue occurs when navigating from Jobs to Job Ledger Entries. The form has always been slow but recently its got a lot, lot worse. Sometimes, navigating to the form will be instantaneous but others you will be sitting there for several minutes with the screen frozen.

Upon investigating SQL we found we were getting PAGEIOLATCH_SH wait type on the record. During that time the disk is spinning at 100% which is whats causing the record to freeze.

We've had a look on the web and gone through this thread
viewtopic.php?t=26424&highlight=pageiolatchsh
which has given us some good ideas. The most likely 'quick fix' is to add more memory to the server. It is pretty light with only 2gb of ram, although it only has to service about 20 users.

What I was looking for was any insight into why it is one particular query that is cauing the problems. When the disk is at 100% sql is performing this query:
(@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int)
SELECT *
FROM "NavLive"."dbo"."CompanyNameHere$Job Ledger Entry"
WHERE (("Job No_"=@P1)) AND "Job No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_"<@P4
ORDER BY "Job No_" DESC,"Posting Date" DESC,"Entry No_" DESC OPTION (FAST 10)

When it unlocks it is showing this query:
(@P1 varchar(20),@P2 varchar(20))
SELECT *
FROM "NavLive"."dbo"."CompanyNameHere$Job Ledger Entry"
WHERE (("Job No_"=@P1)) AND "Job No_"<@P2
ORDER BY "Job No_" DESC,"Posting Date" DESC,"Entry No_" DESC OPTION (FAST 10)

When you run those queries directly in sql they are instantaneous every time. I thought it may be an abundance of flow fields but I don't see any on that form.

All a bit confusing.

kriki

I think you are hitting the clustered index scan with the form.
I suppose you have several 100.000 or 1.000.000 records in that table.

What I would first try is this:
For showing the entries, I wouldn't do it on the real table but a temptable:
I would read the entries from the real table, put them in a temptable and run the form on that temptable.
This is more or less the code you need:

tmpJobLedgerEntry.RESET;
tmpJobLedgerEntry.DELETEALL(FALSE);

recJobLedgerEntry.RESET;
recJobLedgerEntry.SETCURRENTKEY("Job No.","Posting Date");
recJobLedgerEntry.SETRANGE("Job No.","Job No.");
IF recJobLedgerEntry.FINDSET THEN
  REPEAT
    tmpJobLedgerEntry := recJobLedgerEntry;
    tmpJobLedgerEntry.INSERT(FALSE);
  UNTIL recJobLedgerEntry.NEXT = 0;
tmpJobLedgerEntry.RESET;
tmpJobLedgerEntry.SETCURRENTKEY("Job No.","Posting Date");
FORM.RUNMODAL(0,tmpJobLedgerEntry);

camgates

Thanks kriki
Yes we are hovering around 1000000 records in that table.

Whilst we didn't specifically take your advice you did make us think about the state of the indexes in the database. We didn't have a maintenance plan in place to rebuild them and consequently they were all over the show. We noticed an instant improvement once done.

This will probably explain why sometimes there was no delay and others there was a delay of several minutes.

Cheers

camgates

Just as an update:

Things have been a lot smoother since the rebuild. We still get the odd freeze. It no longer occurs when selecting Job Ledger Entries though. On certain records within Job Ledger Entries (not all), when we 'Navigate' the table locks up as it did previously. Interestingly, if we filter on the record we want to Navigate and THEN press Navigate it doesn't freeze at all. Take the filter off, Navigate and it freezes.

camgates

Another update:
After a month or so of relatively smooth performance the table locking flared up again. Rebuilding the indexes only made very minor improvements. It was eventually solved by upgrading the Nav Client from 4 to 5.01.

I can only assume that something that there is a bug in the 4 client somewhere.