Getting from a SPID to an AX user id and back

Earlier versions of AX offered information about the SPID from within AX and the Online Users form. In AX 2012 it is a bit more tricky and requires a little tweaking of the AOS configuration.

What you need to do is to add a key in the registration database and it goes like this:

1. Run regedit.exe

2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dynamics Server\6.0\<ServerNumber>\<MyConfigurationName>. If you have not changed the configuration of the AOS it is named “Original (installed configuration)”.

2. Add a key of the type String Value, name it connectioncontext and give it the value of 1

3. Restart your AOS

If you have multiple AOS-instances you need to repeat the above for each AOS server.

Now we can use this statement to get a list of active queries:

SELECT cast(context_info AS VARCHAR(128)) AS [Connection info] , *
FROM sys.dm_exec_sessions
WHERE program_name LIKE '%Dynamics%'
	AND host_name LIKE '<AOSNameMask>'

If your AOS servers are named MyAOS01, MyAOS02, MyAOS03 and so forth put in MyAOS% as the <AOSNameMask>.

Now, the result is in the first column a variety of information including the AX user id and the AX session id. This can be matched directly to the online users overview within AX.

You can go the other way around by adding this

AND context_info like ' <AXUserId>%'

where <AXUserId> is the relevant id of the user in AX. Notice the initial blank in the range value.

Advertisements

Caching display methods in forms

This post is probably not the most earth shaking news but still a relevant issue to visit once in a while.

When developing or customising forms we often have to put in display methods to show the user data not available directly from the datasource. That can be either data from related tables or the result of calculations. For each refresh of data the display fields will be updated and the content recalculated. This can result in a significant performance plunge which easily can be fixed.

I did a quick test and in the Customer group form with 7 records a display method is called 27(!) times just opening the form. If your display method is the first column in the grid you can actually add a couple of clicks to that number.

So there definitely motivation enough here to spent a few moments on this. So let us start by separating the display method into two categories: Form methods and table methods.

Caching table methods

This is the easy one. Go to the init method on the datasource and add the following piece of code after the super() call:

CustGroup_ds.cacheAddMethod(tableMethodStr(CustGroup, cacheTestTableCached));

That is it. Your table display method is now cached and in the above example goes from 27 to 7 clicks.

Caching form methods

In this example we have a display method on the CustGroup datasource looking like this:

display Description paymTermDescription(CustGroup _custGroup)
{
    return PaymTerm::find(_custGroup.PaymTermId).Description;
}

With the earlier mentioned example this data lookup will be performed 27 times for 7 records. Here we need to do a couple of lines of coding to do make this work. First of all we need a Map object in the form declaration method:

public class FormRun extends ObjectRun
{
    Map cacheDemo;
}

Then we change our display method to look like this:

display Description paymTermDescriptionCached(CustGroup _custGroup)
{
    if (! cacheDemo)
    {
        cacheDemo = new Map(Types::String, Types::String);
    }
    if (! cacheDemo.exists(_custGroup.PaymTermId))
    {
        cacheDemo.insert(_custGroup.PaymTermId, PaymTerm::find(_custGroup.PaymTermId).Description);
    }
    return cacheDemo.lookup(_custGroup.PaymTermId);
}

Let us break down what the method does now.

  1. First we make sure that the Map object cacheDemo is instantiated. This could be done in the forms init method instead.
  2. Then we check if we already have the value we need in the cache. This is done based on the PaymTermId field on the CustGroup record. If it is not found we add it to the cache.
  3. Finally, we lookup the value from the cache and returns it. We know that the value always is in the cache so no need to do an exists check again.

This means that we still hit the display method as many times as before; but the calculation/data search part is only executed once per Payment term id. In this case that makes it 6 clicks since to customer groups had the same Payment term id.

The effect of unnecessary tables in a query

The other day I was asked to look at a performance issue with a form opening slow. After going through the normal motions I ended up with a method being called hundreds of time. It did not take that long time to execute but all in all it was the reason for the form slowing down.

What the method did was to call a view and summarise a quantity field. Not something extra ordinary but I gave the view a look anyhow. And that is what triggered me to do this small post on a simple but often effective question: Do I really need that table? 🙂

Let me show you what I found with a simple example. We want to have listed all purchase order ids, item ids, the line status and quantity sorted by purchase order id, line status and item id.

This is a simplified example of the real life example and the demo view looks like this:

SELECT TOP (100) PERCENT dbo.PURCHTABLE.PURCHID
	, dbo.PURCHLINE.ITEMID
	, dbo.PURCHLINE.PURCHSTATUS
	, dbo.PURCHLINE.QTYORDERED
FROM dbo.PURCHTABLE
INNER JOIN dbo.PURCHLINE
	ON dbo.PURCHTABLE.PURCHID = dbo.PURCHLINE.PURCHID
		AND dbo.PURCHTABLE.DATAAREAID = dbo.PURCHLINE.DATAAREAID
		AND dbo.PURCHTABLE.PARTITION = dbo.PURCHLINE.PARTITION
ORDER BY dbo.PURCHTABLE.PARTITION
	, dbo.PURCHTABLE.DATAAREAID
	, dbo.PURCHTABLE.PURCHID
	, dbo.PURCHLINE.PURCHSTATUS
	, dbo.PURCHLINE.ITEMID

It returns the data as expected, it executes in a few ms and everybody is happy. Well, until you execute it 500 times in a row and get tired of waiting. Looking at what data we actually need returned points us towards the PurchTable. The only thing we get from this table is the PurchId which is present at the PurchLine table as well and fully indexed there as well.

So if we change the view to look like this instead we get a significant effect:

SELECT TOP (100) PERCENT PURCHID
	, ITEMID
	, PURCHSTATUS
	, QTYORDERED
FROM dbo.PURCHLINE
ORDER BY PARTITION
	, DATAAREAID
	, PURCHID
	, PURCHSTATUS
	, ITEMID

The result set is the same but the performance has increased. Let us have a quick look at the statistics.

First what happens when executing the first view:

View1 - statistics

And then what happens with the updated view:

View2 - statistics

Well, several things indicates that we made a score. Packages received from the server has been reduced from 6 to 4 (33%) and bytes received reduced from 13921 to 5885 (58%). With this example only returning very few records (9) we will not see an effect in the execution times being dramatic, however we see an improvement.

All we did was to simplify and still got the exact same result at a much lower cost. This is not only relevant for views but any query made against the SQL server.