Inside Microsoft Dynamics AX 2012 Performance

Hi there!

I found a very interesting video on AX 2012 performance. It is amazing and really exciting to be part of this release as there have been many improvements to create a better, more reliable and faster product. The following are the key points in the video:

1-   Ax 2012 focuses its efforts on the AOS, this is, the AOS can scale up and scale out.  For example, as computer manufactures create bigger boxes (strong processing power), AX 2012 application server scales out to take full advantage of this new advances.

2-    Ax 2012 takes the unmanaged X++ and run it in .NET. Therefore X++ is now a managed language which means is faster (way faster than before!)

3-    There has been a lot of work done towards space optimization. For instance, in retail situations the number of transactions can quickly grow, and this data needs to be stored efficiently without increasing storage costs.

4-    Transactions are much faster thanks to the improvements in X++ compatibility with .NET. For example, in AX 2009 you could run 100 lines of code in X amount of time (X being a number of seconds). In AX 2012 you can run 1,000 lines of code in the amount of time (X) that AX 2009 does.  This helps achieving fast deployments and customer usability of the product.

5-    The goal of AX 2012 is increase transaction speed and customer satisfaction by implementing the solution on pretty much the same hardware an end user had before.

Check it out.

Take care!

Indexes In Microsoft Dynamics AX

Today I would like to have a discussion about Indexes in AX. In my opinion, I think it is extremely important to create an architecture that will support the fast retrieving of records. In addition, there is no way we can provide a good service if we don’t provide a nice and easy to follow guide on indexes.  

So, basically indexes in databases are used to locate records and they are stored separately in the database. They contain a key that can be quickly located in the index, and this key will have a reference to a record.

For example, the let’s think on the SalesIdx index in the Sales Table. This table field index contains the SalesId of a Sales Order. Now, because the Sales order is unique and sequenced, an index will be used to quickly lookup the sales orders records through the reference.

Type of Indexes

Unique

A unique index is created based on a column; Microsoft Dynamics AX assures that no duplicate key values can occur in that same column. Also, when updating a column that contains a unique index will cause an error.

Non-Unique

Non-unique indexes are created for performance reasons. In other words, they give a fast way of retrieving data. For example, (and here we can think on the example above about the SalesIdx) instead of doing a full-table search of all the records in a table, and non-unique index will help us narrow down this search to a reference.

In general, indexes use system space and must be updated every time system data is created, edited, or deleted. I have seen indexes slowing down the updating process. However in most cases the overall performance improvement when selecting records far outweighs the performance loss when updating.

Microsoft recommends that when creating an index to give it a name that reflects the names of the fields on a table plus the suffix Idx. (i.e. SalesIdx)

Creating Indexes

Creating indexes is not very difficult at all. You just want to make sure that the fields you want to create the index on exist in the table (I guess this is obvious, but you’ll be surprise with some of the question I have been asked over the years). Anyway, the indexes can be unique or non-unique, and are based on a single column or multiple columns that exist within the table.

1. Locate a table in the AOT (This has to be a table in your Dev environment or some virtual machine AX)
2. Right-click the Indexes node in the table and select New Index. A new index Index1 is created.
3. Rename the index to the field name of the table/field you have chosen
4. Drag the field you have chosen to the index created in the step above node.
5. In the properties sheet for the created index node, set the property AllowDuplicates to No.

Just to make it more visually clear, please take a look at the following picture:

Take Care!

AX 2009 Data Import / Export

The following post is about shows how to:

1-   Export data from a company in AX using a definition Group

2-   Creating a new company that will take the new data

3-   Importing the data exported from step 1 into the new company created in step 2 using a definition group as well.

In a current project, I needed to freeze the Test1 environment due to some focus testing of a certain functionality we implemented. At the same time, we needed to allow the uses to keep testing other customizations without affecting Test1.

In addition, to make the user’s lives easier, we decided to have the same data that they were using in Test1 in our Test2 instance. So, we exported the data from our Test1 environment, and then imported it to a new company in our Test2 environment.

The following steps will show this process step by step:

1-   First select the company you want to export the data from as the AX current company by clicking in company name in the right lower corner of your AX interface. 2-   This would open the “Select Company” form as shown;

3-   Select  Administration (module)

4-   Under periodic->Data exports /import ->Click Export to

5-   The “Export options” form will appear

6-   Select a definition group. You can either create a new definition group to include what you need, or just choose ALL to get all the tables. NOTE: In some cases, the already defined “ALL” definition group will not have all the tables, so in this case you would have to create a new definition group. In my case I created a new one name “ALL_NewDef” that included the latest tables.

7-   Enter a file name and select where to save it

8-   Click OK. At this point the company date export process should be done (after processing of course), and the files (.dat & .def) should have be saved on your computer.

Now, let’s create a new company

9-   Make sure that the selected company empty and click area node Administration

10- Click company Accounts under Common forms

11-   Press (Ctrl + N) in order to Create new company

12-   Make sure you select the newly created company as the current company

Import company file already exported in the prior steps and click Administration

13- Under periodic->Data exports /import ->Click import; The “Import options” screen will appear

14- Select the “import definition group” drop down list and choose ALL (otherwise create your own)

15-   Select the .dat file (exported before) and Click Ok to start the process

The import process may take some time according to the size of transactions in the original (exported) company.

In addition, you can choose to delete all the transaction data before the import when you are importing a .dat file o an existing company account to update the data.

LIKE Operator in X++

In Axapta, we can use the LIKE operator in an inline SQL statement, see the following:


select * from VendTable where VendTable.Name like 'P*'


Another example is to use the LIKE operator to compare strings!


static void MyTestForLike(Args _args)
{

    str test;
    ;

    test="Eduardo";

    if(test like "Eduar*")
       info('Hello Ed!');

}

SQL 2008R2 FULL TEXT INDEX SEARCH SETUP

In Microsoft SQL 2008 R2, the process of setting up full text search is done differently then in prior versions of SQL.


In earlier versions of SQL, we would have to right-click on the table we wanted to have full text index search capabilities by going to Design. Once in the table designer, we would choose Full Text Index and we would set the values in the window that would pop up.


In SQL 2008 R2, however, the process of setting up the full text index search is as follows:


The very first step is to create a new Catalog in our data base as follows (Let's not forget to click on the Data Base we want to setup the Full Text Index Search)


CREATE FULLTEXT CATALOG NameOfCatalog


Execute the stored procedure, which should be really quick and in where we should get a message like this:


Command(s) Completed Successfully


Then we go through the following steps: