Performance Queries for dsiTransactionLog
About performance queries for dsiTransactionLog
You can query data in your registered dsiTransactionLog to help identify the overall health and performance of your Mobile Enterprise Platform solution. Example queries are provided that you can run individually or as a script, and can alter to suit your needs. For example, you might add parameters to further filter results or to gather additional details.
The example queries cover a variety of Top 10 lists that provide data about devices, function calls, transactions, servers, and users. They also include options for you to gather data about trends over a designated time frame.
The example queries are intended to run against the dsiTransactionLog on a SQL Server 2012 or 2016 database. The syntax may need to be altered for a different version of SQL Server, or when running queries on an Oracle Database.
If you prefer more detailed information about the health and performance of your Mobile Enterprise Platform solution, or to capture and view details about app performance, refer to Supply Chain Insights.
Queries for Top 10 lists
The following Top 10 example queries are available.
Queries for trends
In Mobile Enterprise Platform, function calls are used in apps and app items to call registered functions through an associated connector for synchronous or asynchronous integration with an ERP or other system of record. The following example queries are available to identify trends in your function call data.
After completing the queries, you can export and analyze the resulting data using a variety of tools. For example, the following chart was created in Excel using data from the queries for function calls by hour with average time for the last 30 days, 7 days, and 24 hours.
These queries group the transactions by the date and time the Mobile Enterprise Platform Application Sever received them. The data was totaled and an average elapsed time over all function call transactions within the given hour is provided. The same query was then repeated for each specified time range.
The chart shows how the volume of transactions increased as the elapsed time decreased, and conversely how the transactions decreased as the elapsed time increased. The data in this example is from a properly tuned Mobile Enterprise Platform solution that is integrated with JD Edwards EnterpriseOne. Mobile Enterprise Platform uses multiple threads for processing while only starting the number that is required based on transaction volume. As volume scales, more threads are opened. If your ERP system is tuned to accommodate this increase in threads, you will see this type of trend in your queried data.
Top Ten Devices by Volume
When run on the dsiTransactionLog, the Top Ten Devices by Volume query returns a ranked list of the devices that have executed the most transaction requests. When a common user ID is used to log into devices, the results of this query can help you determine volume data based on workstation versus a specific user.
Query
--Top Ten Devices by Volume-- SELECT TOP 10 dsiTransactionLog.DeviceID, Count(*) AS Total FROM dsiTransactionLog GROUP BY dsiTransactionLog.DeviceID ORDER BY Total DESC;
Result
A list of the top 10 devices ranked by the total number of executed transactions.
Result example
Top Ten Function Calls
When run on the dsiTransactionLog, the Top Ten Function Calls query returns a ranked list of function calls with the highest number of requests. This enables you to identity which function calls (APIs) are most often used in your Mobile Enterprise Platform solution.
Query
--Top Ten Function Calls-- SELECT TOP 10 dsiTransactionLog.FunctionCallID, Count(*) AS Total FROM dsiTransactionLog where transactiontype = 'A' GROUP BY dsiTransactionLog.FunctionCallID ORDER BY Total DESC;
Result
A list of the top 10 function calls ranked by the total number of requests.
Result example
Top Ten Functions with Errors
When run on the dsiTransactionLog, the Top Ten Functions with Errors query returns a ranked list of function calls that ended in error for any reason. You can further filter this data to help uncover the underlying cause of the error condition. In some cases, the error is the result of the user entering bad data, which can lead to improving validation of the data entered by the user.
Query
--Top Ten Functions with Errors-- SELECT TOP 10 dsiTransactionLog.FunctionCallID, Count(*) AS Total FROM dsiTransactionLog WHERE dsiTransactionLog.RecordStatus='2' and dsiTransactionLog.transactiontype = 'A' GROUP BY dsiTransactionLog.FunctionCallID ORDER BY Total DESC;
Result
A list of the top 10 function calls ranked by the total number of errors.
Result example
Top Ten Transactions by Type
When run on the dsiTransactionLog, the Top Ten Transaction by Type query returns a ranked list of the largest number of transactions by type, such as API, SQL Statement, or Output.
Query
--Top Ten Transaction by Type-- SELECT dsiTransactionLog.TransactionType, Count(*) AS Total FROM dsiTransactionLog GROUP BY dsiTransactionLog.TransactionType ORDER BY Total DESC;
Result
A list of the top 10 transactions by type, ranked by the number of transactions. The transaction types will vary based on how your solution is implemented, such as the ERP system integrated with your Mobile Enterprise Platform instance.
-
Q: SQL requests (if logged)
-
O: Outputs (These can represent API request in a JDE World environment)
-
A: API request
-
null: typically used when resetting a device record counter
Result example
Top Ten Long Running Transactions
When run on the dsiTransactionLog, the Top Ten Long Running Transactions query returns a ranked list of the top ten longest running transactions, along with the average elapsed time of the transactions.
The elapsed time is a measurement of the amount of time it takes your ERP system to process an API request sent by the Application Server. In a well tuned environment, the average transaction time should be sub-second (sub 1000 milliseconds). Transactions taking longer to run might indicate a need to tune the ERP system or other API processing system.
Query
--Top Ten Long Running Transactions by Average Time-- SELECT TOP 10 dsiTransactionLog.FunctionCallID, avg(elapsedtime) AS AvgTime FROM dsiTransactionLog where transactiontype = 'A' GROUP BY dsiTransactionLog.FunctionCallID ORDER BY AvgTime DESC;
Result
A list of the top 10 longest running transactions ranked by the average elapsed time of the transactions.
The time is expressed in milliseconds so that any number greater than 1000 is more than a second.
Result example
Top Ten Servers by Volume
When run on the dsiTransactionLog, the Top Ten Servers by Volume query returns a ranked list of your Mobile Enterprise Platform servers based on transaction volume.
In a multi-server implementation where a load balancer distributes device connections across multiple servers, this query can help you identify if the load balancing is working properly. In a properly balanced environment the transaction volume between servers should be very close. Large differences between the volumes across servers can indicate that some devices might be using the incorrect server name or IP address.
Query
--Top Ten Servers by Volume-- SELECT TOP 10 dsiTransactionLog.ServerName, Count(*) AS Total FROM dsiTransactionLog GROUP BY dsiTransactionLog.ServerName ORDER BY Total DESC;
Result
A list of the top 10 servers ranked by the total number of executed transactions.
Result example
Top Ten Users with Errors
When run on the dsiTransactionLog, the Top Ten Users with Errors query returns a ranked list of users who submitted transaction requests that resulted in errors. When used with the Top Ten Users by Volume query results, this data can help you identify when a user might benefit from additional training.
Query
--Top Ten Users with Errors-- SELECT TOP 10 dsiTransactionLog.UserID, Count(*) AS Total FROM dsiTransactionLog WHERE dsiTransactionLog.RecordStatus='2' and dsiTransactionLog.transactiontype = 'A' GROUP BY dsiTransactionLog.UserID ORDER BY Total DESC;
Result
A list of the top 10 users ranked by the number of transactions ending in error.
Result example
Top Ten Users by Volume
When run on the dsiTransactionLog, the Top Ten User by Volume query returns a ranked list of users who executed the most transaction requests. This information can help you identify which users are completing the most work. Alternately, you can review results from the Top Ten User by Volume and Top Ten User with Errors queries to identify which users might benefit from additional training to minimize errors.
Query
--Top Ten Users by Volume-- SELECT TOP 10 dsiTransactionLog.UserID, Count(*) AS Total FROM dsiTransactionLog GROUP BY dsiTransactionLog.UserID ORDER BY Total DESC;
Result
A list of the top 10 user IDs ranked by the total number of executed transactions.
Result example
Average, Minimum, and Maximum Function Call Times
When run on the dsiTransactionLog, the Avg, Min, and Max API Processing Time query returns a list of all function calls (API transaction types) submitted within your Mobile Enterprise Platform solution, as well as the average, maximum, and minimum elapsed time of each call. This data provides general performance information about your back office, based on total processing time against the system of record.
When you filter the query by date range using the WhenReceived column in the dsiTransactionLog, you can chart weekly, monthly, or quarterly values.
Query
--Avg. Max, and Min API Processing Time-- SELECT avg(elapsedtime) AS AvgTransTime, max(elapsedtime) AS MaxTransTime, min(elapsedtime) AS MinTransTime FROM dsiTransactionLog where transactiontype = 'A'
Result
A list of the average, maximum, and minimum elapsed time of all function calls (API transaction types) submitted within your Mobile Enterprise Platform solution.
Result example
Function Calls by Hour, by Hour of Day, with Histogram
When run on the dsiTransactionLog, the Function Calls by Hour, by Hour of Day, with Histogram query returns a list of each hour of the day and the total function call transactions received by Mobile Enterprise Platform Application Server during that hour, as well as the average and maximum time it took to process the transactions during that hour.
Query
--Function Calls by Hour, by Hour of Day, with Histogram-- SELECT substring(whenreceived,9,2) HOUR, count(*) COUNT, AVG(Elapsedtime) Average, MAX(Elapsedtime) MAX FROM dsiTransactionLog where recordstatus= '1' and transactiontype= 'A' Group by substring(whenreceived,9,2) Order by substring(whenreceived,9,2);
Result
A list of all hours of the day that business function calls were received, in UTC time, plus the transaction count for that hour and the average and maximum processing time by the ERP system. Additionally there is a histogram to the right that shows a visual representation of the ‘Count’ column. The asterisks are sourced from the title bar row, in the same column as the asterisks shown, with the font in white to match the background (not shown).
To account for a higher or lower range of values in the ‘Count’ column, you need to adjust the calculation that produces the asterisks for each hourly row. The Hour of Day is shown in UTC format and manual manipulation of the hour column. You must resort the table to show the data in local server time.
Result example
Function Calls by Hour with Avg. Time (Last 30 Days)
When run on the dsiTransactionLog, the Function Calls by Hour with Avg. Time (Last 30 Days) query returns a list of all function call transactions received by the Mobile Enterprise Platform Application Server in the past 30 days, as well as the date and hour each transaction was received, and the average time it took to process the transaction.
Query
--Function Calls by Hour with Avg. Time (Last 30 Days)-- SELECT cast(left(WhenReceived, 8)+' '+SUBSTRING(WhenReceived, 9, 2)+':00:00' as datetime) as Date_Hour, COUNT(*) as Total, AVG(ELAPSEDTIME) as Avg_Elapsed_Time FROM dsiTransactionLog WHERE WhenReceived between convert(varchar(25),DATEADD(DAY, -30, GETDATE()),112) ++ replace(convert(varchar(25), DATEADD(DAY, -1, GETDATE()), 108),':','') AND convert(varchar(25),getdate(),112) ++ replace(convert(varchar(25), getdate(), 108),':','') and [TransactionType] = 'A' GROUP BY cast(left(WhenReceived, 8)+' '+SUBSTRING(WhenReceived, 9, 2)+':00:00' as datetime) ORDER BY Date_Hour
Result
A list of all function calls received by the Mobile Enterprise Platform Application Server in the past 30 days, along with the date and hour each transaction was received, and the average time it took to process each transaction.
Result example
Function Calls by Hour with Avg. Time (Last 7 Days)
When run on the dsiTransactionLog, the Function Calls by Hour with Avg. Time (Last 7 Days) query returns a list of all function call transactions received by the Mobile Enterprise Platform Application Server in the past 7 days, as well as the date and hour each transaction was received, and the average time it took to process the transaction.
Query
--Function Calls by Hour with Avg. Time (Last 7 Days)-- SELECT cast(left(WhenReceived, 8)+' '+SUBSTRING(WhenReceived, 9, 2)+':00:00' as datetime) as Date_Hour, COUNT(*) as Total, AVG(ELAPSEDTIME) as Avg_Elapsed_Time FROM dsiTransactionLog WHERE WhenReceived between convert(varchar(25),DATEADD(DAY, -7, GETDATE()),112) ++ replace(convert(varchar(25), DATEADD(DAY, -1, GETDATE()), 108),':','') AND convert(varchar(25),getdate(),112) ++ replace(convert(varchar(25), getdate(), 108),':','') GROUP BY cast(left(WhenReceived, 8)+' '+SUBSTRING(WhenReceived, 9, 2)+':00:00' as datetime) ORDER BY Date_Hour
Result
A list of all function calls received by the Mobile Enterprise Platform Application Server in the past 7 days, along with the date and hour each transaction was received, and the average time it took to process each transaction.
Result example
Function Calls by Hour with Avg. Time (Last 24 Hours)
When run on the dsiTransactionLog, the Function Calls by Hour with Avg. Time (Last 24 Hours) query returns a list of all function call transactions received by the Mobile Enterprise Platform Application Server in the past 24 hours, as well as the date and hour each transaction was received, and the average time it took to process the transaction.
Query
--Function Calls by Hour with Avg. Time (Last 24 Hours)-- SELECT cast(left(WhenReceived, 8)+' '+SUBSTRING(WhenReceived, 9, 2)+':00:00' as datetime) as Date_Hour, COUNT(*) as Total, AVG(ELAPSEDTIME) as Avg_Elapsed_Time FROM dsiTransactionLog WHERE WhenReceived between convert(varchar(25),DATEADD(DAY, -1, GETDATE()),112) ++ replace(convert(varchar(25), DATEADD(DAY, -1, GETDATE()), 108),':','') AND convert(varchar(25),getdate(),112) ++ replace(convert(varchar(25), getdate(), 108),':','') GROUP BY cast(left(WhenReceived, 8)+' '+SUBSTRING(WhenReceived, 9, 2)+':00:00' as datetime) ORDER BY Date_Hour
Result
A list of all function calls received by the Mobile Enterprise Platform Application Server in the past 24 hours, along with the date and hour each transaction was received, and the average time it took to process each transaction.
Result example
Loading...
There was a problem loading this topic