Performance code writing

There are a few things to think off when writing code, design requirements and performance. If we include the thought process of performance when developing we can often improve our overall code performance at the time of writing.

A typical piece of code found in DAX is:

While
    Select * from <table>

This code will return all the fields of the <table>, which is fine if all the fields are required, but if only one field was required then this would be wasting system resources to retrieve the all the data.

I would expect to see the following code:

While
    Select <field1>, <field2> from <table>

The above code is much more efficient.

DAX best practice is to create on every table a static Find method. This method is a generic method, and as such should be used with CAUTION. It employs the Select * from <table> methodology so is inefficient. It should almost never be used, with the possible exception of very narrow tables.

Typical scenarios where find methods are used are:

<variable> = <table>::find(<id>).<field>;

The same can be achieved with the following:

<variable> = (Select firstonly <field1> from <table> where <criteria>).<field1>;

The system resource load for the second command is much smaller, thus better performance.

The last topic for this message is going to be loops. It is often found in DAX that we have loops within Loops.

Example 1

While
    Select * from SalesTable
{
    Select firstonly * from CustTable
        Where CustTable.accountnum == SalesTable.CustAccount;
    <Do some code action>
}

If there was one hundred (100) sales order records in SalesTable, then this code would send one request for the sales table then one hundred (100) separate requests for customer table.

This can be done in one SQL statement and greatly reduce the system resource.

While
    Select <field list> from SalesTable
        Join <field list> from CustTable
            Where CustTable.accountnum == SalesTable.CustAccount
{
    <Do some code action>
}

This request would only send one (1) SQL request for information. Must more efficient.

Example 2

Another example of code often seen is as follows:

While
    Select * from SalesTable
{
    if(SalesTable.custaccount == “account1”)
    {
        <Do some code action>
    }
}

This can be improved by only getting the records needed in the first place:

While
    Select <field list> from SalesTable
        Where SalesTable.custaccount == “account1”
{
    <Do some code action>
}

If Custaccount of account1 only represented ten percent (10%) of the records this is ninty percent (90%) less work for SQL and DAX.

Example 3

Another example of code often seen is as follows:

TTSbegin;
While
    Select forupdate * from SalesTable
{
    if(SalesTable.custaccount == 'account1')
    {
        SalesTable.<fieldX> = 'Test';
        SalesTable.update();
    }
    else
    {
        Salestable.<fieldY> = 'Test';
        SalesTable.update();
    }
}
TTScommit;

This can be improved by using the bulk update commands:

Update_recordset SalesTable
    Setting <fieldX> = 'Test'
    Where Salestable.custaccount == 'account1';

Update_recordset SalesTable
    Setting <fieldY> = 'Test'
    Where Salestable.custaccount != 'account1';

The original example would make one (1) SQL request for information, and one hundred (100) update statements. The improved version would send two (2) update requests to SQL, greatly improving the performance of the request.

The key is to put as much information as possible into the SQL commands and use the Bulk SQL commands as much as possible. It can take some time to ensure only the necessary fields are in the Select statement, but the performance improvements can be huge.

Happy coding…

Advertisements

One thought on “Performance code writing

  1. You must also take caching into account. For example, if you have a table with find(), it likely uses single-record caching. If you replace find() with “select field1…”, the first select will fetch all fields anyway (at least in AX 2012) to fill the cache and all subsequent selects (if any) will take the value from cache. Therefore your select “select field1…” will never send to database and you just wasted time.
    In older versions, you could actually prevent caching from happening, therefore you could actually introduce a performance issue by your change.

    Yes, caching it tricky. But it’s very important.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s