Best way to do this?
in [Java Help]
|
Prev: Const correctness (was Re: Oppinion on 'least priviledge','const correctness', etc.)
Next: How to remotely construct a remote object with RMI
From: harryajh on 22 Jul 2010 12:00 I have to process a large number of database records, examine a column value (can be 10 different values) and set a bean's property depending on that fields values e.g (vastly cut down) ResultSet rs = ps.executeQuery(); while (true) { ret = new myBean(); String propName = rs.getString(2); if (propName.equalsIgnoreCase("licence_number")) { ret.setLicenceNo(rs.getString(3)); } else if (propName.equalsIgnoreCase("return_requirement_id")) { ret.setReturnReqId(rs.getString(3)); } } Hopefully its relativily easy to follow what I'm trying to do, my big problem though is using a "String" to hold the fields value, this obviously is not a good idea and surely for performance reasons neither would - if (rs.getString(2).equalsIgnoreCase("licence_number")) or wouldn't it make much difference getting the field value up to 10 times? A bit lost as to what's the best/efficient way to do this, should I use a StringBuffer every time? would it be garbage collected? - any other ideas? thanks harry
From: Alan Gutierrez on 22 Jul 2010 12:34 harryajh wrote: > I have to process a large number of database records, examine a column > value (can be 10 different values) and set a bean's property depending > on that fields values e.g (vastly cut down) > > ResultSet rs = ps.executeQuery(); > > while (true) > { > ret = new myBean(); > > String propName = rs.getString(2); > > if (propName.equalsIgnoreCase("licence_number")) > { > ret.setLicenceNo(rs.getString(3)); > } > else if (propName.equalsIgnoreCase("return_requirement_id")) > { > ret.setReturnReqId(rs.getString(3)); > } > } > > Hopefully its relativily easy to follow what I'm trying to do, my big > problem though is using a "String" to hold the fields value, this > obviously is not a good idea and surely for performance reasons > neither would - > > if (rs.getString(2).equalsIgnoreCase("licence_number")) > > or wouldn't it make much difference getting the field value up to 10 > times? Probably not, but it looks right to me to put into a local variable something that you are going to reference 10 times locally, and the method chaining looks repetitious. > A bit lost as to what's the best/efficient way to do this, should I > use a StringBuffer every time? would it be garbage collected? - any > other ideas? Now for another installment of micro-optimization theater. JDBC is going to give you a String. That String is going to be created by JDBC. You cannot tell JDBC to reuse a StringBuffer or change the JDBC API. (Cue the poster who shows me how you can use JDBC do just that. The point is that if an API is returning a String there not much you can do about it, and you shouldn't worry about it.) In this microcosm of your application, when you get the String from the ResultSet you are going use that same string for all 10 comparisons. There is no place to insert a StringBuffer or StringBuilder here to make things faster. Neither of them implement equals, so you would have to create a String from them to do the comparison, or far worse, write your own equality function that compares StringBuilder. Short lived objects are cheap to create and collect. You are not building a String, so StringBuilder buys you nothing, or if it buys does you something I'm certain it is not worth the cost in your time. The slow part of this code is going to be the round trip to the database, which might be adjustable, but the driver JDBC will probably do the right thing and chunk up the transfers. That is unless your large number of database records are part of a query that is poorly designed. I'd run the query from the RBMS console get timings, add indexes, try different joins, normalize, denormalize, and hint until it was spewing rows quickly. Basically, by the time you're fussing around with StringBuilder, the real work has already been done, and the time it takes to do the real work is what will determine its speed. IJeff Atwood illustrates the futility of micro-optimization using a String example, so this should really speak to you. http://www.codinghorror.com/blog/2009/01/the-sad-tragedy-of-micro-optimization-theater.html -- Alan Gutierrez - alan(a)blogometer.com - http://twitter.com/bigeasy
From: markspace on 22 Jul 2010 12:34 harryajh wrote: > else if (propName.equalsIgnoreCase("return_requirement_id")) > { > ret.setReturnReqId(rs.getString(3)); ^^^ > } > Hopefully its relativily easy to follow what I'm trying to do, Uh, no. Should that last getString() use a 4 instead of a 3? You use 3 twice in two different places. > problem though is using a "String" to hold the fields value, this > obviously is not a good idea and surely for performance reasons > neither would - > > if (rs.getString(2).equalsIgnoreCase("licence_number")) Not at all sure what you are trying to say or do. Maybe you should hoist the string comparison out of the loop? ResultSet rs = ps.executeQuery(); String id; int propNum; String propName = rs.getString(2); if (propName.equalsIgnoreCase("licence_number")) { id = rs.getString(3); propNum = 0; } else if (propName.equalsIgnoreCase("return_requirement_id")) { id = rs.getString(3); propNum = 1; } while (true) { ret = new myBean(); switch( propNum ) { case 0: ret.setLicenseNo( id ); break; case 1: ret.setReturnReqID( id ); break; } } This avoid string comparisons inside a loop....
From: Alan Gutierrez on 22 Jul 2010 12:40 markspace wrote: > harryajh wrote: > >> else if (propName.equalsIgnoreCase("return_requirement_id")) >> { >> ret.setReturnReqId(rs.getString(3)); > ^^^ >> } > >> Hopefully its relativily easy to follow what I'm trying to do, > > Uh, no. Should that last getString() use a 4 instead of a 3? You use 3 > twice in two different places. > > >> problem though is using a "String" to hold the fields value, this >> obviously is not a good idea and surely for performance reasons >> neither would - >> >> if (rs.getString(2).equalsIgnoreCase("licence_number")) > > Not at all sure what you are trying to say or do. Maybe you should > hoist the string comparison out of the loop? I read the OPs uncompilable code as being a loop over a result set, even though it has that nonsense while(true) which really should read while (rs.next()), but then maybe the OP really wants an infinite loop over a single row of a result set. In which case, it doesn't matter how much faster it runs. -- Alan Gutierrez - alan(a)blogometer.com - http://twitter.com/bigeasy
From: Tom Anderson on 22 Jul 2010 13:10
On Thu, 22 Jul 2010, harryajh wrote: > I have to process a large number of database records, examine a column > value (can be 10 different values) and set a bean's property depending > on that fields values e.g (vastly cut down) > > ResultSet rs = ps.executeQuery(); > while (true) > { > ret = new myBean(); > String propName = rs.getString(2); > if (propName.equalsIgnoreCase("licence_number")) > { > ret.setLicenceNo(rs.getString(3)); > } > else if (propName.equalsIgnoreCase("return_requirement_id")) > { > ret.setReturnReqId(rs.getString(3)); > } > } > > Hopefully its relativily easy to follow what I'm trying to do, my big > problem though is using a "String" to hold the fields value, this > obviously is not a good idea and surely for performance reasons > neither would - > > if (rs.getString(2).equalsIgnoreCase("licence_number")) > > or wouldn't it make much difference getting the field value up to 10 > times? Compared to the cost of going to the database, no, not at all. An alternative approach would be to change your query to include "and prop_name = 'license_number'", then you know that everything that comes back is a license number, and so don't have to check. Then do the same again for the other beans. However, that would rather increase the number of queries you make, which is not likely to aid performance! You could also do an N-way self-outer-join, which would put the values stored against each property name in different columns in the result set. I think the SQL for that would be a bit fiendish, though. tom -- got a DOCTORATE in cold ROCKIN' IT |