Webcast on SQL Optimization for Ms Dynamics NAV

2»

Answers

  • Waldo
    Waldo Member Posts: 3,412
    Ok, Mark, looking forward to it ... meanwhile ... see you in Plopsa :mrgreen:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Yes! Everybody, next sunday will be MiBuSo Plopsa day! :mrgreen:

    See you in Belgium \:D/

    (PS: This is not a SQL Workshop location)
    Marije Brummel | Freelance Business Central Specialist
  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    Hynek Muhlbacher wrote:
    N.B. I think that I have done two posts so far breaking all rules not to advertise business here... Sorry, perhaps somebody ban me from here please?
    So you are asking for an invoice, right ;-)
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • NavStudent
    NavStudent Member Posts: 399
    Ok ran it with closing date.

    setrange(Date,010107D,CLOSINGDATE(123107D));
    SELECT SUM("s2") FROM "NavisionSP2$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=? AND ((f3>=? AND f3<?)))


    setrange(Date,010107D,123107D);
    SELECT SUM("sum1") FROM (SELECT  SUM("s2") AS "sum1" FROM "NavisionSP2$50000$0" WITH (READUNCOMMITTED)  WHERE (bucket=? AND ((f3>=? AND f3<?))) UNION ALL SELECT  SUM("s2") AS "sum1" FROM "NavisionSP2"."dbo"."NavisionSP2$50000$0" WITH (READUNCOMMITTED


    It looks like you should set closing date in your filter for dates?
    Will this have perfomrance Improvement? Looks like the second statement does a Union whereas the first doesn't.
    my 2 cents
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    What is this 50000 table you have created? In your first example you used the item ledgers.
    Marije Brummel | Freelance Business Central Specialist
  • NavStudent
    NavStudent Member Posts: 399
    Yes I created a 50000 table to play and learn. With Line No. as PK and Date and amount field.

    Something else I just saw that was mentioned in webcast was example of table that can gain performance. "Warehouse Activity Line" was one of them.

    Anyways. Another tip I want to add is that. Since 5.0 has sql performance changes already added to the object, if you want to improve performance, Just compare the 5.0 table keys to 4.0. They've added different SQL Index to the keys and have disabled MAINSQL Index on several keys.
    Make sure that you don't change Navision keys, just the sqlindex.

    This can be very helpfull.
    my 2 cents
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    And who do you think has done that. :mrgreen:
    Marije Brummel | Freelance Business Central Specialist
  • NavStudent
    NavStudent Member Posts: 399
    I would have assumed somebody from Navision, but based on your question, it looks like the person is not from Navision.

    So my final answer as they would say in jeopardy. Who is Hynek?
    my 2 cents
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    To completely finalise this advertisement.

    Hynek is manager of SQL Perform Ltd, a company specialised in SQL tuning for NAV.

    The answer to your question is yes, even MS is hiring him for his knowledge.

    Please look at www.sqlperform.com
    Marije Brummel | Freelance Business Central Specialist
  • NavStudent
    NavStudent Member Posts: 399
    two more question. In his demo he did something to sql profiler that made it stop going to the last line. what was that?


    Also how much performance degradation is there when running sql profiler on a database? Is it noticeable. If lets say there are 20 people loged in?
    my 2 cents
  • NavStudent
    NavStudent Member Posts: 399
    ok I've now started query analyzer and looked at the following sql statement
    SELECT  *,DATALENGTH("Picture") FROM "NAVPROD"."dbo"."Jason Pharm$Contact" WHERE (("Name" LIKE 'test')) AND  "No_">'C100020' ORDER BY "No_" OPTION (FAST 10)

    There are two keys on Contact table
    "No."
    "Name","No." <---- new key added by me.

    Based on execution plan both keys have 50% percent chance for the above.


    I added the key because users constantly running this query on production.



    In SQL the query analyizer shows the following code
    SELECT  *,DATALENGTH("Picture") FROM "NAVPROD"."dbo"."Jason Pharm$Contact"  WHERE (("Name" LIKE @P1)) ORDER BY "No_" OPTION (FAST 10)
    I can't tell what P1 is and P2. Is there a way I can find out?

    If I change the value of P1 and P2 I get different percange 68% - 32%.
    The Primary clustered key wins most of the time.

    In query analyzer this shows a duration of 700 ms.
    There are 150K Contacts records in the table.

    is 700 ms too high? Will the new key bring any benefits if I add it to production?

    Users are constantly searching for contacts on contact card.
    my 2 cents
  • ara3n
    ara3n Member Posts: 9,258
    NavStudent wrote:
    two more question. In his demo he did something to sql profiler that made it stop going to the last line. what was that?

    Click on window, uncheck autscroll.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories