Puzzling "Costly type conversion" warnings
Posted by: Craig Carper
Date: June 10, 2005 09:43AM

I enabled useUsageAdvisor in my application, and noticed some questionable warnings in the torrent of output that ensued.

If the value of a DECIMAL column is read using either getObject() or getBigDecimal(), the result is a BigDecimal object, but a warning is issued (in either case):

WARNING: Profiler Event: [WARN] at xetus.test.DBSelectTest.main(DBSelectTest.java:62) duration: 0 ms, connection-id: 0, statement-id: 6, resultset-id: -1, message: Costly type conversion for prepared statement. Column number '1' is MySQL type 'FIELD_TYPE_DECIMAL' but needed to be converted to a String for further processing/conversion. It is more efficient to ensure that the datatype asked for via ResultSet.get*() matches what was returned from the server.

Am I to understand that the driver is converting the DECIMAL value to a String and then to a BigDecimal? Is there a less costly alternative?

A bigger surprise is reading a CHAR or VARCHAR column using either getObject() or getString():

WARNING: Profiler Event: [WARN] at xetus.test.DBSelectTest.main(DBSelectTest.java:64) duration: 0 ms, connection-id: 0, statement-id: 6, resultset-id: -1, message: Costly type conversion for prepared statement. Column number '3' is MySQL type 'FIELD_TYPE_VAR_STRING' but needed to be converted to a String for further processing/conversion. It is more efficient to ensure that the datatype asked for via ResultSet.get*() matches what was returned from the server.

I suspect that in fact there is no costly conversion being performed.

In general, I'm confused about why there would be a loss of efficiency in using getObject() -- doesn't the jdbc standard specify the most natural (and therefore efficient) mapping from SQL to Java types?

Finally, this warning is issued when a prepared statement is executed just once:

WARNING: Profiler Event: [WARN] at xetus.test.DBSelectTest.main(DBSelectTest.java:50) duration: 0 ms, connection-id: 0, statement-id: 6, resultset-id: -1, message: PreparedStatement created, but used 1 or fewer times. It is more efficient to prepare statements once, and re-use them many times

Is it not true than once a prepared statement is created, no other query can be executed on the connection without closing the prepared statement first? In my use case, a statement is prepared each time its executed because its interleaved with other queries using one connection. I know its more efficient if I could prepare it just once, but that's not the situation. Unless I misunderstand, this warning just clutters the output.

Options: ReplyQuote


Subject
Written By
Posted
Puzzling "Costly type conversion" warnings
June 10, 2005 09:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.