Client Monitor in NAV 2009 R2

navuser1

Sir,

The below mentioned code is found during the performance checking of a NAV Classic report but I have never found the order by/shorted by the key of "Posting Date","No." in report designer.

How does it come in sql query ?

SELECT TOP 501 * FROM "CompanyName$Sales Invoice Header" WITH (READUNCOMMITTED)   WHERE (("Posting Date">={ts '2013-01-01 00:00:00.000'} AND "Posting Date"<={ts '2013-02-28 00:00:00.000'})) ORDER BY "Posting Date","No_" OPTION (OPTIMIZE FOR U)

matteo_montanari

navuser1 wrote:

Sir,

The below mentioned code is found during the performance checking of a NAV Classic report but I have never found the order by/shorted by the key of "Posting Date","No." in report designer.

How does it come in sql query ?

SELECT TOP 501 * FROM "CompanyName$Sales Invoice Header" WITH (READUNCOMMITTED)   WHERE (("Posting Date">={ts '2013-01-01 00:00:00.000'} AND "Posting Date"<={ts '2013-02-28 00:00:00.000'})) ORDER BY "Posting Date","No_" OPTION (OPTIMIZE FOR U)

Hi

You are on SQL 2008 or newer (flag OPTIMIZE FOR UNKNOW), you changed the cache recordset on File->database->alter->Advanced to 500 records (SELECT TOP 501) and you did a FINDSET(FALSE) (without cursor and without locking)

I think you have somewhere a "SETCURRENTKEY("Posting Date");" because "No_" (primary key) is automatically added by NAV to all secondary keys.

Matteo

navuser1

We'r using SQL Server 2008 R2.
The default record set is 500.

Here FINDSET is used to loop through a set without updating/locking it.

What could be reason for the slower performance of the report ? :-k

EDITED :
We redesign the Classic Report and the slow performance issue is gone.