Nav 5 generated Sql Query very slow

bluecat

The following change between 3.7 and 5.0 is causing reports to run massively slower.

Navision 5 is generating the following query (90,000 reads average) :-

SELECT TOP 985 * FROM "CPNAVISION020708"."dbo"."Company$Project Job Ledger Entry"

WITH (READUNCOMMITTED, INDEX("Company$Project Job Ledger Entry$0"))
WHERE (("Job No_"='AB60015')) AND (("Posting Date">='Jan 1 1753 12:00:00:000AM' AND "Posting Date"<='Jun 30 2008 12:00:00:000AM'))

ORDER BY "Entry No_"



Where as in Nav 3.7 the query is (Only 60 reads average):-

SELECT TOP 985 * FROM "CPNAVISION020708"."dbo"." Company$Project Job Ledger Entry"

WITH (READUNCOMMITTED)

WHERE (("Job No_"='AB60015')) AND (("Posting Date">='Jan 1 1753 12:00:00:000AM' AND "Posting Date"<='Jun 30 2008 12:00:00:000AM'))

ORDER BY "Entry No_"

kine

You have enabled Index Hinting. Disable it. Check the forum and blogs for "Index Hinting" for how to disable it.