performance issue in iterating through large list of result set

[Priyanka Sumanam]

I have the below code which takes more than a minute to iterate through 5000 records...which is not acceptable performance

while( rs.next() )
{
Object[] resultFields=new Object[colCount];

for(int i=1;i<=colCount;i++)
resultFields[i-1]=rs.getObject(i);
resultList.add(resultFields);

}

Could any one suggest something.

[joy b chakravarty]

It would be helpful if you could larger/complete code snippet.. what is resultList?
If its a List (deduced from the name), then looks like you are performing this action
Lets say colCount is 3
list.add([]{r1.c1, null, null})
list.add([]{r1.c1, r1.c2, null})
list.add([]{r1.c1, r1.c2, r1.c3})
list.add([]{r2.c1, null, null})
list.add([]{r2.c1, r2.c2, null})
list.add([]{r2.c1, r2.c2, r2.c3})

which really doesn't make sense to me.

[Priyanka Sumanam]

Hi Joy,
Here is the full code. Result list is JDBC ResultSet

 public  static ResultSet getResulSet(String sqlquery) {
       Connection conn = jdbcConnection.getConnectionBioSIRT();
       List<Object[]> resultList=new LinkedList <Object[]>();
       ResultSet rs=null;
       PreparedStatement PS=null;
       try {
	   PS =  conn.prepareStatement(sqlquery);
	 
      rs = PS.executeQuery();
      
             
       } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       finally  {
           if( rs != null )  
           {
               try {
                   rs.close();
               }
               catch( Exception e ) {
            	  log.debug( e.getMessage());
            	  e.printStackTrace();
            	   
               }
           }
           if( PS != null ) 
           {
               try {
            	   PS.close();
               }
               catch( Exception e ) {
            	   log.debug( e.getMessage());
             	  e.printStackTrace();
               }
           }


}
       return rs;
    }

Thanks

[Madhan Sundararajan Devaki]

@Priyanka

Your code is incomplete. Also, please enclose your code snippet within

// Your Code.

tags.

[Ashwin Sridhar]

Please post complete code. Also from your initial post, i could find that you are creating Object array everytime in the while loop. you could create the Object array once outside the loop and keeping using the same in the loop.

something like below

Object[] resultFields=null;

while{

resultFields=new Object[colCount]; 

// your code
}

This may or may not improve your performance. but a suggestion

[joy b chakravarty]

i could find that you are creating Object array everytime in the while loop. you could create the Object array once outside the loop and keeping using the same in the loop.
Object[] resultFields=null;
while{
resultFields=new Object[colCount];

The object creation still happens inside the loop, you are just declaring the variable outside, broadening its scope.

[Sudheer Bhat]

You need to find out where the performance issue is. You say that you process around 5000 records. There is very unlikely chance that the moment you say statement.executeQuery, all the records are returned to middle tier from DB.
Check the following.

1. How much time it takes for the PS.executeQuery(); statement to run?
2. Check to see if your iteration over the resultset takes time.

First find out where the performance problem is.
The very first step in performance tuning should be to find out where the problem is, then collect relevant data to support that and most importantly what is your expectation. If you expect to fetch 5000 records from DB, do some processing on that in a 1/1000th of a second, then its probably not possible!!

[joy b chakravarty]

as I had mentioned earlier, your code is doing something like this

list.add([]{r1.c1, null, null})
list.add([]{r1.c1, r1.c2, null})
list.add([]{r1.c1, r1.c2, r1.c3})
list.add([]{r2.c1, null, null})
list.add([]{r2.c1, r2.c2, null})
list.add([]{r2.c1, r2.c2, r2.c3})

I think what you probably wanted is
list.add([]{r1.c1, r1.c2, r1.c3})
list.add([]{r2.c1, r2.c2, r2.c3})

while( rs.next() ){
  Object[] resultFields=new Object[colCount];
  for(int i=1;i<=colCount;i++)
    resultFields[i-1]=rs.getObject(i);
  } 
  resultList.add(resultFields);
}

[Ashwin Sridhar]

joy b chakravarty wrote:

i could find that you are creating Object array everytime in the while loop. you could create the Object array once outside the loop and keeping using the same in the loop.
Object[] resultFields=null;
while{
resultFields=new Object[colCount];

The object creation still happens inside the loop, you are just declaring the variable outside, broadening its scope.

So just single variable is created and object is referenced to that variable.

instead of creating 5000 variables in the memory.