how to make this code perform better

[Phoebe Song]

Hi all,

This is an interview question, how to make the code perform better? Asume all codes work fine. Thanks.


String paymentType = im.getType();

String debitBankCode = im.getField("DEBIT_BANK_CODE");

String beneBankCountry = im.getField("BENE_BANK_COUNTRY");

String paymentMethod = im.getField("PAYMENTMETHOD");



SystemLogicImpl sysLogic = new SystemLogicImpl();

sysLogic.setEnvironment(environment);

DBRecord record = null;

StringBuffer sb = BufferPoolUtil.getBuffer(128);

sb.setLength(0);

try

{

if (sysLogic != null)

{

sb.append("select fieldname from rtgsstprule where paymenttype='");

sb.append(paymentType);

sb.append("' and debitbankcode = '");

sb.append(debitBankCode);

sb.append("' and benebankcountry = '");

sb.append(beneBankCountry);

sb.append("' and paymentmethod='");

sb.append(paymentMethod);

sb.append("'");



ArrayList resultSet = sysLogic.getDBRecords(sb.toString(),"WEB");

Iterator itr = resultSet.listIterator();

while (itr.hasNext())

{

record = (DBRecord)itr.next();

String fieldName = record.getField("fieldname");

if(fieldName.equals(fldName)){

return true;

}



}



}

}
[ April 13, 2008: Message edited by: Bear Bibeault ]

[neilson ramalho]

One possible answer can be the way you assemble your query. I mean, why not to use "binding".
Maybe these topics can help you:
https://coderanch.com/t/302422/JDBC/java/Statement-vs-PreparedStatement

http://faq.javaranch.com/java/PreparedStatement

[Phoebe Song]

Thanks for the help, that's one possiblity. Could it be performance between String and StringBuffer? I was asked one question regarding those two.

[Ernest Friedman-Hill]

Actually, this builds a query that returns all rows which match the given criteria, and then there's a loop that iterates over all the rows, checking if the 'fieldname' column contains a certain value. Why not just specify "fieldname = fldname" in the query, and then just check if the result has any rows at all, rather than fetching ALL the rows (as this code does)? I think that's the major performance issue right there.

[neilson ramalho]

You're right, Ernest. There's no reason for fetching ALL the rows and just after that checks if the query has returned something...
but, it seems that this part of the code just want to check if "fieldname" is a valid column in the table (I know it doesn't make sense) and we know it is because we've got a suggestion in the beginning of the query. Thus, we do not need to iterate through the resultset to know it. I mean, why not to call this method "getFieldValue"? Or use JDBC's syntax (rs.getType(col_type)).At least we could avoid ambiguity. But, considering that the code compiles fine and we are worried about performance, Ernest is definitely right.
Uow, I'm sidetracking... hehe
[ April 08, 2008: Message edited by: neilson ramalho ]

[M John]

you can also try Prepared statement.

[Ben Souther]

"morin dkttar",
Please check your private messages regarding an important administrative matter.
-Ben

[Maris Orbidans]

use StringBuilder instead of StringBuffer

[Anirudh Vyas]

StringBuilder instead of buffer, is working in pre Java 5, this wouldn't be valid, remove ListIterator and try simple for loop. I was working in a project once where we removed iterators (I dont know why you would need a ListIterator any ways, unless you plan to do a previous( ) or anything) we were able to bring down and get performance at par with our tests.

Although iterator is more OO approach imho.

Regards
Vyas, Anirudh

[Jimmy Clark]

You could remove the SELECT statement and call a stored procedure instead.

String query = "getFieldName paymentType,debitBankCode,beneBankCountry, paymentMethod";


Stored procedures typically execute faster than SELECT statements.