[liorb]

Hi, I have a performance issue with PreparedStatement, my code is attached,it is very simple, it inserts simple beans into a DB where each bean is a line in the DB.
I tested it 1420 beans, it took around 2 minutes no matter How I did it.
I'm using tomcat5.0 and Mysql5.0.

I tried both executing the PS each time the loop runs (execution for each bean), here I'm trying to batch every 100 beans, execute them and then move to the next 100. it seems like a lot of time is spent preparing the statement while inserting the data into the DB is relatively fast.

I'm relatively new to java so please feel free to comment and tell me what I might be doing wrong, thanks in advance, Lior.

Code:

public static  boolean insertList(ArrayList dwhBeabList, String logFileName) throws Exception   {  
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ArrayList failedBeans = new ArrayList();
	    
	    try{
		     if(dwhBeabList != null){     
		    	 conn = DBManager.getConnection();        
		         StringBuffer query = new StringBuffer();
		         query.append("insert into "+TABLE_NAME+"(");
		         query.append("full_log_id, ");
		         /***following insertion 18 fields into the query***/
		         query.append("log_file_name, db_insert_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                         /**attach the query into the PS**/
		         ps = conn.prepareStatement(query.toString());
		         DWHBean dwhBean = null;
		         logger.error("srating insering the beans in to the DB");
		         int dwhBeanCounter = 0;
			 /**around 2 minutes are spent inside this loop***/
		         for(int i = 0; i< dwhBeabList.size(); i++){
		        	 try{
			        	 dwhBean = (DWHBean) dwhBeabList.get(i);
			        	 if(dwhBean != null){
				        	ps.setString(2,dwhBean.getUserId());
						/**following insertion of 18 params from the bean into the PreparedStatment**/
			        		/**Here the beans properties are inserted into a batch, this takes 15 seconds for 100 beans**/	 
				        	if(dwhBeanCounter<99){
						/**it takes 3 seconds to actually insert the data from 100 beans into the DB**/
				        		 ps.addBatch();
				        		 dwhBeanCounter++;
				        	 }else{
				        		 ps.executeBatch();
				        		 ps.clearBatch();
				        		 dwhBeanCounter=0;
				        	 }
			        	 }
		        	 }catch(Exception ex){
		        		 
		        	 }
		         }
		         if(dwhBeanCounter<99 && dwhBeanCounter >0){
		        	 ps.executeBatch();
		         }
		     }
	    }