Projects:Performance Optimization/Specifications

Overview

Performance Optimization is a project focused on fixing multiple issues in many different places of the application.

Detected Issues

Data Grid

Retrieves all data

• Status: Completed
• Bug: 1863256
• Trunk rev.: 1902

Data Grid retrieves all data for the table (with the applied filter) not only the displayed rows.

Fetched unnecessary columns

• Status: Completed
• Bug: 1811346
• Trunk rev.: 2261
• Contributor: eintelau

The dynamically created query for the grid is fetching all the columns in the table while it is not necessary and makes the query to be slower.

Many requests to backend

• Status: To be started

Many requests to the backend are sent from the datagrid.js that are worthless, so performance is damaged.

Others

• Status: To be started
• Use limit and offset for SQL queries where possible.
• Evaluate performance boot achieved by JSON transport instead of XML.

WAD

Remove unnecessary imports in generated sources

• Status: Partially completed - Look for more unused imports
• Bug: 1800172

WAD generated sources contain non-used importations.

XMLEngine

Remove unused XMLEngine templates

• Status: Partially completed - Look for more unused templates
• Trunk rev.: 2038

In XMLEngine there are some templates that are no longer used.

SQL Issues

AD_Column_Identifier function

• Status: To be started
• Bug: 1881978

AD_Column_Identifier function is used to display the identifier of a record, when it is included in a bigger query (for example in infos) it makes the query to be slow.

Current model

Currently SQL query to get identifier is stored in AD_Table. AD_Update_Column_Identifier procedure, which is invoked by wad, updates this SQL statement. AD_Column_Identifier function executes this query to get the identifier for a single record.

The current clause looks like:

SELECT identifier_columns AS COLUMN_IDENTIFIER 
  FROM (SELECT AD_LANGUAGE 
          FROM AD_LANGUAGE 
         WHERE AD_LANGUAGE=:c_language) L, 
        yourTable T 
  WHERE yourTable_ID=:c_ID

SELECT (SELECT COALESCE(TO_CHAR(MAX(TT.Name)), TO_CHAR(T.Name)) 
          FROM yourTable_Trl TT 
         WHERE TT.yourTable_ID=T.yourTable_ID 
           AND TT.AD_LANGUAGE=L.AD_LANGUAGE)  AS COLUMN_IDENTIFIER 
  FROM (SELECT AD_LANGUAGE 
          FROM AD_LANGUAGE 
         WHERE AD_LANGUAGE=:c_language) L, 
        yourTable T 
  WHERE yourTable_ID=:c_ID

The first one is for tables without language translation and the second one for table with it.

In both cases it is not necessary the AD_Language subquery (for translated tables it would be enough passing the :c_language parameter to the first subquery).

Other approaches

Maintain a glossary table

This table would contain the identifier for every record in database.

Pros

• It is faster than the current approach because data is already calculated and it can be joined with other tables.
• Additionally inter-table queries can be performed in an easy way (for example look for every record in database whose identifier match a pattern.

Cons

• Difficulty to maintain. New triggers should be added to every table in order to update this glossary, whenever a new record is created, identifier columns are changed or the value of these columns is updated the glossary table must be updated.

Java Issues

Garbage Collector thread

• Status: Completed
• Bug: 1813644
• Trunk rev.: 2033
• Contributor: villind

Application calls a thread that invokes garbage collector, this was done for old Tomcat version but currently it has non-sense.

Key Map class

• Status: Completed
• Bug: 1887808
• Trunk rev.: 2262

When KeyMap class is instanced it is created a complete structure for the tab (including all its columns, primary key, etc.), this structure is never used, in fact just tab and window names are actually used.

Code style

• Status Started
• Patch

Currently there is inefficient control structures and data types used. The following list is things to avoid and suggestions:

• Loop over Map entries and compare each entry to see what needs to be done
  • Use the map as designed by getting values by key and remember to handle null values.

• Extensive use of String
  • Use StringBuilder where thread safety is not an issue
  • Use StringBuffer where thread safety is needed
  • Use CharSequence for interface definitions

• Multiple passes when the same thing can be done with single pass, for example character entity Replacer and Loop over Map
  • Use direct access instead of for inside for

Javascript Issues

Js Importations

• Status: To be started

Many pages are importing javascript files that are not used.

Compilation Issues

sqlc

• Status: to be started

When xsql files are compiled by sqlc SQL clauses are tested in db, depending on the amount of data in db it can be very slow.

Number of compilation tasks

• Status: to be started

Reduce the number of compilation tasks and improve them to make them more 'intelligent', this is, compile just the necessary things.

Other Issues

Order Product callout

• Status: To be started

SLOrderProduct takes very long in installations with a big number of orders. The problem is the usage of the lastPriceSO column where is calculated the last price used. This column should be deprecated and the calculation removed from the callout.

Database Issues

Selects use functional conditions without corresponding indexes

In many SELECT statement the WHERE condition uses for example UPPER(columnname)=UPPER(value), but there is no corresponding index. This cases the database to select sequence scan instead of index scan.

When this kind of WHERE condition is needed often it may be good to add corresponding functional index.