Slow ResultSet

[baden]

As not to have nested ResultSets, I am calling an ArrayList containing objects of the first ResultSet, finding a valid sub-set of data (in this case, part orders (OHORDn)) then saving (setting) the value in the first ArrayList ( aServicesH ).

The result set is contained within a loop - which takes too long. Commenting out (and related text) "rsParts = stmt.executeQuery(sqlParts);" allows it to run just fine.


try
{
stmt = conn.createStatement();
for (int x=0; x<aServicesH.size(); x++)
{
aPartNos = new ArrayList();

sqlParts = "SELECT OHORD# AS OHORDn " +
"FROM FRNDTA020.OHL93 "+
"WHERE OHOTYP='SI' " +
"AND OHFFN1="+ ((ServicesH)aServicesH.get(x)).getSRVNSH(); // grab service order number
// DEBUG: System.out.println("Parts: "+sqlParts);
System.out.println("getting parts... "+sqlParts);

rsParts = stmt.executeQuery(sqlParts); // why does this take so long?

while (rsParts.next())
{
System.out.println("part:"+x);
aPartNos.add(rsParts.getString("OHORDn"));
}


aPartNos.trimToSize();
//add parts arraylist to object
((ServicesH)aServicesH.get(x)).setPartNos(aPartNos);
} //for
}
catch (Exception ex)
{
ex.printStackTrace();
}


Suggestions please?

 

[stefanwagner]

Did you test the same query on an raw sql-interface? (sqlplus, ...)

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[sedj]

>>>>> // why does this take so long?

The query is probably large ...

 

[baden]

The result is not that large - I guess the question here is, because " rsParts = stmt.executeQuery(sqlParts); " is inside a FOR loop - this may be what's causing the slowdown - but I am reusing the same ResultSet object, not creating a new one each time.

 

[sedj]

As stefan said - how long does it take to execute in a sql console session ?
The result might not be large, but the query may take a long time to execute because the table has millions of records or somehting ....

 

[stefanwagner]

A prepared Statement could be faster:

String ps = "SELECT OHORD# AS OHORDn " +
	       "FROM FRNDTA020.OHL93 "+
	       "WHERE OHOTYP = 'SI' " +
	       "AND OHFFN1 = ? "; 
PreparedStatement pstmt = con.prepareStatement(ps);
 
for (int x=0, y = aServicesH.size(); x < y; x++)
{
	int ohffn = ((ServicesH)aServicesH.get(x)).getSRVNSH();
	pstmt.setInt (1, ohffn);
	// ...

Another test:

String ps = "SELECT OHORD# AS OHORDn, ohffn " +
	       "FROM FRNDTA020.OHL93 "+
	       "WHERE OHOTYP = 'SI' " +
	       "AND OHFFN1 IN ("; 
String bucket = "";
for (int x=0, y = aServicesH.size(); x < y; x++)
{
	int ohffn = ((ServicesH)aServicesH.get(x)).getSRVNSH();
	if (x > 0) bucket += ", ";
	bucket += ohffn;
}
bucket += ")";
// ... AND ohffn1 IN (7, 19, 23, 33, 7981926)
// ...

Is an index on ohffn1?
Which database?
As sedj said: how many rows? how many matches?

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[dbleyl]

It's best to release external resources after you have used them, and use the least consuming approach (your code goes in one direction, and doesn't modify the rs...)

Check your locking and cursor strategy - you might be fighting for locks at the database level.

Change:
stmt = conn.createStatement();
To:
stmt= conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READONLY);
Add:
rsParts.close();
After using it.