Looping through large result set running very slow (JDBC+PostgreSQL performance issue)

I am working on a project using combination of JAVA and PostgreSQL. During the development I found some performance issue while working with JDBC ResultSet. On small ResultSet with lesser columns and few K rows the loop works great, but when I try to looping through huge ResultSet with > 100K rows and >30 columns it goes very slow and takes forever to finish. I did lot of googling and tried many ideas, but nothing worked for me.

I also tried many tricks of mine and got one of them working for my scenario. I know this is not an optimal way to do this but I haven’t any choice left for now. If I found something which seems much better solution, I’ll definitely replace my code with that.

Following is the bunch of code that works for me.

public String[][] GetResultSet() throws SQLException 
Connection con = null;
Statement smt=null;
ResultSet RstSet=null;
int ColCount=0;
int Rows=0;
int RowIndex=0;
ResultSetMetaData rsmd;
String [][] dataArray=null;
con = DriverManager.getConnection(
ModFunc.decrypt("jdbc:postgresql://localhost/dbname"), ModFunc.decrypt(FFunc.getDBUser()), ModFunc.decrypt(FFunc.getDBPassword()));
smt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

String Query="select coalesce(field1, '')||'!S3PC0L!'||coalesce(field2, '')||'!S3PC0L!'||coalesce(field3, '')||'!S3PC0L!'||coalesce(field4, '')||'!S3PC0L!'||coalesce(field5, '')||'!S3PC0L!'||coalesce(field6, '')||'!S3PC0L!'||coalesce(field7, '')||'!S3PC0L!'||coalesce(field8, '')||'!S3PC0L!'||coalesce(field9, '')||'!S3PC0L!'||coalesce(field10, '')||'!S3PC0L!'||coalesce(field11, '')||'!S3PC0L!'||coalesce(field12, '')||'!S3PC0L!'||coalesce(field13, '')||'!S3PC0L!'||coalesce(field14, '')||'!S3PC0L!'||coalesce(field15, '')||'!S3PC0L!'||coalesce(field16, '')||'!S3PC0L!'||coalesce(field17, '')||'!S3PC0L!'||coalesce(field18, '')||'!S3PC0L!'||coalesce(field19, '')||'!S3PC0L!'||coalesce(field20, '')||'!S3PC0L!'||coalesce(field21, '')||'!S3PC0L!'||coalesce(field22, '')||'!S3PC0L!'||coalesce(field23, '')||'!S3PC0L!'||coalesce(field24, '')||'!S3PC0L!'||coalesce(field25, '')||'!S3PC0L!'||coalesce(field26, '')||'!S3PC0L!'||coalesce(field27, '')||'!S3PC0L!'||coalesce(field28, '')||'!S3PC0L!'||coalesce(field29, '')||'!S3PC0L!'||coalesce(field30, '')||'!S3PC0L!'||coalesce(field31, '')||'!S3PC0L!'||coalesce(field32, '')||'!S3PC0L!'||coalesce(field33, '')||'!S3PC0L!'||coalesce(field34, '')||'!S3PC0L!'||coalesce(field35, '')||'!S3PC0L!'||coalesce(field37, '')||'!S3PC0L!'||coalesce(field7, '')||'!S3PC0L!'||coalesce(field38, '') from huge_table";

//Contact all required columns using some separator (in my case !S3PC0L! ) and prepare Query as above.

RstSet = smt.executeQuery(Query);

Rows = getRowCount(RstSet);
rsmd = RstSet.getMetaData()
ColCount = rsmd.getColumnCount();
dataArray=new String [Rows][ColCount];
while (RstSet.next())
      dataArray[RowIndex]=Rst.getString(1) .split("!S3PC0L!");
// I replaced nested loop for columns with above line and reduced the execution times upto 6 times.
return dataArray;

private static int getRowCount(ResultSet set) throws SQLException 
      int rowCount; 
      int currentRow = set.getRow();
      rowCount = set.last() ? set.getRow() : 0;
      if (currentRow == 0)
      return rowCount; 


No comments:

Post a Comment