I am importing huge amount of data from CSV files into MSSQL Server 2008. I am using core JDBC (Without any ORM frameworks) and communicating with the DB using the driver 'sqljdbc4.jar' provided by Microsoft.
As of now am importing the records one by one. Which takes considerable amount of time even though am using PreparedStatement.
I am not using batch import since I need to log the exact error into the Error file.
Kindly suggest any an idea to improve the performance with out sacrificing the accurate error logging. And I am forced to do this without any ORM tools.
Here is the sample code:
public void importCSV(){
for (Map<String, String> csvRecord : csvAsList) {
try{
categoryDAO.findByName(categoryName,<PreparedStatement object>);
}
catch(Exception exp){
}
try{
categoryDAO.insert(category,<PreparedStatement object>);
}
catch(Exception exp){
}
}
}
public Category insert(Category category,PreparedStatement pstmt ) throws SQLException{
if (category == null) {
return null;
}
ResultSet rs = null;
try {
pstmt.setInt(1, category.getField1());
pstmt.setString(2, category.getField2());
int result = pstmt.executeUpdate();
if (result < 1) {
return null;
}
rs = pstmt.getGeneratedKeys();
if (rs.next()) {
category.setId(rs.getInt(1));
}
} finally {
if (rs != null)
rs.close();
}
return category;
}
Note:
The time taken for inserting 42390 records + (8 * 42390) select operations is 6.30 mins(approx). Is it a good figure ? Or still is there any way to improve the performance even better ?
Thanks in advance.