0

I have written a code to import .xls and store the data into db. It works fine for small amount of data i.e some hundreds of rows of excel. But if the .xls has say 5000 rows, the importing and storing takes about 1.5 - 2 minutes. Here is the piece of code:

sql = new StringBuffer("insert into bookdetails(bookno");
for (int i = 0; i < header.length; i++) sql.append("," + header[i]);

sql.append(") values(?");
for (int i = 0; i < header.length; i++) sql.append(",?");

sql.append(")");
System.out.println(sql);
psmt = con.prepareStatement(sql.toString());

columnCount = s.getColumns();

// Reading Individual Row Content
for (int i = 1; i < rowCount; i++) {
  // Get Individual Row

  auto =
      getAutoIncrement(
          con,
          "bookdetails"); // getAutoIncrement() just increments the value(as the name suggests).
  psmt.setString(1, auto);

  // s is sheet
  if (s.getCell(0, 0).getContents().length() != 0) {
    for (int j = 0; j < columnCount; j++) {
      rowData = s.getCell(j, i).getContents();

      System.out.println(
          "Header====" + header[j] + "==rowData[j]==" + rowData + "==i==" + i + "==j==" + j);
      // let's say our excel has 4 columns[sno, hello, asd, column]
      if (header[j].equalsIgnoreCase("sno")) {
        psmt.setString(2, rowData);
      } else if (header[j].equalsIgnoreCase("hello")) {
        psmt.setString(3, rowData);
      } else if (header[j].equalsIgnoreCase("asd")) {
        psmt.setString(4, rowData);
      } else if (header[j].equalsIgnoreCase("column")) {
        psmt.setString(5, rowData);
      }
    }
    psmt.addBatch();
    psmt.executeBatch();
  }
}

what measures can I take to make this storing faster.
Can I store the .xls sheet all at once temporarily instead of row-by-row(which takes a lot of time). Any suggestions are welcome.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
JustCurious
  • 344
  • 5
  • 15
  • It's hard to see with the current indentation: are you executing an insert for each row? If so, perhaps put lots of rows into the same insert. – Andy Turner Jan 08 '20 at 07:31
  • Probably the FASTEST solution would be a) throw away the Java code, b) write a VBA macro to export the Excel data as a bunch of SQL "insert" statements, then c) execute the SQL script directly from your database. Another "faster" approach might be if you can can connect directly to your database from Excel. If your database happens to be MSSQL, [SSIS](https://learn.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-ver15) is yet another altnerative. – FoggyDay Jan 08 '20 at 07:33
  • My appologies for the indentation @AndyTurner . Im trying to rectify it and make it more readable. – JustCurious Jan 08 '20 at 07:35
  • You're doing batching wrong. You do `addBatch()` and then instantly `executeBatch()`. You're supposed to do `N` times `addBatch()`, then `executeBatch()`. – Kayaman Jan 08 '20 at 07:40
  • 1
    You can ignore the server prepared statements here, since it becomes relevant only with large amounts of rows (tens of thousands) https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance but it shows how to do the add/execute. Lots of other batching questions around here too to show you how it's done. – Kayaman Jan 08 '20 at 07:44
  • So you're saying that if I move ```addBatch()``` into the for loop, that will store the data into batches and then Apply ```executeBatch()``` . Or something like this? @Kayaman – JustCurious Jan 08 '20 at 07:47
  • Something like that yes. You should look at other batching questions to make sure you get it right. – Kayaman Jan 08 '20 at 07:54

1 Answers1

1

You are doing an executeBatch after each addBatch. Execute the batch at the end. It might be better to do not batches of thousands, so below I do batches of 100 (less memory usage and less heavy data transport by the driver).

int batchSize = 0;

for (int i = 1; i < rowCount; i++) {
  if (batchSize >= 100) {
      batchSize = 0;
      psmt.executeBatch();
      psmt.clearBatch();
  }

  // Get Individual Row

  auto =
      getAutoIncrement(
          con,
          "bookdetails"); // getAutoIncrement() just increments the value(as the name suggests).
  psmt.setString(1, auto);

  // s is sheet
  if (s.getCell(0, 0).getContents().length() != 0) {
    for (int j = 0; j < columnCount; j++) {
      rowData = s.getCell(j, i).getContents();

      System.out.println(
          "Header====" + header[j] + "==rowData[j]==" + rowData + "==i==" + i + "==j==" + j);
      // let's say our excel has 4 columns[sno, hello, asd, column]
      if (header[j].equalsIgnoreCase("sno")) {
        psmt.setString(2, rowData);
      } else if (header[j].equalsIgnoreCase("hello")) {
        psmt.setString(3, rowData);
      } else if (header[j].equalsIgnoreCase("asd")) {
        psmt.setString(4, rowData);
      } else if (header[j].equalsIgnoreCase("column")) {
        psmt.setString(5, rowData);
      }
    }
    psmt.addBatch();
  }
  psmt.executeBatch(); // At the end.
  psmt.close(); // Close
}

Irrelevant, but you should use a StringBuilder i.o. the old, slower StringBuffer.

Joop Eggen
  • 107,315
  • 7
  • 83
  • 138