There are some tips we can use to improve performance of reports for Dynamics AX 2012.
- Use static reports. The deployment of static reports is described here: http://technet.microsoft.com/en-us/library/hh496446.aspx In this way we will safe several calls to Metadata service to get labels values and correct formatting of dates and numbers.
- Install application hotfix KB 2879661 which fix the situation when
first generation of a report after Dynamics AX client restart takes
long. This happens for each user.
- Newest kernel. Hotfixes for performance improvement are included in
new kernels that are released; sometimes there is a dramatic impact on
performance.
- In the morning a first report generation takes a lot of time
SSRS uses session pooling. The first request that comes in from a
user – a session will be created for them within the AOS services. Each
user gets a session cached on the AOS services, so subsequent calls just
reuse the session which matches the user who is making the request.
This means that the first time this is created for the user the report
will perform slower.
SSRS also uses a metadata cache the first request coming in which
needs a certain piece of metadata, the metadata is read from the
application code, and then cached in SSRS so it doesn’t have to read it
subsequent times. There is one metadata cache for all users – once an
object is cached in metadata, the services don’t go back to the code and
retrieve it again, and they just read from cache, until the cache is
cleared. This is also why a report may become slower the first time it
is run.
The RecycleTime is used to restart the application domain which
causes a refresh of the metadata.You could edit the RecycleTime (value
is in Minutes) to therefore stop the cache from clearing and which then
stop the extra hop to the AOS to gather the metadata.
Edit the <RecycleTime> element within:
RSReportServer.config (
http://technet.microsoft.com/en-us/library/ms157273.aspx )
Open the RsReportServer.config file.
• If you are using SQL Server 2008, the default location
of this file is: \Program Files\Microsoft SQL
Server\MSRS10.[SSRSInstanceName]\Reporting Services\ReportServer.
• If you are using SQL Server 2008 R2, the default
location of this file is: \Program Files\Microsoft SQL
Server\MSRS10_50.[SSRSInstanceName]\Reporting Services\ReportServer.
• If you are using SQL Server 2012, the default location
of this file is: \Program Files\Microsoft SQL
Server\MSRS11.[SSRSInstanceName]\Reporting Services\ReportServer
The default value is 720 (the value is in min so it is 12 hours.
NOTE:Problems you may see when deploying reports because of metadata caching.
A new field is added to a table in AX, the field is used on a report in VS. The report deploys to SSRS.
However when the report is called, it fails on the new field.
This is because SSRS caches metadata. The new object is not recognized as the old metadata is still cached
The other solution is when every morning the first user has to wait
for report very long is to pre-warm up the specific report. We would
have to choose those reports which are critical for customer and have to
run fast regards the time. To pre-warm up the report we can:
- Run report in batch job ie. SalesInvoice report:
public void run()
{
SrsReportRunController controller =
new SrsReportRunController();
SrsReportDataContract contract;
SalesInvoiceContract rdpContractClass;
LogisticsAddressCountryRegionISOCode code = "US";
// establish the report name
controller.parmReportName(
ssrsReportStr(SalesInvoice, Report));
// suppress the parameter dialog
controller.parmShowDialog(
false);
controller.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File);
controller.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
controller.parmReportContract().parmPrintSettings().overwriteFile(
true);
controller.parmReportContract().parmPrintSettings().fileName("c:\\TEMP\\DummyReport.pdf");
// set the required parameter.
contract = controller.parmReportContract();
rdpContractClass = contract.parmRdpContract()
as SalesInvoiceContract;
rdpContractClass.parmCountryRegionISOCode(code);
// start the operation
controller.runReport();
}
2. Run Power-Shell script.
. This
solution is not so easy as we need to specify correct parameters which
is not so obvious in Dynamics AX case. So to get parameters values we
can run following SQL on Reporting Services database (this is
SalesInvoice example):
SELECT e.timestart,DATEDIFF(ms,e.[timestart],e.[timeend]) AS [TotalTime]
,e.[timedataretrieval] AS [DataRetrievalTime]
,e.[timeprocessing] AS [ProcessingTime]
,e.[timerendering] AS [RenderingTime]
,COALESCE([AdditionalInfo].value( '(/AdditionalInfo/RdceSnapshotGenerationTime//node())[1]', 'int'),0) AS [RDCESnapshotGenTime]
,e.*
FROM [executionlog2] e
There is column Parameters. Which looks in my case following:
AX_PartitionKey=initial&AX_UserContext:isnull=true&AX_RenderingCulture=en-us&AX_CompanyName=CEU&FormLetterRecordId=0&PrintCopyOriginal=Original&RecordId=22565421785&LedgerJournalTransRecordId=0&AX_ReportContext=?RunOnClient%3D1%26PrintMediumType%3DScreen&AX_RdpPreProcessedId=5637144577&SalesInvoiceDS_isTaxInvoice=False&SalesInvoiceDS_TaxInvoiceItemType=Item&SalesInvoiceDS_CountryRegionISOCode=US&DocumentTitle=Invoice&DocumentTitleSequel:isnull=true&IdentificationText:isnull=true&InstanceType=Original&InvoiceInstanceText:isnull=true&CustInvoiceDuplicateRecId=0&UserName:isnull=true&SalesInvoiceDS_AgentAddress_TH:isnull=true&SalesInvoiceDS_AgentId_TH:isnull=true&SalesInvoiceDS_AgentName_TH:isnull=true&IsUnrealizedVATEnabled_TH=False&SalesInvoiceDS_Location=None&SalesInvoiceDS_MiscText:isnull=true&SalesInvoiceDS_PrintHeader:isnull=true&SalesInvoiceDS_ProdType=All&SalesInvoiceDS_TaxBranchCode_TH:isnull=true&SalesInvoiceDS_TaxBranchName_TH:isnull=true
What we do is:
Restart Reporting services – this step can be removed
Open internet explorer
Set IE window to be not visible
Navigate in SalesInvoice report with dummy parameters (here I pause
the script for 3 minutes to make sure the report will finish rendering)
Close IE.
Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"
Start-Service "SQL Server Reporting Services (MSSQLSERVER)"
# based on http://msdn.microsoft.com/en-us/library/ms155391.aspx)
[string] $url = "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fDynamicsAX%2fStaticReports%2fen-US%2fSalesInvoice.Report&AX_PartitionKey=initial&AX_UserContext:isnull=true&AX_RenderingCulture=en-us&AX_CompanyName=CEU&FormLetterRecordId=0&PrintCopyOriginal=Original&RecordId=22565421785&LedgerJournalTransRecordId=0&AX_ReportContext=?RunOnClient%3D1%26PrintMediumType%3DScreen&AX_RdpPreProcessedId=5637144577&SalesInvoiceDS_isTaxInvoice=False&SalesInvoiceDS_TaxInvoiceItemType=Item&SalesInvoiceDS_CountryRegionISOCode=US&DocumentTitle=Invoice&DocumentTitleSequel:isnull=true&IdentificationText:isnull=true&InstanceType=Original&InvoiceInstanceText:isnull=true&CustInvoiceDuplicateRecId=0&UserName:isnull=true&SalesInvoiceDS_AgentAddress_TH:isnull=true&SalesInvoiceDS_AgentId_TH:isnull=true&SalesInvoiceDS_AgentName_TH:isnull=true&IsUnrealizedVATEnabled_TH=False&SalesInvoiceDS_Location=None&SalesInvoiceDS_MiscText:isnull=true&SalesInvoiceDS_PrintHeader:isnull=true&SalesInvoiceDS_ProdType=All&SalesInvoiceDS_TaxBranchCode_TH:isnull=true&SalesInvoiceDS_TaxBranchName_TH:isnull=true&rs:Command=Render";
#documentation of InternetExplorer.Application: http://msdn.microsoft.com/en-us/library/aa752084(v=vs.85).aspx
$ie = New-Object -comObject InternetExplorer.Application;
$ie.navigate2($url);
$ie.visible = $false;
# sleep for 3 minutes to make sure the call is properly completed
Start-Sleep -s 180;
$ie.Quit();
Thing to remember is in case of using static reports:
http://technet.microsoft.com/en-us/library/hh496446.aspx
In this case we create directory StaticReports under DynamicsAx directory and there we have for each language separated report.
That makes reports better performed as we safe couple of trips to
Metadata service as we do not run in this case AX rendering extension,
but we have RDL ready to be run (see image below) but then if we want to
warm up the report we have to make sure we warm up each language
version we want to use. (ie
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fDynamicsAX%2fStaticReports%2fde-de%2fSalesInvoice.Report for German)
Facebook
