0

I have an excel file with 50000 rows and I'm updating a MySQL table using hibernate by uploading that excel file from a Java Program. It is taking a huge time to update all the rows. Can it be possible if I divide batches of 100 rows in excel file and assign different batches of rows to different threads to concurrently update the rows in table from multiple threads ?

  • Does your program use transactions? (and is the table InnoDB?) – jkavalik Sep 23 '15 at 16:37
  • No, it is basically a simple java program which is updating the rows in table sequentially from excel. And yes table is InnoDB – Codefool Sep 23 '15 at 16:39
  • hibernate should be able to send (flush) multiple rows to the database and commit many of them in one transaction, but that is probably a question for stackoverflow with showing some code how are you doing it – jkavalik Sep 23 '15 at 16:50
1

You can split your file into multiple files.

Try POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files (Apache POI Project)

Here's an example of how to read an Excel file :

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
   }
0

If updating all 50K rows is a common practice, then you have designed the schema wrong. Please elaborate on the schema; we may be able to suggest ways for the "updates" to happen when you fetch the data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.