Update Analysis View slow after upgrade to SQL 2005

plaasjapie

Hi All,

I have an interesting problem. ](*,) Our customer upgraded their NAV 5.0 SP1 to run on SQL 2005 instead of SQL 2000. Every month they post 6000 invoices which use to take 26 minutes and now it takes days. The problem is that after every invoice is posted an analysis view is update - standard NAV code. For now they have switched the auto update off. The G/L Entry table contains 13,318,238 entries. I know it is not best practice to have the Auto Update switched on.

The following line of code takes 30 seconds to run on SQL 2005 (also SQL 2008 with NAV 2009 R2 client) but milliseconds on SQL 2000: Codeunit 410 (Update Analysis View) - Function - Update Entries

UNTIL (GLEntry.NEXT = 0) OR (LedgEntryDimEntryNo = 0);

The actual line that I think that is causing the problem is the line before:
GLEntry.SETRANGE("Entry No.",LedgEntryDimEntryNo,MaxNumber);

SQL code that NAV code runs:
SELECT * FROM "SQL 2005 Test$G_L Entry" WITH (UPDLOCK) WHERE (("Entry No_">=0 AND "Entry No_"<=2147483647)) AND (("G_L Account No_">='2000' AND "G_L Account No_"<='9999') AND ("G_L Account No_"<>'')) AND "Entry No_">13334454 ORDER BY "Entry No_"

If I run the above SQL Code in SQL directly it is fast.

The three databases reside on the same hardware with the same memory allocated and optimisation ran.

If I create a G/L Account key on the G/L Entry table in NAV it makes no difference. If I create a non-clustered non-unique SQL Index on the G/L Entry table with the Entry No. as an Indexed Key column and add the G/L Account No. as an Included Column the posting reduces to 2 seconds. Still slower than SQL 2000 but much faster than 30 seconds.

I cannot get a NAV key to create a SQL Key as above i.e. can't get NAV to create an Include Column key. Don't want to create the key in SQL because the NAV backup won't include it.

Questions:
- Why is it slower on SQL 2005 and SQL 2008.
- Any ideas to make it faster other than rewrite the standard NAV code.

Thank you all.
Regards

David_Singleton

Are these old objects (3.7 or 4.0)?

plaasjapie

5.0 SP1 Objects. Checked and the object is the same in NAV2009.

Thanks

David_Singleton

Then just fix the code.

plaasjapie

Thanks but I would like to understand why.

Marije_Brummel

There are loads of posts on the forum about the differences between SQL2000 and newer versions.

If you are running on SQL2005 it is very important to have all the latest servicepacks and hotfixes for both NAV and SQL. SQL2005 was, when it was released, the worst combination ever with NAV.

Can't you upgrade directly to SQL2008?

plaasjapie

Thanks but already upgraded to NAV2009R2 client and SQL2008 and same issue. Code is the same between 5.0 SP1 and NAV2009 so no point in upgrading code.

imhoteph

I tested posting yesterday, because we had some problems about blocked users and slow posting some invoices. Finally the code in CU410 is horrible in replacing WHERE clause for SQL query "on-fly".

This lines:

IF LedgEntryDimEntryNo < MaxNumber THEN
    GLEntry.SETRANGE("Entry No.",LedgEntryDimEntryNo,MaxNumber);

caused changing range to 0..MaxNumber, so all G/L Entries match on GLEntry.NEXT = 0

To solve use this: http://www.mibuso.com/forum/viewtopic.php?p=270695#p270695