Improving SSRS Query-based Report Performance by not Using Display Methods - Part 3

This post is about Query based reports in Ax2012. Ax2012 supports table display method in query based reports. It is convenient to use. For example, the query for VendDueReportDetail is on VendTable and looks like this in VS designer:

 

The table display methods above look up VendInvoiceJour and VendTrans tables to get the last dates respectively.

However, there is a performance cost associated with this convenience. The call to a table display method is line-based. When a report is critical in performance, you can make it run faster by replaying the display method calls with Ax Views.

For the above report, we added two views, with aggregation, that duplicate what the display methods do. Please See xpo attatchment for one of them. The other view is almost the same. In one test case, this particular change improved the db access time from 0.42” to 0.05”. Here is the AOT of one view.

 

 

This pattern of improvement makes sense for the display method that is database bound and the logic in the display method can be expressed in views. For example, display method InventTable.itemName() can’t be expressed in views.

Please consider using calculated fields in Views before using display method. Calculated fields will be translated into SQL expression as part of target SQL statement, instead of being executed row-by-row in AOS.

View_VendInvoiceJourLastInvoiceDate.xpo