Hi there,
We have 1.6 million records in the table Sales Invoice Header and doing search and filtering on Sales Invoice Number is slow. 15 seconds.
With help from database profiler I found the slow query and eventually I figured out that disabling the index $Key9 on the table speeds up the query. It now runs instantly in our test environment.
My question: can I safely disable this index in production, or is it something that our Dynamics Partner needs to do via application code?
Edit:
The key is a non-clustered index on [Posting Date] ASC, [No_] ASC
We have disabled it in test, and we do see any negative impact. My thinking is that if it has an impact, we just reactivate the index.
This is the execution plan with the index disabled:
This is the execution plan with the index enabled:
The query:SELECT TOP (50) /Sales Invoice Header/./timestamp/ AS /timestamp/, /Sales Invoice Header/./No_/ AS /No_/FROM /STAR/.dbo./Company$Sales Invoice Header$437dbf0e-84ff-417a-965d-ed2bb9650972/ AS /Sales Invoice Header/ WITH(READUNCOMMITTED)WHERE ( /Sales Invoice Header/./No_/ COLLATE Danish_Greenlandic_100_CI_AI LIKE N'%333741%' OR /Sales Invoice Header/./Sell-to Customer No_/ COLLATE Danish_Greenlandic_100_CI_AI LIKE N'%333741%' OR /Sales Invoice Header/./Posting Description/ COLLATE Danish_Greenlandic_100_CI_AI LIKE N'%333741%')ORDER BY /Posting Date/ DESC, /No_/ DESC
The query is sent when we search for a posted invoice on page Posted Sales Invoices (143) with the filter on Number. I guess it is the query that fetches the invoices in the drop down that pops up when you start typing the invoice number.
As said earlier, the table /only/ contains 1,6m records, which should be far below the upper limits of what BC is capable of. I'm struggling to understand why we see such performance issues in a standard functionality.
