Thread: Mysql query performance issue in java batch

Hi,

We are having the java batch job, it will receive the around 250000 records and upload into the mysql database.

table TB10_MESSAGE having aroung 1 crore record and TB60_TMP_MESSAGE_UPLOAD having 250000 records.

Below query executing aroung 6 hours ,

UPDATE TB10_MESSAGE SET TP_PREVIOUS_ACTIVATE_STATUS_DATE = TP_ACTIVATE_STATUS_DATE, TP_PREVIOUS_ACTIVATE_STATUS = TP_ACTIVE_STATUS,TP_ACTIVE_STATUS = 'S', TP_ACTIVATE_STATUS_DATE = NOW(), TP_LAST_REMOVE_DATE = NOW(), TP_QUANTITY = 0 where concat(TP_TAG_GROUPING_ID,TP_GP_NUMBER,TP_PART_NUM BER,TP_PIB_TYPE) NOT IN(select concat(TMP_TAG_GROUPING_ID,TMP_TP_GP_NUMBER,TMP_TP _PART_NUMBER,TMP_TP_PIB_TYPE) from TB60_TMP_MESSAGE_UPLOAD where TMP_TP_DEL IS NULL ) AND TP_PART_NUMBER is not null AND TP_ACTIVE_STATUS != 'S';

But we would to optimize the query. Please help me!

I trying to modify above query as below,

UPDATE TB10_MESSAGE as M
LEFT JOIN TB60_TMP_MESSAGE_UPLOAD as T
ON CONCAT(M.TP_TAG_GROUPING_ID,M.TP_GP_NUMBER,M.TP_PA RT_NUMBER,M.TP_PIB_TYPE)=concat(T.TMP_TAG_GROUPING _ID,T.TMP_TP_GP_NUMBER,T.TMP_TP_PART_NUMBER,T.TMP_ TP_PIB_TYPE)
SET TP_PREVIOUS_ACTIVATE_STATUS_DATE = TP_ACTIVATE_STATUS_DATE, TP_PREVIOUS_ACTIVATE_STATUS = TP_ACTIVE_STATUS,TP_ACTIVE_STATUS = 'S', TP_ACTIVATE_STATUS_DATE = NOW(), TP_LAST_REMOVE_DATE = NOW(), TP_QUANTITY = 0
WHERE concat(T.TMP_TAG_GROUPING_ID,T.TMP_TP_GP_NUMBER,T. TMP_TP_PART_NUMBER,T.TMP_TP_PIB_TYPE) IS NULL
AND TP_PART_NUMBER IS NOT NULL
AND TMP_TP_DEL IS NULL
AND TP_ACTIVE_STATUS != 'S';

Please help me for it!

Originally Posted by copeg

Thread moved out of What's wrong with my code.

This is more of a SQL question than a java question. That being said you are doing a lot of operations within the query (Join, concat, etc...) with a lot of AND's, NOT IN's, etc.... It would help to post the table structure, and list any indexes that you may have

please find the table structure and it don't have indexed column.


Table : TB60_TMP_MESSSAGE_UPLOAD
Field Type Null Key Default Extra
TMP_TAG_PROJECT varchar(4) YES
TMP_TAG_BATCH varchar(4) YES
TMP_TAG_LAYOUT_KEY varchar(3) YES
TMP_TA_PROJECT_MILESTONE varchar(2) YES
TMP_TA_NUMBER varchar(4) YES
TMP_TP_GP_DESIGNATION varchar(60) YES
TMP_TP_GP_NUMBER varchar(9) YES
TMP_TP_GFE varchar(2) YES
TMP_TP_PART_NUMBER varchar(10) YES
TMP_TP_PART_DESIGNATION varchar(60) YES
TMP_TP_TYPE varchar(2) YES
TMP_TP_SUPPLY_CONTRACT_PART char(1) YES
TMP_TP_SUB_ASSEMBLY varchar(3) YES
TMP_TP_QUANTITY decimal(6,0) YES 0
TMP_TAG_GROUPING_ID int(10) unsigned YES MUL
TMP_TP_PIB_TYPE varchar(1) YES
TMP_PID int(10) unsigned NO PRI auto_increment
TMP_TP_DEL varchar(1) YES


Table :TB10_MESSAGE
Field Type Null Key Default Extra
TP_ID decimal(10,0) unsigned NO PRI
TP_GP_NUMBER varchar(10) NO
TP_PART_NUMBER varchar(10) YES
TP_DESIGNATION_FR varchar(40) YES
TP_DESIGNATION_EN varchar(40) YES
TP_PIB_TYPE char(1) YES
TP_CREATION_DATE timestamp YES
TP_GFE varchar(2) YES
TP_TYPE varchar(2) NO
TP_ACTIVE_STATUS char(1) NO
TP_ACTIVATE_STATUS_DATE timestamp YES
TP_SUB_ASSEMBLY varchar(3) YES
TP_QUANTITY decimal(6,0) YES 0
TP_PACKAGING_CODE varchar(10) YES MUL
TP_TAG_GROUPING_ID int(10) unsigned YES MUL
TP_LAST_REMOVE_DATE timestamp YES
TP_LAST_REACTIVATION_DATE timestamp YES
TP_PREVIOUS_ACTIVATE_STATUS_DATE timestamp YES
TP_PREVIOUS_ACTIVATE_STATUS char(1) YES
TP_SUPPLY_CONTRACT_PART char(1) NO
TP_PACKAGING_MODIFICATION_TYPE char(1) YES
TP_PACKAGING_MODIFICATION_DATE timestamp YES