• About

Day 2 Day Dynamics

~ Experiences of a working AX developer

Day 2 Day Dynamics

Tag Archives: AX Database

Minimizing Database Calls

01 Monday Feb 2016

Posted by John Hagler in X++

≈ Leave a comment

Tags

AX, AX 2012, AX Best Practices, AX Database, AX Development, Dynamics AX, Performance, Tuning

I have been focusing a lot of attention on performance tuning AX over the last year.  The company I work for has now been live on AX 2012 R2 for about a year and a half and we’ve spent a lot of effort tuning indexes and re-writing code and processes to speed up our system.  Spending so much time thinking about performance has caused me to change the way I write and fix code so I figured I would share.

Outside of making sure you have good indexes for your tables, the biggest offender I’ve found for slow processes is unnecessary database calls.  I see it and have been guilty of writing it in code all the time.  You have a process that loops through records, evaluates some conditions, grabs related data if necessary and then performs some action.  The offender in this scenario is “grabs related date if necessary”.

It is really easy to write a basic query to start off with and then, in the middle of your process, grab more data.  AX 2012 makes it easy for you with Table::find() methods and by having the CreateNavigationPropertyMethods property on a table relation.  This allows a developer to grab a related table from an existing table buffer using a method call that matches the relation (e.g CustTable.DirPartyTable_FK()).  While this is incredibly useful, the danger with this approach is that it adds another database call to the process.

DatabaseCallJob

Examine the job above.  I have 2 while select statements that loop through the same number of records.  I have used different ranges so I could show times for cached and non-cached results.  The first loop grabs the project records and then during processing makes 2 more database calls to get the CustTable and then the DirPartyTable.  The second loop uses joins and a field list to minimize both the amount of data returned and the number of database calls.

I ran this process twice.  The infolog shows that the first loop took 5 seconds to run and the second loop took 1 second to run.  The second time I ran this process the first loop fell to 3 seconds because of database caching.  This means that the first loop takes 5 times longer to run when it’s calling the database and 3 times longer when its reading from the cache (a precision of seconds is probably minimizing this difference).  5 seconds doesn’t seem like a long time to process over 1100 records but there are a few things to consider.  This process isn’t doing anything but grabbing the records and this job was run in a stand-alone development environment.  The client, AOS and database are all on the same server and there is no one else using it except for me.  Imagine how much longer this could be if there were lots of users running processes in the system or if the components were separate and the network happened to be slow.

Each database call can add up to 2 remote procedure calls depending on where the code is running.  In this instance, my job is running on the client so the number of RPC’s is twice the number of database calls (1 from client to AOS and 1 from AOS to database).  The first loop has 2 RPCs for the while select, and then 2 (number of calls) X 2 (client and server) X 1169 (number of records) for a total of 4,678 RPCs and 2,339 database calls.  The second loop has 2 RPCs and 1 database call and the potential to give you just as much data as the first loop.  That’s a pretty crazy difference!

I now write a lot more X++ pseudo-SQL with joins when writing new code and changing existing processes.  The amount of time that can be saved during runtime far outweighs the extra time it takes to plan out your processes to grab all of the data that is needed the first time.

 

D2DDynamics custom PowerShell module

31 Monday Aug 2015

Posted by John Hagler in Powershell

≈ Leave a comment

Tags

ALD, AOT, Automation, AX, AX 2012, AX Artifacts, AX Database, AX Import, AX Label, Build Scripts, Compile, DB, Dynamics AX, PowerShell, PowerShell Module, R2 CU7, Server Compile, Team Foundation Server, TFS, XPO

The D2DDynamics custom PowerShell module is finally available in Codeplex.  The 1.0.0 version includes 15 exportable PowerShell functions and 3 internal helper functions.  They are:

  • Get-AXAutoRunXML
  • Start-AXAutoRun
  • Start-AXBuildCompile
  • Compile-AXCIL
  • Sync-AXDB
  • Compile-AXAOT
  • Clean-AXArtifacts
  • Import-AXXPO
  • Import-AXLabelFile
  • Sync-TFSWorkingFolder
  • Restore-AXDatabase
  • Combine-AXXPO
  • Start-AXMSBuildImport
  • Compile-AXXppNode
  • Import-AXVSProject

The 3 internal functions are:

  • Send-Email
  • Clean-Folder
  • Clean-Folders

This module is dependent on the 0.3.5 release of the DynamicsAXCommunity PowerShell module among others.  There are also some variables that should be set up in your profile if you are planning on using the functions.  The profile that I use can be found in Codeplex at Source Code -> Powershell -> Profile -> Modules -> Microsoft.PowerShell_profile.ps1.  Please use this as a reference for setting up your own profile.

To install the module, copy the D2DDynamics folder to the module folder for PowerShell.  On my Server 2012 server, the location is “C:\Windows\System32\WindowsPowerShell\v1.0\Modules”.  After the module is there, you should be able to open PowerShell and add it with the Import-Module cmdlet.

Future posts will include the steps necessary to create a custom build process using the module as well as more functions that can be used for deploying your build to other environments.  Please let me know what you think of the module and if you find any problems with using the functions in your environments.

Restore-AXDatabase custom PowerShell function

18 Thursday Jun 2015

Posted by John Hagler in Powershell

≈ 2 Comments

Tags

Automation, AX, AX 2012, AX Database, Build Scripts, Database Restore, DB, Dynamics AX, PowerShell, SQL Server

I think I have officially become a bi-weekly blog.  My current schedule isn’t going to be able to support a weekly blog right now.  The Restore-AXDatabase custom PowerShell function will restore a SQL Server database from a backup file.  This is used in my custom build process to reset the build environment back to a “vanilla” instance.

A development build environment needs to be reset to an instance that is missing all of your build code before starting the new build.  It is possible to delete the build models and recompile/resync everything to get back to “vanilla” but it is significantly faster to make a database backup of the AX database and model database without your code present and then restore these.  It was taking me 5-20 minutes to delete models and 90-150 minutes to recompile and resync everything.  It generally takes me 2-5 minutes to restore the 2 databases.

The Restore-AXDatabase function takes between 2-6 parameters:

  • ServerInstance (Server and SQL instance where the DB is located.  Defaults to the local default instance.)
  • AXDBName (Name of the database to be restored.)
  • BackupFilePath (The backup file to be restored.)
  • AdditionalSQLRestore (This defaults to the values that I use for SQL 2012.  I have added it as a parameter so it could be changed if necessary.)
  • TimeOut (The value defaults to 10 minutes but can be overridden if desired)
  • SMTPServer (SMTP server to use to send the email)
  • MailMsg (Net.Mail.MailMessage object used to send the email)
  • VariablePath (Path to a file used to default the parameters/variables)

This function can be found in Codeplex.  The steps of this function are:

  • Load the variables if a VariablePath parameter is used
  • Set up the SQL command
  • Restore the DB using Invoke-SqlCmd
  • Send the success/fail message

There is some setup that needs to be done to use this function.  The first thing necessary it to load the PowerShell module that contains it.  I wrestled with adding this to the function itself but decided against it.  The reasoning is that if you are using an earlier version of SQL Server than 2012, you need to load the SQL PowerShell Snap-ins vs loading the module.

LoadSQLModule

I have added 3 lines to my profile to accomplish this.  The first and third line are added to make up for a side effect of loading the SQLPS module, it changes your location in PowerShell.  The Push-Location cmdlet pops your location onto a stack and the Pop-Location cmdlet retrieves it.  This allows you to load the SQLPS module without changing your location.

The other step that you will need to do is to get your build environment cleaned up and create backups of the AX database and the model database.  You are going to want to have only the models that aren’t part of your build installed in AX.  In my case, this would be my SYS, SYP, ISV & ISP layers.  I only have a single custom model in my VAR layer that I build.  I compile the AOT, IL and sync the database to make sure everything is ready.  I then take a backup of both my AX database and my model database.  Now I can restore both databases and use my Clean-AXArtifacts custom Powershell function to clean up all of the compiled IL and when I start my AOS back up, I have a working “vanilla” instance of AX for my build.

Follow Day 2 Day Dynamics on WordPress.com

Day 2 Day Dynamics

  • RSS - Posts
  • RSS - Comments

Follow me on Twitter

My Tweets

Recent Posts

  • Minimizing Database Calls
  • Push-AXModel custom PowerShell function
  • Clean-AXModel custom PowerShell function
  • Get-AXTFSCombinedXpo custom PowerShell Function
  • Refresh-AXAifPort custom PowerShell function

Archives

  • February 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015

John Hagler

John Hagler

John Hagler

I am the Dynamics AX Technical Architect at Dealer.com. I have been working with AX since Sep. 2006, starting with Axapta 3.0. I have worked as both a VAR and an AX customer.

View Full Profile →

Blog at WordPress.com.

  • Follow Following
    • Day 2 Day Dynamics
    • Already have a WordPress.com account? Log in now.
    • Day 2 Day Dynamics
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...