Index Hinting

company
company Member Posts: 89
edited 2010-11-02 in SQL Performance
Hi All,

Does Index hinting work in NAV 2009 Sp1 with Sql Server 2008.

For Example see below:

GLEntry.SETCURRENTKEY("G/L Account No.","Posting Date");
GLEntry.SETRANGE("G/L Account No.",'2910');
GLEntry.FIND('-');

the output in the profiler with index hint as below
(IndexHint=Yes;Company=;Table="G/L Entry";Key="G/L Account No.","Posting Date";Search Method="-+";Index=2)
was:

SELECT * FROM "TestHint"."dbo"."CRONUS International Ltd_$G_L
Entry" WITH (READUNCOMMITTED, INDEX("$1")) WHERE (("G_L Account
No_"=@P1)) ORDER BY "G_L Account No_","Posting Date","Entry No_"
','2910'

but when i have checked in 4.0 Sp3 or 5.0sp1 with the same index hint settings as above the output was:

SELECT * FROM "TestHint"."dbo"."CRONUS International Ltd_$G_L
Entry" WITH (READUNCOMMITTED, INDEX("$2")) WHERE (("G_L Account
No_"=@P1)) ORDER BY "G_L Account No_","Posting Date","Entry No_"
','2910'

can i know what have been changed with the index hinting
in the previous versions(4.0 Sp3 or 5.0sp1) compared to Nav 2009 Sp1

Thanks in Advance

Comments

  • kriki
    kriki Member, Moderator Posts: 9,121
    1) check the indexes in the table to see if your second index is correct.
    2) why are you using indexhinting? It should be the last option. Are you sure you do regular indexrebuilds (or at least update your sql statistics).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • company
    company Member Posts: 89
    Hi kirki,

    thanks for the reply .

    1>Second Index exists in the G/L Entry table.
    2>We do the Rebuild Index and Update statistics regularly.
    We wanted to use Index Hinting to Increase the Performance on the List form which
    is doing the clustered index scan.

    We just checked with the newer build of NAV 2009 SP1 and found that the performance increased a lot
    in the List forms,so will not use Index Hinting,but want to know the changes and how this works in new versions.

    Thanks in Advance.
  • kriki
    kriki Member, Moderator Posts: 9,121
    I think some small explanation is necessary:

    With the old version of NAV you might have hit the parameter-sniffing problem. It is a problem that arose with SQL2005.

    With SQL2008, a new hint came in to being to avoid parameter-sniffing (in SQL2005 there were ways but not really good ones) in a correct way and NAV2009SP1 is using that hint when on SQL2008.

    Microsoft has been working a lot on NAV-on-SQL performance and out-of-the-box, NAV is doing quite well for performance (but for us, performance tuners, it has become a more difficult to tune a database that needs tuning).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories