Josh Josh - 1 month ago 5
MySQL Question

MySQL - Improve UPDATE Query Performance For Complex Calculations

I am trying to minimise the calculation / query time for the following UPDATE statement that is embedded in a stored procedure on my server...

SET @EQUIPMENT_VARIABLE = 'B1010-V001';
UPDATE EQUIPMENT e
/*24. MAX COF*/
SET e.MAX_COF = least(COF_SAFETY, COF_HEALTH, COF_ENVIRONMENT, COF_ASSETS)
/*25. RECOMMENDED RBI INTERNAL INTERVAL*/
, e.RECOMMENDED_RBI_INTERNAL_INTERVAL = (SELECT RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL FROM DAMAGE_MECHANISM WHERE EQUIPMENT_ID = e.EQUIPMENT_ID ORDER BY RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL ASC LIMIT 1)
/*26. LAST INSPECTION DATE INTERNAL*/
, e.LAST_INSPECTION_DATE_INTERNAL = (
SELECT a.LAST_INSPECTION_DATE
FROM CML a LEFT JOIN CML_TYPES b ON a.CML_TYPE = b.CML_TYPE
WHERE a.EQUIPMENT_ID = e.EQUIPMENT_ID AND a.STATUS NOT IN('X') AND b.INTERNAL_EXTERNAL = 'INTERNAL'
ORDER BY LAST_INSPECTION_DATE DESC LIMIT 1
)
/*27. NEXT INSPECTION DATE INTERNAL*/
, e.NEXT_INSPECTION_DATE_INTERNAL = DATE_ADD(e.LAST_INSPECTION_DATE_INTERNAL, INTERVAL FLOOR(RECOMMENDED_RBI_INTERNAL_INTERVAL * 365) DAY)
/*28. RECOMMENDED RBI EXTERNAL INTERVAL*/
, e.RECOMMENDED_RBI_EXTERNAL_INTERVAL = (SELECT RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL FROM DAMAGE_MECHANISM WHERE EQUIPMENT_ID = e.EQUIPMENT_ID ORDER BY RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL ASC LIMIT 1)
/*29. LAST INSPECTION DATE EXERNAL*/
, e.LAST_INSPECTION_DATE_EXTERNAL = (
SELECT a.LAST_INSPECTION_DATE
FROM CML a LEFT JOIN CML_TYPES b ON a.CML_TYPE = b.CML_TYPE
WHERE a.EQUIPMENT_ID = e.EQUIPMENT_ID AND a.STATUS NOT IN('X') AND b.INTERNAL_EXTERNAL = 'EXTERNAL'
ORDER BY LAST_INSPECTION_DATE DESC LIMIT 1
)
/*30. NEXT INSPECTION DATE EXTERNAL*/
, e.NEXT_INSPECTION_DATE_EXTERNAL = DATE_ADD(e.LAST_INSPECTION_DATE_EXTERNAL, INTERVAL FLOOR(RECOMMENDED_RBI_EXTERNAL_INTERVAL * 365) DAY)
/*31. END OF LIFE*/
, e.END_OF_LIFE = (
SELECT END_OF_LIFE
FROM CML
WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN('X')
ORDER BY END_OF_LIFE ASC LIMIT 1
)
/*XX. REMEDIATION STATUS*/
, e.REMEDIATION_STATUS = (SELECT GROUP_CONCAT(DISTINCT(REMEDIATION_STATUS)) FROM CML WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN('X'))
, e.REMEDIATION_STATUS =
CASE WHEN e.REMEDIATION_STATUS IS NOT NULL
THEN e.REMEDIATION_STATUS
ELSE 'F'
END
/*XX. MAX ACR*/
, e.MAX_ACR = (SELECT APPLIED_CORROSION_RATE FROM CML WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN ('X') ORDER BY APPLIED_CORROSION_RATE DESC LIMIT 1)
/*XX. MIN REMAINING LIFE*/
, e.MIN_REMAINING_LIFE = (SELECT REMAINING_LIFE_BASED_ON_ACR FROM CML WHERE EQUIPMENT_ID = e.EQUIPMENT_ID AND STATUS NOT IN ('X') ORDER BY REMAINING_LIFE_BASED_ON_ACR ASC LIMIT 1)
WHERE e.EQUIPMENT_ID like @EQUIPMENT_VARIABLE;


Any ideas on how I can improve the query structure to minimise the query time / maximise the UPDATE query performance?

** UPDATE **

Approximate number of records in each major table is as follows:


  • CML_TYPES: 10

  • REMEDIATION_STATUSES: 10

  • INSPECTION: 218,000

  • CML: 2180

  • DAMAGE_MECHANISM: 2180

  • EQUIPMENT: 218



Table database structure is provided below.

/*CREATE TABLES AND SET DATATYPES*/
/*LOOKUP TABLES TO BE ADDED FIRST SO FOREIGN KEY CONSTRAINTS CAN BE ASSIGNED CORRECTLY*/
CREATE TABLE IDMAS_VESSELS.CML_TYPES
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`CML_TYPE` VARCHAR(50),
`INTERNAL_EXTERNAL` enum('INTERNAL', 'EXTERNAL') default NULL,

/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (CML_TYPE),

/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
INDEX CML_TYPES_INDEX (CML_TYPE)

) ENGINE=InnoDB;

CREATE TABLE IDMAS_VESSELS.REMEDIATION_STATUSES
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`REMEDIATION_STATUS` VARCHAR(1),
`DESCRIPTION` VARCHAR(100),

/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (REMEDIATION_STATUS)

/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
-- PRIMARY KEY INDEXED BY DEFAULT. NO OTHER INDEXES REQUIRED.

) ENGINE=InnoDB;

CREATE TABLE IDMAS_VESSELS.EQUIPMENT
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`EQUIPMENT_ID` VARCHAR(20) NOT NULL,
`EQUIP_TYPE` VARCHAR(50),
`STATUS_OF_VESSEL` enum('ACTIVE', 'INACTIVE') NOT NULL default 'ACTIVE',
`REMEDIATION_STATUS` VARCHAR(10),
`MIN_REMAINING_LIFE` DOUBLE,
`MAX_ACR` DOUBLE,
`END_OF_LIFE` DATE,
`AREA` VARCHAR(50),
`EQUIP_FROM` VARCHAR(50),
`EQUIP_TO` VARCHAR(20),
`CONTENTS` VARCHAR(50),
`COMMENTS` TEXT,
`MATERIAL` VARCHAR(50),
`MATERIAL_SPECIFICATION` VARCHAR(2),
`CORROSION_ALLOWANCE (MM)` DOUBLE(2,2),
`CONSTRUCTION_CODE` VARCHAR(20),
`DESIGN_PRESSURE_MPA` DOUBLE,
`DESIGN_TEMPERATURE_DEGC` DOUBLE,
`PID` VARCHAR(20),
`DRAWING_REF` VARCHAR(20),
`OPERATING_PRESSURE_KPA` DOUBLE,
`OPERATING_TEMP_DEGC` DOUBLE,
`CAPACITY_L` DOUBLE,
`CAPACITY_MPA_PER_L` DOUBLE,
`MAXIMUM_ALLOWABLE_WORKING_PRESSURE_MPA` DOUBLE,
`MAXIMUM_ALLOWABLE_WORKING_TEMPERATURE_DEGC` DOUBLE,
`DESIGN_STRENGTH_MPA` DOUBLE,
`JOINT_EFFICIENCY` DOUBLE,
`COF_SAFETY` INT(1),
`COF_HEALTH` INT(1),
`COF_ENVIRONMENT` INT(1),
`COF_ASSETS` INT(1),
`MAX_COF` INT(1),
`HAZARD_LEVEL` CHAR(1),
`CONTENTS_CLASS` VARCHAR(50),
`INSTALLATION_YEAR` DATE,
`INTERIOR_COATING` VARCHAR(50),
`EXTERIOR_COATING` VARCHAR(50),
`EXTERIOR_INSUL` VARCHAR(50),
`MANUFACTURER` VARCHAR(50),
`MANUFACTURE_DATE` DATE,
`MANUFACTURE_NO` VARCHAR(50),
`INTERNAL_ANODES` VARCHAR(50),
`ASSOC_PSV` VARCHAR(50),
`MAINT_ITEM_CODE` VARCHAR(50),
`SERV_TYPE` VARCHAR(50),
`EQUIP_CLASS` VARCHAR(50),
`EQUIP_SUBCLASS` VARCHAR(50),
`PROCESS_UNIT` VARCHAR(50),
`RECOMMENDED_RBI_INTERNAL_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_INTERNAL` DATE,
`NEXT_INSPECTION_DATE_INTERNAL` DATE,
`RECOMMENDED_RBI_EXTERNAL_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_EXTERNAL` DATE,
`NEXT_INSPECTION_DATE_EXTERNAL` DATE,
`DESIGN_LIFE` DOUBLE,

/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (EQUIPMENT_ID),

/*INDEX FIELDS TO INCREASE PERFORMANCE REDUCES NUMBER OF ACCESSES (N) FROM N to LOG2(N) I.E. AN EXPONENTIAL DECREASE
*APPLYING THESE INDEXES TO PRIMARY KEY FIELDS ALSO REMOVES THE REQUIREMENT FOR THE SYSTEM TO SEARCH FOR DUPLICATES
*INDEXES ONLY INCREASE THE DISK SPACE REQUIRED OF THE DATABASE.
*ALL DATABASE SEARCHING SHOULD ALWAYS BE DONE ON THE PRIMARY KEY INDEXED FIELD.
*/
INDEX EQUIPMENT_INDEX (EQUIPMENT_ID)

) ENGINE=InnoDB;

CREATE TABLE IDMAS_VESSELS.DAMAGE_MECHANISM
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`DAMAGE_MECHANISM_ID` VARCHAR(50) NOT NULL,
`EQUIPMENT_ID` VARCHAR(20),
`DESCRIPTION` VARCHAR(50),
`REMEDIATION_STATUS` VARCHAR(10),
`DAMAGE_MECHANISM_TYPE` enum('QUALITATIVE', 'QUANTITATIVE') default NULL,
`INSPECTION_STRATEGY` VARCHAR(20),
`AGE_RELATED` enum('YES', 'NO') NOT NULL default 'NO',
`FEASIBLE` enum('YES', 'NO') NOT NULL default 'NO',
`NOTES_ON_FEASIBILITY` TEXT,
`MIN_REMAINING_LIFE` DOUBLE,
`MAX_ACR` DOUBLE,
`MINIMUM_CALCULATED_RBI_INTERVAL` DOUBLE,
`MAXIMUM_CRITICALITY_RATING` VARCHAR(20),
`RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_INTERNAL` DATE,
`NEXT_INSPECTION_DATE_INTERNAL` DATE,
`NOTES_ON_RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL` TEXT,
`RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL` DOUBLE,
`LAST_INSPECTION_DATE_EXTERNAL` DATE,
`NEXT_INSPECTION_DATE_EXTERNAL` DATE,
`NOTES_ON_RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL` TEXT,

/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (DAMAGE_MECHANISM_ID, EQUIPMENT_ID),
FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(EQUIPMENT_ID),

/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
INDEX DAMAGE_MECHANISM_INDEX (EQUIPMENT_ID, DAMAGE_MECHANISM_ID)

) ENGINE=InnoDB;

CREATE TABLE IDMAS_VESSELS.CML
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`CML_ID` INT NOT NULL,
`CML_LOCATION` VARCHAR(20),
`STATUS` enum('C','X','A','U') NOT NULL default 'C',
`REMEDIATION_STATUS` VARCHAR(1),
`EQUIPMENT_ID` VARCHAR(20),
`DAMAGE_MECHANISM_ID` VARCHAR(50),
`CML_TYPE` VARCHAR(50),
`LAST_INSPECTION_DATE` DATE,
`NOMINAL_DIAMETER_MM` DOUBLE,
`OUTSIDE_DIAMETER_MM` DOUBLE,
`MEAN_DIAMETER_MM` DOUBLE,
`NOMINAL_WALL_THICKNESS_MM` DOUBLE,
`MINIMUM_REMAINING_WALL_THICKNESS_MM` DOUBLE,
`DEPTH_OF_LOSS_FROM_NWT_MM` DOUBLE,
`THICKNESS_WALL_LOSS_PERCENTAGE` DOUBLE,
`PHOTO` INT,
`MINIMUM_ALLOWABLE_WALL_THICKNESS_MM` DOUBLE,
`ACTUAL_CORROSION_RATE_MM_PER_YEAR` DOUBLE,
`MAX_CALCULATED_CORROSION_RATE` DOUBLE,
`DAMAGE_LOOP_MAXIMUM_CORROSION_RATE` DOUBLE,
`APPLIED_CORROSION_RATE` DOUBLE,
`APPLIED_CORROSION_RATE_NOTES_AND_ENTERED_BY` TEXT,
`FAILURE_MODE` VARCHAR(50),
`REMAINING_LIFE_BASED_ON_ACR` DOUBLE,
`END_OF_LIFE` DATE,
`LR_LD_APPLIED` DOUBLE,
`POF_APPLIED` INT(1),
`DAMAGE_MECHANISM_VISUAL` TEXT,
`FAILURE_MODE_VISUAL` TEXT,
`POF_APPLIED_VISUAL` INT(1),
`POF_APPLIED_VISUAL_COMMENT` TEXT,
`CRITICALITY_RATING_VISUAL` INT(2),
`CRITICALITY_RATING_APPLIED` VARCHAR(10),
`DEGRADATION_MECHANISM` DOUBLE,
`RELIABLE_MONITORING` DOUBLE,
`MULTIPLE_INSPECTIONS` DOUBLE,
`CONFIDENCE_SCORE` DOUBLE,
`CONFIDENCE_RATING` VARCHAR(50),
`INSPECTION_INTERVAL_FACTOR_APPLIED` DOUBLE,
`RISK_BASED_INSPECTION_INTERVAL_APPLIED_YEARS` DOUBLE,
`RECOMMENDED_REMEDIATION` TEXT,

/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (EQUIPMENT_ID, CML_ID),
FOREIGN KEY (EQUIPMENT_ID, DAMAGE_MECHANISM_ID) REFERENCES DAMAGE_MECHANISM(EQUIPMENT_ID, DAMAGE_MECHANISM_ID),
FOREIGN KEY (CML_TYPE) REFERENCES CML_TYPES(CML_TYPE),
FOREIGN KEY (REMEDIATION_STATUS) REFERENCES REMEDIATION_STATUSES(REMEDIATION_STATUS),

/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
INDEX CML_INDEX (EQUIPMENT_ID, CML_ID)

) ENGINE=InnoDB;

CREATE TABLE IDMAS_VESSELS.INSPECTION
( /*FIELD NAME*/ /*DATA TYPE AND RESTRICTIONS*/
`INSPECTION_NO` INT NOT NULL,
`STATUS` enum('P','A','S','R') NOT NULL default 'P',
`CML_ID` INT NOT NULL,
`EQUIPMENT_ID` VARCHAR(20),
`INSPECTION_TYPE` VARCHAR(50),
`SCOPE_COMMENTS` TEXT,
`NOMINAL_WALL_THICKNESS` DOUBLE,
`MINIMUM_REMAINING_WALL_THICKNESS` DOUBLE,
`WALL_LOSS` DOUBLE,
`SERVICE_YEARS` DOUBLE,
`CORROSION_RATE_ACTUAL_SHORT_TERM_MM_PER_YEAR` DOUBLE,
`CONDITION` VARCHAR(50),
`INSPECTION_DATE` DATE,
`NDT_REPORT_NUMBER` VARCHAR(20),
`INSPECTION_COMMENTS` TEXT,
`INSPECTION_SCOPE` TEXT,
`WORK_ORDER` TEXT,
`INPECTOR_NAME` VARCHAR(50),
`INPECTION_COMPANY` VARCHAR(50),
`PHOTO` INT NOT NULL AUTO_INCREMENT,

/*PRIMARY AND FOREIGN KEY DEFINITIONS*/
PRIMARY KEY (EQUIPMENT_ID, CML_ID, INSPECTION_NO),
FOREIGN KEY (EQUIPMENT_ID, CML_ID) REFERENCES CML(EQUIPMENT_ID, CML_ID),

/*ADD TABLE INDEXES TO IMPROVE PERFORMANCE*/
/*INDEX ON PHOTO FIELD REQUIRED TO ALLOW AUTO INCREMENTING OF FIELD*/
INDEX INSPECTION_INDEX (EQUIPMENT_ID, CML_ID, INSPECTION_NO),
INDEX `PHOTO` (`PHOTO`)
-- INDEX INSPECTION_DATE (INSPECTION_DATE)

) ENGINE=InnoDB;

Answer

Suggested code

  1. I assumed the like can be replace with =.
  2. The main key here is to retrieve only once from each source table.

SET @EQUIPMENT_VARIABLE = 'B1010-V001'
;


UPDATE      EQUIPMENT e 

            left join  (SELECT      min(RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL)  as MIN_RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL /*25. RECOMMENDED RBI INTERNAL INTERVAL*/
                                   ,min(RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL)  as MIN_RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL /*28. RECOMMENDED RBI EXTERNAL INTERVAL*/

                        FROM        DAMAGE_MECHANISM 

                        WHERE       EQUIPMENT_ID = @EQUIPMENT_VARIABLE
                        ) dm

            on  true

            left join  (SELECT      max(case b.INTERNAL_EXTERNAL when 'INTERNAL' then a.LAST_INSPECTION_DATE end) MIN_LAST_INSPECTION_DATE_INTERNAL    /*26. LAST INSPECTION DATE INTERNAL*/
                                   ,max(case b.INTERNAL_EXTERNAL when 'EXTERNAL' then a.LAST_INSPECTION_DATE end) MIN_LAST_INSPECTION_DATE_EXTERNAL    /*29. LAST INSPECTION DATE EXERNAL*/
                                   ,min(END_OF_LIFE)                            as min_END_OF_LIFE                          /*31. END OF LIFE*/
                                   ,GROUP_CONCAT(DISTINCT REMEDIATION_STATUS)   as GROUP_CONCAT_DISTINCT_REMEDIATION_STATUS /*XX. REMEDIATION STATUS*/
                                   ,max(APPLIED_CORROSION_RATE)                 as max_APPLIED_CORROSION_RATE
                                   ,min(REMAINING_LIFE_BASED_ON_ACR)            as min_REMAINING_LIFE_BASED_ON_ACR

                        FROM        CML a JOIN CML_TYPES b ON a.CML_TYPE = b.CML_TYPE

                        WHERE       a.EQUIPMENT_ID = @EQUIPMENT_VARIABLE AND a.STATUS NOT IN('X')
                        ) ct

            on  true

SET         e.MAX_COF                           = least(COF_SAFETY, COF_HEALTH, COF_ENVIRONMENT, COF_ASSETS)                                            /*24. MAX COF*/            
           ,e.RECOMMENDED_RBI_INTERNAL_INTERVAL = dm.MIN_RECOMMENDED_RBI_INTERNAL_INSPECTION_INTERVAL                                                   /*25. RECOMMENDED RBI INTERNAL INTERVAL*/        
           ,e.LAST_INSPECTION_DATE_INTERNAL     = ct.MIN_LAST_INSPECTION_DATE_INTERNAL                                                                  /*26. LAST INSPECTION DATE INTERNAL*/
           ,e.NEXT_INSPECTION_DATE_INTERNAL     = DATE_ADD(e.LAST_INSPECTION_DATE_INTERNAL,INTERVAL FLOOR(RECOMMENDED_RBI_INTERNAL_INTERVAL*365) DAY)   /*27. NEXT INSPECTION DATE INTERNAL*/
           ,e.RECOMMENDED_RBI_EXTERNAL_INTERVAL = dm.MIN_RECOMMENDED_RBI_EXTERNAL_INSPECTION_INTERVAL                                                   /*28. RECOMMENDED RBI EXTERNAL INTERVAL*/
           ,e.LAST_INSPECTION_DATE_EXTERNAL     = ct.MIN_LAST_INSPECTION_DATE_EXTERNAL                                                                  /*29. LAST INSPECTION DATE EXERNAL*/
           ,e.NEXT_INSPECTION_DATE_EXTERNAL     = DATE_ADD(e.LAST_INSPECTION_DATE_EXTERNAL,INTERVAL FLOOR(RECOMMENDED_RBI_EXTERNAL_INTERVAL*365) DAY)   /*30. NEXT INSPECTION DATE EXTERNAL*/  
           ,e.END_OF_LIFE                       = ct.min_END_OF_LIFE                                                                                    /*31. END OF LIFE*/
           ,e.REMEDIATION_STATUS                = coalesce(ct.GROUP_CONCAT_DISTINCT_REMEDIATION_STATUS,'F')                                             /*XX. REMEDIATION STATUS*/
           ,e.MAX_ACR                           = ct.max_APPLIED_CORROSION_RATE                                                                         /*XX. MAX ACR*/
           ,e.MIN_REMAINING_LIFE                = ct.min_REMAINING_LIFE_BASED_ON_ACR                                                                    /*XX. MIN REMAINING LIFE*/

WHERE       e.EQUIPMENT_ID = @EQUIPMENT_VARIABLE
;