SSRS report tuning possibility in MS Dynamics AX 2012 RTM

SSRS in MS Dynamics AX 2012 RTM 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 in MS Dynamics AX 2012 RTM 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, 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 method Classes\SRSReportRunUtil\getParameterNameValueCollection is returning a CLR data type. The type it’s returning is “System.Collections.Specialized.NameValueCollection”. This data type is used to store a map of parameter names and their values.

If there are no parameter names or values to be stored, then we still need to return the CLR data type but we need to return it with a value of null. To do this we use the method “CLRInterop::Null” (http://msdn.microsoft.com/en-us/library/aa623097(v=ax.10).aspx)

If this line is commented out, I believe an error would be thrown in scenarios where there are no parameter names or values in the map, because the CLR data type has not been instantiated.

In the current design, the CLRInterop::null method is called every time. However, it might be possible to move this line so that it’s only called in the scenario where there are no parameters to be stored. An IF statement at line 29 checks if the parameter passed to the method contains any elements. If it does, then the code in the IF statement runs and a new CLR data type object is instantiated. Otherwise, we jump straight to the last line (61) and return the CLR datatype, which was set to NULL.

It might be possible to set the CLR data type to NULL within an ELSE statement, see below:

“Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This mail message assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures.”

//nameValueCollection = CLRInterop::Null(@”System.Collections.Specialized.NameValueCollection”);

if (_reportParametersMap.elements() > 0)

{

  nameValuePairMap = SrsReportRunUtil::getReportParamNameValuePairs(_reportParametersMap);

  if (nameValuePairMap != null && nameValuePairMap.elements() > 0)

{

// convert the X++ map to a .NET NameValueCollection sorted by RDL parameter order

// BP Deviation documented.

collectionSize = nameValuePairMap.elements();

nameValueCollection = new System.Collections.Specialized.NameValueCollection(collectionSize);

sortedParameterNameList = SrsReportRunUtil::getRdlOrderParameterNameList(_reportParametersMap);

listEnum = sortedParameterNameList.getEnumerator();

while (listEnum.moveNext())

{

  parameterName = listEnum.current();

  if (parameterName && nameValuePairMap.exists(parameterName))

{

// The .Net NameValueCollection keeps the inserted order and allows inserting the same name more than once

// although we are guaranteed unique parameter names

nameValueCollection.Add(parameterName, nameValuePairMap.lookup(parameterName));

}

}

CodeAccessPermission::revertAssert();

}

}

// Begin: Adding call to CLRInterop::Null in ELSE statement

else

{

nameValueCollection = CLRInterop::Null(@”System.Collections.Specialized.NameValueCollection”);

CodeAccessPermission::revertAssert();

}

// End