#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep 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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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';
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep 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';
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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;

IMN logo majestic logo threadwatch logo seochat tools logo