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

Comments

• Anonymous
  February 23, 2014
  You didn't mention how the views are replacing the display method in the report. Please be specific.
• Anonymous
  March 24, 2014
  It is depends on the logic in display method. In short, you can use a view only if the logic in the display method is a select statement. You can then convert the select statement into a view, that can be part of the data source table or can be joined with the table. Open the display method, LastInvoiceDate, you will see how to creat a view to replace it.