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?)– jkavalikSep 23, 2015 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– CodefoolSep 23, 2015 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– jkavalikSep 23, 2015 at 16:50
2 Answers
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) {
}
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.