Enabling index hints is deprecated in AX 2012 … almost

Sending index hints to the SQL server from AX has been around for a long time and often it has not done any good since the SQL server is pretty good at picking the best way it self.

So when it ended up as a parameter in the AOS server configuration in AX 2009 and then removed from the configuration in AX 2012 we seemed clear from the trouble it could cause. Microsoft stated that it was deprecated with AX 2012 and no longer available …

So it seemed a bit strange that the SQL server apparently received the information on a server I was working on recently.

While going through about all possible stuff to locate why it was acting like the non-existing index hint flag was enabled, I was going through the registration database to compare it against an AOS we knew was working as expected. And there it was … the registration key called “hint”.

I did a bit of research and I was not the only one struggling with this. As it appears there are these values to choose from :

Empty = Index hints are enabled and LTRIM (yes, it is there too) is disabled.

0 = Both index hints and LTRIM are disabled. This has to be the recommended setting.

1 = The same as empty. Does that make sense? No, not really. Anyways …

2 = Index hints are disabled and LTRIM is enabled.

3 = Both index hints AND LTRIM are enabled

 

And just for refreshing the memory: The location of the registration keys are

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\Dynamics Server\6.0

and in here find the right AOS and configuration.

Advertisements

Locate duplicate values (or the opposite) in a table

The way X++ handles SQL statement often lacks a bit compared to what you can do in standard TSQL.

One of the areas is if you want to get a set of records from a table where a specific value in a field only occurs once in the table. You could traverse through the records and compare values as you go along. That could pull down performance significantly depending on the record count. Another way of doing it is to group by the field while counting the records in e.g. RecId. Then traversing this result set and look at the value in RecId could give you either the unique or not unique value depending on what you need.

A better way would be to let the SQL server do some of the work and consequently return as few records as possible. Here is a job that illustrates this and the group by version mentioned above.

AX 2012 queries support the HAVING clause. That can in some scenarios do the same and a bit more elegant than this.

 

static void DEMO_NoDuplicates(Args _args)
{
    CustGroup custGroup;
    CustGroup custGroup2;
    PaymTermId lastPaymTermId;

    info("TRAVERSE COUNT");

    while select count(recid) from custGroup
        group by PaymTermId
    {
        if (custGroup.RecId == 1)
        {
            while select custGroup2
                where custGroup2.PaymTermId == custGroup.PaymTermId
            {
                info(custGroup2.PaymTermId);
            }
        }
    }

    info("USE JOIN");
    
    while select custGroup
        order by PaymTermId
        notexists join custGroup2
            where custGroup.RecId != custGroup2.RecId
               && custGroup.PaymTermId == custGroup2.PaymTermId
    {
        info(custGroup.PaymTermId);
    }
}


Joins, indexes and an example of when they don’t match…

We experienced some data locks that gave a customer some performance issues. Going through the motions we found this statement (scrambled) being the problem:

update_recordSet adjustment
    setting Voucher = voucher
    where ! adjustment.voucher
        join trans
            where trans.Status == Status::Open
               && trans.DateTime < perDateTime
               && trans.RecId == adjustment.Trans;

The table Adjustment is connected to the table Trans through a relation like this: Adjustment.Trans = Trans.RecId. And Adjustment has – among others – an unclustered index like this: Trans, Voucher and a couple of other fields.

So you might think that the SQL server was capable of utilising this index since both Trans and Voucher are in play in the attempt to limit the records involved.

Looking at it from the SQL server it ends up like this:

(@P1 NVARCHAR(21), @P2 INT, @P3 BIGINT, @P4 NVARCHAR(5), @P5 NVARCHAR(21), @P6 BIGINT, @P7 NVARCHAR(5), @P8 INT, @P9 DATETIME2) UPDATE T1
SET VOUCHER = @P1, RECVERSION = @P2
FROM ADJUSTMENT T1
CROSS JOIN TRANS T2
WHERE (((T1.PARTITION = @P3)
AND (T1.DATAAREAID = @P4))
AND ((T1.VOUCHER = @P5)))
AND (((T2.PARTITION = @P6)
AND (T2.DATAAREAID = @P7))
AND (((T2.STATUS = @P8)
AND (T2.DATETIME < @P9))
AND (T2.RECID = T1.TRANS)))

Now, when executing this ended up giving an index scan resulting in heavy locking of data. The reason for this – and the reason why the index could not be used – is that the SQL server sees this as two statements selecting adjustment records with the Voucher field as only range and the trans records with the specified ranges except the relation range and then returns the intersection of these two result sets.

Adding an index with Voucher as first field solves the problem and the data locking stops.

Roaming AUC files – new feature in R3

The quest for increasing performance in AX appears never-ending. With AX 2012 R3 Microsoft has added a new tool allowing us to make the AUC files global/shared.

Basic AUC file info:

AUC files are cache files made by the AX client to improve load time. Forms we have already loaded once could be loaded again from the cache without having to make a trip to the AOS for the bits and bytes. All stored in c:\users\<username>\AppData\local and then by nature user specific.

So the main challenge so far has been that user A opens the sales order form the first time, waits, caches the element and can open the form faster the second time and the same story for user B, C and all the others.

In AX 2012 R3 a new feature have been added to the client configuration with the title “Use roaming user profile to store client-side cache”. That basically says it all. We can now specify a global directory for the cache files allowing users to share the cache so when user A has opened the form user B can utilise the cache.

Still no fun for user A, but user B is in luck …

Change which index is clustered – directly on the SQL server

Todays post is not exactly Dynamics AX relevant. However, in some cases where you need to do some immediate testing og hot fixing and you are not able to change it on a running AX environment you could end up making temporary index modifications directly on the SQL server.

The thing about this is that when you later on synchronise from Dynamics AX changes often are overwritten in that process. That can easily be ok as long as you are aware of it.

One of the things that needs a bit more work than just adding/removing indexes or fields is to change which index is clustered. For demonstration purposes I have created a small database called DemoDB with a table called PersonTbl.

It contains 3 fields and 2 indexes as shown here:

TableWithWrongClusteredIndex

The thing is that we in this case for some reason need to have the IdIdx as the clustered index and it is not an option to wait for the change to be released through the normal procedure in AX.

If we look at the clustered index NameIdx we cannot un-cluster it:

OldClusteredIndex

And clustering the IdIdx index is not possible from the properties form either:

NewClusteredIndex

To make this happen we need to modify the table through design mode. Right click on the table and select Design:

DesignTable

Right click anywhere on the columns overview and select Indexes/Keys:

SelectIndexesKeys

Now we can uncluster the NameIdx table like this:

UnclusterIndex

And make IdIdx clustered:

ClusterIndex

Click Close and save the table modifications.

Voila! The indexes have been changed. You might need to refresh to make it visible and should anyone be using the table and indexes while you make your customisations the saving part would fail.

TableWithCorrectClusteredIndex

 

Please remember that this procedure is not exactly best practice and should only be used as a last exit.

Using HAVING in a query

With AX 2012 we now have the option of using HAVING in a query. This allows us to limit a result set based on aggregated fields.

The advantage is that we let the SQL server do some filtering and receive less records compared to the old-school version were we had to receive all records and then use an IF or the likes to filter away the records that did not match the criterias.

In this simple example we want to get all sales ids on orders having lines with a line amount total of 100000 and beyond.

 

static void SimpleQueryWithHavingExample(Args _args)
{
    Query query;
    QueryRun queryRun;
    QueryBuildDataSource qBDS_SalesTable;
    QueryBuildDataSource qBDS_SalesLine;
    SalesTable salesTable;
    SalesLine salesLine;
 
    // Init query
    query = new Query();
 
    // Add datasources and use standard relations
    qBDS_SalesTable = query.addDataSource(tableNum(SalesTable));
    qBDS_SalesLine = qBDS_SalesTable.addDataSource(tableNum(SalesLine));
    qBDS_SalesLine.relations(true);
 
    // Add a group by on SalesTable
    qBDS_SalesTable.addGroupByField(fieldNum(SalesTable, SalesId));

    // Add aggregation on LineAmount
    qBDS_SalesLine.addSelectionField(fieldNum(SalesLine, LineAmount), SelectionField::Sum);
 
    // Add the having filter
    query.addHavingFilter(qBDS_SalesLine, fieldStr(SalesLine, LineAmount), AggregateFunction::Sum).value(SysQuery::range(100000, ''));
 
    // Create and run the queryRun object
    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
        salesTable = queryRun.get(tablenum(SalesTable));
        salesLine = queryRun.get(tableNum(salesLine));
        info(strFmt("%1 %2", salesTable.SalesId, salesLine.LineAmount));
    }
}

 

I am sure that this will come in handy. 🙂

 

New feature in AX 2012 R3 – Inserting data in table directly from query

A new version/release of Dynamics AX is finally gone RTM and one of the new cool features is the ability to insert data from a query directly into a table. And why is that so clever you might think? Well, consider the performance boost of not having to loop through thousands of records including the roundtrips between SQL server, AOS server, client and back. That is why this is so interesting.

Here is a quick demo of how it works:

I have created a table called TESTQuery2Record. It contains 4 fields: CustGroupId, CustGroupName, PaymTermId and PaymTermDesc. It should be fairly obvious what to expect from these fields. 🙂

The purpose of the demo scenario is to get customer group data including the corresponding payment term information inserted in our test table. To do this I have written this job:

 

static void DEMO_Query_InsertRecordSet(Args _args)
{
    TESTQuery2Record testTable;
    Map fieldMapping;
    Query query;
    QueryBuildDataSource qbds_custGroup;
    QueryBuildDataSource qbds_paymTerm;
    QueryBuildFieldList fldList_custGroup;
    QueryBuildFieldList fldList_paymTerm;

    // Empty the target test table
    // ---------------------------
    delete_from testTable;

    // Build the query
    // ---------------
    query = new Query();
    qbds_custGroup = query.addDataSource(tableNum(CustGroup));
    qbds_paymTerm = qbds_custGroup.addDataSource(tableNum(PaymTerm));
    qbds_paymTerm.addLink(fieldNum(CustGroup, PaymTermId), fieldNum(PaymTerm, PaymTermId));

    // Field lists are required
    // ------------------------
    fldList_custGroup = qbds_custGroup.fields();
    fldList_custGroup.addField(fieldNum(CustGroup, CustGroup));
    fldList_custGroup.addField(fieldNum(CustGroup, Name));
    fldList_custGroup.dynamic(QueryFieldListDynamic::No);

    fldList_paymTerm = qbds_paymTerm.fields();
    fldList_paymTerm.addField(fieldNum(PaymTerm, PaymTermId));
    fldList_paymTerm.addField(fieldNum(PaymTerm, Description));
    fldList_paymTerm.dynamic(QueryFieldListDynamic::No);

    // Specify the mapping between target and source
    // ---------------------------------------------
    fieldMapping = new Map(Types::String, Types::Container);
    fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupId), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, CustGroup)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupName), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, Name)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermId), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, PaymTermId)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermDesc), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, Description)]);

    // Let AX handle getting data from the query to the target table
    // -------------------------------------------------------------
    query::insert_recordset(testTable, fieldMapping, query);

    // Done!
    // -----
}

So what is happening when we execute the above. Besides the delete_from we will see to queries executed on the SQL server.

1. Execution of the query and adding the data to a temporary table:

SELECT T1.CUSTGROUP AS f1,
       T1.NAME AS f2,
       T2.PAYMTERMID AS f3,
       T2.DESCRIPTION AS f4,
       N'usmf' AS DATAAREAID,
       1 AS RECVERSION,
       5637144576 AS PARTITION,
       IDENTITY(BIGINT, 1, 1) AS RECID
INTO [##ax_tmp_usmf8_113_103617]
FROM CUSTGROUP T1
CROSS JOIN PAYMTERM T2
WHERE (
        (T1.PARTITION = @P1)
    AND (T1.DATAAREAID = @P2)
      )
    AND (
    (
        (T2.PARTITION = @P3)
    AND (T2.DATAAREAID = @P4)
    )
    AND (T1.PAYMTERMID = T2.PAYMTERMID)
      )
ORDER BY T1.CUSTGROUP

2. Inserting the data from the temporary table in the target table

INSERT INTO TESTQUERY2RECORD (
    CUSTGROUPID,
    CUSTGROUPNAME,
    PAYMTERMID,
    PAYMTERMDESC,
    DATAAREAID,
    RECVERSION,
    PARTITION,
    RECID
 )
SELECT F1,
       F2,
       F3,
       F4,
       DATAAREAID,
       RECVERSION,
       PARTITION,
       RECID + 5637144596
FROM [##ax_tmp_usmf8_113_103617]

The effect of this is x number of records queried and inserted in 1 roundtrip to the SQL server and still based on a query.