Share via

Improving SSRS Report Performance using new R3 features - Part 6

  • Article

Using SrsReportDataProviderPreProcessTempDB class

In Ax2012, we introduced class SrsReportDataProviderPreProcess class as the solution for long running SSRS reports that cause SSRS time out. The approach is to process data in Ax session before calling SSRS. The preprocessed data stored in a regular SQL table shared by all user sessions, only to be striped by session id. That approach creates a bottleneck in case of many concurrent user sessions. In R2, we introduced this new class to allow use of tempDB to carry report data across sessions, from Ax data processing session to SSRS data retrieval session. AOS kernel will not purge tempDB table when session end leaving it to application code to clean up the tempDB table. In case of report, this base class will take care of the clean up.

You should stop use the old SrsReportDataProviderPreProcess class but use the new base class instead for long running reports. You will see performance gain in case of multiple concurrent sessions on the same report.

Currently there are many OOB Ax2012 R3 reports already take advantage of this feature. You can check out how they are leverage the feature there. One typical example is TaxListDP class.

This R3 feature is implemented in the kernel. The usage is not limited to reporting. Attached is a sample project showing how this kernel feature is used in case of multi-threading in Ax: each thread creates its own instance of a temp table, inserts rows into the table, and passes the table name back to the main thread (crossing session boundary), where contents from each temp table is then merged into one DB table using set-based insert. At the end, the code must despose the temp tables. The code is for demonstraction only. A more practical application to leverage this feature is in batch processing.

 

 

Using Dynamic Set-based insert operation

In AX2012, set based insert operation is part of X++ syntax and is precompiled. That limits its use when filters are dynamic, i.e. taking from end user at run time.

In AX2012, R3 we implemented a DCR that allows constructing a set based insert where clause based on Query range on the fly. This feature allows much wider adoption of set based operation and greatly improve performance when applied.

A good coding example can be seen in TaxListDP.insertTaxListTaxTmpData. It is done using a new static method on Query object. Here is how it is used.

                Query::insert_recordset(taxListTaxTransTmp, insertRecordsetMap, query);

 the first parameter is the tempDB table and the second one is the mapping between the tempDB table fields and query fields. See detail in the AOT class source code. Here is the highlight of the row-based old code:

 

Without the DCR, we can’t change this code to set-based because query range is set by end users. Now, the highlight of the new high performance code is as follow:

 

 

PrivateProject_TempDBTableDCRSample.xpo

Comments

  • Anonymous
    March 01, 2015
    This is brilliant.Tanks fort sharing.

  • Anonymous
    April 01, 2015
    Thanks for sharing, always interesting!

  • Anonymous
    May 26, 2015
    Hi, How can we insert display method value in above said method?? Can you please explain, if we can do this?

  • Anonymous
    December 15, 2015
    Brilliant info! Thanks a ton for sharing..Regards, <a href="http://www.skillquotient.net">AX consultant</a>

  • Anonymous
    February 24, 2016
    Is there a way to map sum field? I have a grouped query...