First of all, it is important to understand that even servers with many CPUs and huge computing capacity and memory size may have performance issues. JDE allows up to hundreds of paralel users and each of them needs a little bit of server’s CPU as each user’s action has impact on database (reads or writes of records) which takes some time. Moreover, on each system there are hundreds of UBEs (reports) that need to be run daily to perform data manipulations etc. All these small requirements may result in heavy workload of local network and servers.
How to prevent performance issues caused by the development: Each developer should develop applications that both follow business requirements but are efficient. So the prevention is the best solution of the performance issues. However it very often happens that either developers are not experienced enough or they underestimate impact of their programs. In such a case their programs are inefficient, slow and may have serious impact on other processes as well. As a result the whole system is getting slower and an action is needed.
How to make the system faster: To make the system faster, it is necessary to identify the biggest performance issues. At the beginning we may start with slow UBEs (reports) but the question is how to find out if a program can be faster?? We have two options – either we can ask a developer to review the program completely which may take from several hours to several days (for complicated programs) with doubtful results (because it is hard to say the impact of different pieces of logic). Or we can use a tool that will identify the performance problems for us. JdeAnalyzer uses the fact that EnterpriseOne can generate detail log („jdedebug.log“) with information about more or less any action that happens in any program (including all SQL statements, cache operations, function calls etc). Based on this file JdeAnalyzer can exactly calculate the CPU consumtion of all the business functions and SQL statements executed during the processing. Based on this information a developer can easily find the place(s) in the code with highest impact on performance and suggest/perform improvement.
What kind of performance issues are the most typical:
- missing index for an SQL statement
- incorrectly built SQL statement (especially in case of joins, wrong combination of selection and sequencing etc.)
- executing the same function or SQL too many times with the same parameters (typically fetching from UDC)
- incorrect program looping (bug in logic that has no impact on results but on performance)
- generating records to work files that are not needed (usually a conceptual problem, the records are later deleted)
- selecting too many fields from the database
- updating the same record several times
- incorrect passing of parameters to a UBE or function (as a result it selects too many records to process)
- reprocessing the same records many times
- and many others
What are other benefit of using JDE Analyzer:
- quality assurance, during analysis we often find logical problems as well
- pressure on better development (if developers know that their programs are analyzed, they develop more responsibly)
- verification of supplier abilities – a good JDE supplier provides good services with highly professional developers that don’t cause performance issues
How difficult is to use the tool: using the tool is simple however interpreting the results properly and suggesting the right improvements is a work for a very experienced developer with deep knowledge of SQL and databases and with excellent knowledge of EnterpriseOne.
Please find attached example on how the result from my program looks like. It is not difficult to understand. In this simple example the main SQL statement took over 14 secs to return the first record which was almost 20% of the whole processing time. The biggest impact on performance was caused by function GetUDC that was called 3904 times (you may also see that this function reads from F0005D which took 15%). As a solution in this case we removed 3902 calls of GetUDC and called it only twice which made the whole report 2 times faster, then we optimized the main SQL statement so in the end the report was almost 3 times faster then before.
SQL STATEMENTS:
===============
SQL Statement: SELECT FROM CRPDTA/F0901 T0,CRPDTA/F0911 T1,CRPDTA/F0006 T
Calls: 1
Time: 14.61 sec 14.611 sec/call 19.8%
Recs: 1 fetched / 0 not found
From: _main_
SQL Statement: SELECT FROM CRPCTL/F0005D
Calls: 3904
Time: 11.32 sec 0.003 sec/call 15.3%
Recs: 1330 fetched / 2574 not found
From: GetUDC
SQL Statement: SELECT FROM CRPDTA/F0006D
Calls: 488
Time: 1.71 sec 0.004 sec/call 2.3%
Recs: 0 fetched / 488 not found
From: F0006DGetBusinessUnitTranslation
SQL Statement: SELECT FROM CRPDTA/F7611B
Calls: 240
Time: 3.49 sec 0.015 sec/call 4.7%
Recs: 204 fetched / 36 not found
From: _main_
SQL Statement: SELECT FROM CRPDTA/F4111
Calls: 36
Time: 1.64 sec 0.046 sec/call 2.2%
Recs: 36 fetched / 0 not found
From: _main_
FUNCTIONS:
==========
Function: GetUDC
Levels: 4-4
Calls: 3904
Time: 37.56 sec 0.010 sec/call 50.9%
Net: 37.56 sec 0.010 sec/call 50.9%
Function: CheckforAccountExistenceF0901
Levels: 4-4
Calls: 488
Time: 4.23 sec 0.009 sec/call 5.7%
Net: 4.23 sec 0.009 sec/call 5.7%
Function: F0006DGetBusinessUnitTranslation
Levels: 4-4
Calls: 488
Time: 3.30 sec 0.007 sec/call 4.5%
Net: 3.30 sec 0.007 sec/call 4.5%
Total processing time: 74 sec
Zobacz więcej...
- Alokowanie transakcji wynikowych pomiędzy okresami - dokumentacja
