Mysql query performance issue

Dev Shed Forums Sponsor:

January 6th, 2013, 11:35 PM

siva.wins

Registered User

Join Date: Jan 2013

Posts: 2

Time spent in forums: 55 m 7 sec
Reputation Power: 0

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

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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

siva.wins

Registered User

Join Date: Jan 2013

Posts: 2

Time spent in forums: 55 m 7 sec
Reputation Power: 0

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

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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;

Mysql query performance issue

Developer Shed Advertisers and Affiliates