Problem of the slow loading customer invoice journal

The title makes this blog post sound like a Scooby Doo caper.

With the GLP-EE (Eastern Europe) patch installed in an Ax 2009 SP 1 instance we found that the customer invoice journal was loading slower and slower.  The form was nothing short of painful.  It seemed to be okay when working with invoices from Spain, Morocco, Italy...

After restarting both Ax and MSSQLServer instances it was time to roll my sleeves up and investigate with the SQL Server Profiler.  While you and I may still be an amateur with the tool a quick perusal of the output identifies the problem query.  After the restart of Ax I relaunched the profiler and immediately opened the invoice journal.  Finding the problematic sql can be difficult due to the use of sp_cursorfetch, hiding from us the original query.  The sp_cursorprepexec is eventually identified, and we can see the sql used in the cursor that is being reused all the time.

Next up was shouting at the silicon gods and then creating a new table index (CustInvoiceTrans) within Ax, to include a new field introduced from the aforementioned patch - RefReturnInvoiceTrans_W.

Maybe should have just added the field to the InvoiceIdx, huh?

With our new index we can compare query times and the difference is huge.

All that I can say is that Microsoft owes me a beer.