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!

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

Quote:

---

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

Quote:

---

nd it don't have indexed column.

---

That can be a big problem when you are selecting/joining on these values with large tables. Further, you have several concat's which will slow down the query.