NAV Query very slow, LOOP JOIN seems to be a problem

Maria-SMaria-S Member Posts: 90
Hello experts,

Here is a mistery.

I have table with 113 records, let's name it SomeEntries.
Then I insert copy-past ~5 000 records to it (I need to test the performance).

Then I create NAV Query:

Sales Invoice Header / SomeEntries - linked on No.= Document No. with option <Use Default Values if no Match>
I run the query and it takes minutes to complete. As a comparison I created SalesInvoiceHeader/SalesLines query and it runs much faster.

SQL MS Studio Actiivity Monitor shows that query is SELECT TOP 1000 ... bla-bla.. OPTION(OPTIMIZE FOR UNKNOWN, FAST 50, FORCE ORDER, LOOP JOIN).
When I try to run the query, and comment parts out, the problem seems to be LOOP JOIN. Without it it runs much faster.

It seems that problem might be that i recently added so much entries to that table. I updated statistics and even rebuild index on SomeEntries table. But this does not help.

Somebody has any thoughts what else can I do to make NAV Query work normally, please?

Answers

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2017-09-16
    Hi,

    Add a "Document Type" to your SomeEntry table, make it a part of PK, just like Document Type/Document No pair in Sales Header, then join both tables on both fields

    EDIT:

    Sorry didn't spot that you are joining Sales Invoice Header, so of course Document Type does not apply here

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • JuhlJuhl Member Posts: 724
    Also have in mind, that Query always run directly on sql, and not on cache, like rest of NAV. So if your sql server is a bit slow, it will show on queries.
    Follow me on my blog juhl.blog
  • Maria-SMaria-S Member Posts: 90
    Small update: I needed some more queries and calculations. Ended by creating SQL stored procedures, calculating all figures that I need, and calling them from NAV.
Sign In or Register to comment.