Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: Mysql query performance issue in java batch

  1. #1
    Junior Member
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default 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!


  2. #2
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: Mysql query performance issue in java batch

    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

  3. #3
    Junior Member
    Join Date
    Oct 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Mysql query performance issue in java batch

    Quote Originally Posted by copeg View Post
    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

  4. #4
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: Mysql query performance issue in java batch

    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.

Similar Threads

  1. How to speed up the performance of the java application
    By Sathiyan in forum Java Theory & Questions
    Replies: 4
    Last Post: December 2nd, 2011, 02:09 AM
  2. How Can i put mysql query results into Linked List.
    By Muhanguzi in forum JDBC & Databases
    Replies: 1
    Last Post: June 18th, 2011, 11:03 AM
  3. Problem with Java/MySQL query...
    By RiskyShenanigan in forum JDBC & Databases
    Replies: 2
    Last Post: March 28th, 2011, 03:50 AM
  4. calling a java a class connected to derby from a batch file
    By Reem in forum What's Wrong With My Code?
    Replies: 4
    Last Post: October 31st, 2010, 03:01 PM