To capture an SQL query from Axapta Query Object

Hi Everyone,

To capture and print an SQL Query generated from Axapta's Query:

info(sysQueryRun.query().dataSourceNo(1).toString());

Very helpful while debugging and tracing the exact SQL query from an Axapta's Query run object.

SQL Query Tracing in Axapta 4.0 - Part 2

SQL Queries can be captured from the Axapta application, once the tracing is enabled as mentioned in the earlier post.

The traced queries can be captured from Axapta’s menu:
Administration -->Inquiries -->Database -->SQL statement trace log

The captured queries can also be filtered on the columns like:
Datewise, Userwise, Trace type wise,etc.

Steps to identify the necessary indexes to implement as per the captured queries:
          1. Save the captured queries into a ‘.sql’ file by replacing the substation variables.
This replacement is needed to get parsed by SQL server during the index tuning.

For eg: The captured query is:

SELECT TOP 1
A.LINEDEVICEPERMANENTID,A.LINEDEVICEID,A.COMPUTERNAME,A.ACTIVE,
A.LINEDEVICEPROVIDER,A.LINEDEVICENAME,A.RECVERSION,A.RECID
FROM SMMPHONEPARAMETERS A WHERE ((DATAAREAID=?) AND ((COMPUTERNAME=?) AND (ACTIVE=?)))

The same can be replaced (‘?’ is replaced with ‘’) as:

SELECT TOP 1
A.LINEDEVICEPERMANENTID,A.LINEDEVICEID,A.COMPUTERNAME,A.ACTIVE,
A.LINEDEVICEPROVIDER,A.LINEDEVICENAME,A.RECVERSION,A.RECID
FROM SMMPHONEPARAMETERS A WHERE ((DATAAREAID='') AND ((COMPUTERNAME='') AND (ACTIVE='')))


2. DETA (Database Engine Tuning Advisor) is the tool available in SQL server, which can be used to assess the workload and identify the necessary indexes for any performance optimization. A very handy tool to use and accessible from SQL Server Management Studio under:
Tools -->Database Engine Tuning Advisor

a. Create a new session in DETA.
b. Load the captured and stored .sql file to analyze.
c. Select the respective Database from the list to assess the load of the identified queries (.sql file will contain the identified queries)
d. Start the analysis
e. After the analysis, the necessary table INDEXES and sql STATISTICS will be identified and displayed.

3. Create the necessary indexes as identified in step #2 in Axapta.
The indexes have to be created only using Axapta, because of one way synchronization from Axapta to SQL Server.

Note:
1. The DETA will not only suggest the indexes to be created, but also provides the syntax to create. But this will be relevant only to create them directly using SQL server. The suggested INDEXES and STATISTICS can be created using SQL SERVER also, but leads to synchronization issues from Axapta.
2. This process helps to identify the necessary indexes, but also have to be cautious while creating the indexes. As we all knew, creating multiple and improper indexes will also affect the performance, hence be cautious too.

It is always ideal to handle the performance issues at the code level only as the first preference than going for these index creations as and when needed.

Hope, this helps for the next time optimizations ;-)

SQL Query Tracing in Axapta 4.0 - Part 1

The key thing for the optimization and performance of the application is a good code.

Optimization can be done in 2 prime ways:
a. Code review and cleanup.
b. Right indexes to the table.

In general, all the possible bottlenecks for the optimization of the application cannot be identified at the initial stages of the implementation, instead only over a period of time. To identify the long running queries, the built in trace utilities exist in the application.

Let’s learn on enabling the trace and capture the long running SQL queries as a first part.

Later about tuning the application by implementing the necessary indexes as secondary.

As a first part, to enable the trace for long running SQL queries:
1. Select ‘SQL Trace’ checkbox from SQL tab. Accessible from Tools >>Options
a. Set the Threshold limit in milliseconds for considering the long running SQL queries.
b. Select ‘Database’ option and click Apply.


Note:
1. This Database option is helpful to parse the queries using SQL SERVER than Ax based SQL Parser utility.
2. This trace checkbox should not be checked always, instead to be used only whenever the tracing is needed. Otherwise, the application’s performance will be affected.
3. This tracing will not capture the queries unless the ‘Client tracing’ option is enabled as mentioned.


Identifying and implementing the indexes for the application tuning will be posted next.

Assigning access rights in Axapta between users

Assignment of access rights for the users is a very frequent task. This script helps to assign or replicate the acess rights between users instantly.


// Script for assigning/swapping access rights between users.
static void assignAccessRights(Args _args)
{
UserGroupList groupList,groupListIns;
str 10 frmUser, toUser;
;

frmUser = 'usr1';
toUser = 'usr2' ;

// To delete the existing permissions of the toUser
delete_from groupList
where groupList.UserId == toUser;


while select groupList
where groupList.userId == frmUser
{
info(strfmt('Group assigned: %1',groupList.groupId));
select forupdate groupListIns
where groupListIns.userID == toUser
&& groupListIns.GroupId == groupList.GroupId;

if(!groupListIns)
{
groupListIns.UserId = toUser;
groupListIns.GroupId = groupList.GroupId;
groupListIns.insert();
}
}
info(strfmt('Permissions changed from: %1 to: %2',frmUser,toUser));
}