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

Maria-S

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?

Slawek_Guzek

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

Juhl

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.

Maria-S

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.