January 6th, 2013, 11:35 PM
-
Mysql query performance issue
Hi,
We are having the 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_NUMBER,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!
January 7th, 2013, 02:45 AM
-
How about something like this...
Code:
UPDATE TB10_MESSAGE x
LEFT
JOIN TB60_TMP_MESSAGE_UPLOAD y
ON y.TP_TAG_GROUPING_ID = x.TP_TAG_GROUPING_ID
AND y.TP_GP_NUMBER = x.TP_GP_NUMBER
AND y.TP_PART_NUMBER = x.TP_PART_NUMBER
AND y.TP_PIB_TYPE = x.TP_PIB_TYPE
AND y.TMP_TP_DEL IS NULL
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 x.TP_PART_NUMBER is not null
AND x.TP_ACTIVE_STATUS != 'S';
January 7th, 2013, 03:27 AM
-
is below query is matched with intial posted query,
UPDATE TB10_MESSAGE M
LEFT
JOIN TB60_TMP_MESSAGE _UPLOAD T
ON (CONCAT(M.TP_TAG_GROUPING_ID,M.TP_GP_NUMBER,M.TP_PART_NUMBER,M.TP_PIB_TYPE)=concat(T.TMP_TAG_GROUPIN G_ID,T.TMP_TP_GP_NUMBER,T.TMP_TP_PART_NUMBER,T.TMP_TP_PIB_TYPE) AND T.TMP_TP_DEL IS NULL)
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 (T.TMP_TAG_GROUPING_ID IS NULL AND T.TMP_TP_GP_NUMBER IS NULL AND T.TMP_TP_PART_NUMBER IS NULL AND T.TMP_TP_PIB_TYPE IS NULL)
AND M.TP_PART_NUMBER IS NOT NULL
AND M.TP_ACTIVE_STATUS != 'S';
January 7th, 2013, 04:06 AM
-
No. But I can see that my query should look more like this (still might be some errors)...
Code:
UPDATE tb10_message x
LEFT
JOIN tb60_tmp_message_upload y
ON y.tmp_tag_grouping_id = x.tp_tag_grouping_id
AND y.tmp_gp_number = x.tp_gp_number
AND y.tmp_part_number = x.tp_part_number
AND y.tmp_pib_type = x.tp_pib_type
AND y.tmp_tp_del IS NULL
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 x.tp_part_number IS NOT NULL
AND x.tp_active_status != 'S'
AND y.tmp_tag_grouping_id IS NULL;