Results 1 to 2 of 2
  1. #1
    liorb is offline Member
    Join Date
    Jul 2007
    Posts
    2
    Rep Power
    0

    Default PredifinedStatment is too slow for me?

    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.
    Java 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, ");
    		         [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();
    		         }
    		     }
    	    }
    Last edited by liorb; 07-31-2007 at 11:12 AM.

  2. #2
    liorb is offline Member
    Join Date
    Jul 2007
    Posts
    2
    Rep Power
    0

    Default

    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

Similar Threads

  1. slow JasperReport
    By bbq in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2008, 08:34 AM
  2. eclipse very slow
    By katie in forum Eclipse
    Replies: 2
    Last Post: 11-05-2007, 10:20 AM
  3. Oracle Resultset slow
    By Peter in forum JDBC
    Replies: 2
    Last Post: 07-04-2007, 01:56 PM
  4. Export query result, procces is to slow
    By Daniel in forum Enterprise JavaBeans (EJB)
    Replies: 2
    Last Post: 06-28-2007, 06:35 PM
  5. Slow reports in Netbeans
    By Alan in forum NetBeans
    Replies: 1
    Last Post: 05-29-2007, 03:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •