PredifinedStatment is too slow for me?

[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.

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, ");
		         [COLOR="Red"]/***following insertion 18 fields into the query***/[/COLOR]
		         query.append("log_file_name, db_insert_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                         [COLOR="Red"]/**attach the query into the PS**/[/COLOR]
		         ps = conn.prepareStatement(query.toString());
		         DWHBean dwhBean = null;
		         logger.error("srating insering the beans in to the DB");
		         int dwhBeanCounter = 0;
			 [COLOR="Red"]/**around 2 minutes are spent inside this loop***/[/COLOR]
		         for(int i = 0; i< dwhBeabList.size(); i++){
		        	 try{
			        	 dwhBean = (DWHBean) dwhBeabList.get(i);
			        	 if(dwhBean != null){
				        	ps.setString(2,dwhBean.getUserId());
						[COLOR="Red"]/**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**/	[/COLOR] 
				        	if(dwhBeanCounter<99){
						[COLOR="Red"]/**it takes 3 seconds to actually insert the data from 100 beans into the DB**/[/COLOR]
				        		 ps.addBatch();
				        		 dwhBeanCounter++;
				        	 }else{
				        		 ps.executeBatch();
				        		 ps.clearBatch();
				        		 dwhBeanCounter=0;
				        	 }
			        	 }
		        	 }catch(Exception ex){
		        		 
		        	 }
		         }
		         if(dwhBeanCounter<99 && dwhBeanCounter >0){
		        	 ps.executeBatch();
		         }
		     }
	    }

[liorb]

I found something that might explain my problem, the question now is, what alternatives can I use?

Some databases don't support the concept of prepared statements in on way or another. What happens in these cases is that the JDBC driver itself actually does the work of the PreparedStatement itself. This means that what happens is that the driver parses the statement, does the parameter binding for you and then when you execute it actually sends a regular old SQL statement (with the formatting it has done for you) to the database. As a popular example of a database that does this MySQL would be one.



About PreparedStatement's performance