Update Analysis View slow after upgrade to SQL 2005

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