Results 1 to 3 of 3
  1. #1
    Randy_Dennis is offline Member
    Join Date
    Mar 2009
    Location
    Alabama
    Posts
    4
    Rep Power
    0

    Exclamation Issues populating MySQL tables

    We have a JAVA application that we developed to process product returns and have been using that application successfully for years. The application utilizes MySQL as the underlying database. Recently, I have been tasked with updating this application (i.e. newer verions of MySQL, code changes, utilize the newest JAVA version, etc.) so that the app will run compatible with Windows Vista / Windows 7.

    One of the problems that I've encountered is a significant delay when attempting to load the MySQL tables with data that is extracted from our ERP system. Data is downloaded into delimited text files based on certain criteria provided by the end user, a zip file of this downloaded data is created and then imported into the application. The problem that I'm experiencing while testing these changes, etc. is that it takes approximately 13 to 15 minutes to load 13 files into the MySQL database. Under the currently installed and used versions of this application it only takes about 1 to 2 mintues tops to upload the very same data. Most of the 13 files are small (i.e. less that 900 KB). There are five files that are 1.5 MB to almost 5 MB, but the same data will upload fine in the currently installed version of this app, etc. Only after having made changes to the app have I experienced this problem.

    Changes made are:
    Installed new version of MySQL 5.5 Community Edition
    Downloaded and using mysql-connector-java.5.1.14-bin.jar
    Running in Windows 7


    Provided below is the code used to process one of the text files into the corresponding MySQL table: (each of the text files are processed using the same technique)

    public void loadReturnHdr(File file, boolean append)
    throws IOException, FileNotFoundException, SQLException{
    BufferedReader br = new BufferedReader(new FileReader(file));
    if(!append){
    getPsDeleteAllReturnHdrs().execute();
    }
    PreparedStatement stmt = getPsInsertReturnHdr();
    stmt.clearBatch();
    stmt.clearWarnings();
    for(String rcd = br.readLine(); rcd != null; rcd = br.readLine()){
    setPsInsertReturnHdr(stmt, rcd);
    stmt.addBatch();
    }
    try{
    stmt.executeBatch();
    }catch(BatchUpdateException be){/* Ignore duplicate key errors */};

    br.close();
    }

    HERE IS THE setPsInsertRetturnHdr method code:

    private void setPsInsertReturnHdr(PreparedStatement stmt, String rcd) throws SQLException{
    StringTokenizer st = new StringTokenizer(rcd,TAB_NL);
    while(st.hasMoreTokens()){
    stmt.clearParameters();
    stmt.setBigDecimal(1, new BigDecimal(st.nextToken()));
    stmt.setString(2, st.nextToken());
    stmt.setString(3, st.nextToken());
    stmt.setString(4, st.nextToken());
    stmt.setString(5, st.nextToken());
    stmt.setString(6, st.nextToken());
    stmt.setString(7, st.nextToken());
    stmt.setString(8, st.nextToken());
    stmt.setString(9, st.nextToken());
    stmt.setString(10, st.nextToken());
    stmt.setString(11, st.nextToken());
    try{
    stmt.setBigDecimal(12, new BigDecimal(st.nextToken())); }catch(NumberFormatException nfe){
    stmt.setBigDecimal(12, new BigDecimal(0));
    }
    stmt.setString(13, st.nextToken());
    stmt.setString(14, st.nextToken());
    stmt.setString(15, st.nextToken());
    try{
    stmt.setBigDecimal(16, new BigDecimal(st.nextToken()));
    }catch(NumberFormatException nfe){
    stmt.setBigDecimal(16, new BigDecimal(0));
    }
    stmt.setString(17, st.nextToken());
    try{
    stmt.setBigDecimal(18, new BigDecimal(st.nextToken()));
    }catch(NumberFormatException nfe){
    stmt.setBigDecimal(18, new BigDecimal(0));
    }
    stmt.setString(19, st.nextToken());
    stmt.setString(20, st.nextToken());
    stmt.setString(21, st.nextToken());
    stmt.setString(22, st.nextToken());
    stmt.setString(23, st.nextToken());
    stmt.setString(24, st.nextToken());
    stmt.setString(25, st.nextToken());
    stmt.setString(26, st.nextToken());
    stmt.setString(27, st.nextToken());
    stmt.setBigDecimal(28, ZERO);
    stmt.setBigDecimal(29, ZERO);
    stmt.setString(30, BLANK);
    stmt.setBigDecimal(31, ZERO);
    stmt.setBigDecimal(32, ZERO);
    stmt.setString(33, st.nextToken());
    stmt.setString(34, st.nextToken());
    stmt.setString(35, st.nextToken());
    }
    }



    HERE IS THE INSERT STATEMENT

    private PreparedStatement getPsInsertReturnHdr() throws SQLException{
    if(psInsertReturnHdr == null){
    psInsertReturnHdr = conn.prepareStatement(
    "insert into returnhdr values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?)");
    }
    return psInsertReturnHdr;
    }

    I've search several forums, etc. to see if anyone else has experienced any similar type problems but havent' found any solutions.
    Input assistance would be appreciated.

    Thanks,
    Randy

  2. #2
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    6

    Default

    i have found for bulk inserting of things the "load data [local] infile", that reads a .csv file input is really the only successful fast method to do this. where even if having many worker nodes running many threads to read and invoke insert statements one at a time, nothing compares to the internal read of a csv file abilities of the database. and not just mysql, but also for postgresql and H2 databases, reading a .csv file input into a table will always be several orders of magnitude faster.
    so if its possible to transform the data to be loaded into ,csv expression, that might be the way to go.

    another idea, mysql supports a crazy multiple insert syntax

    insert into table (col1, col2,...) values (val1, val2), (vala, valb) , (.., ..)

    where they kind of just abuse the sql syntax to allow you to insert as many rows as you provide touples of ( ) after the values. So in that sense, have your java application generate a SQL file which does a big multi values () kind of insert statement like above, and then have the generated SQL file ran into the database (e.g. using mysql cmd line "source" to read the generated file)..

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default

    You'll have to profile it and see where the bottleneck is.
    Anything else is guesswork.

Similar Threads

  1. populating a JList
    By hariza in forum AWT / Swing
    Replies: 2
    Last Post: 09-30-2010, 04:53 AM
  2. XSD Schema to MySql Tables
    By shellrandhawa in forum XML
    Replies: 0
    Last Post: 09-27-2010, 03:49 AM
  3. Populating a JTable
    By toymachiner62 in forum New To Java
    Replies: 2
    Last Post: 10-13-2009, 06:56 AM
  4. How to use one form to submit data to 2 tables on mysql
    By kwesiaryee in forum New To Java
    Replies: 2
    Last Post: 10-10-2008, 02:41 PM
  5. Replies: 0
    Last Post: 12-24-2007, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •