Thread: Fast BigDecimal
Here's another patch I put together while trying to improve performance for the customer that bumped into the repeated Describe thing yesterday. It speeds up ResultSet.getBigDecimal() with the same fastpath implementation we have for getInt and getLong. I'm testing this with a test program that reads 10000 rows from a table with 100 numeric columns, calling getBigDecimal() on each column. This patch cuts the execution time roughly in half, from 2.4s to 1.2s. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java =================================================================== RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v retrieving revision 1.105 diff -u -r1.105 AbstractJdbc2ResultSet.java --- org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 30 Sep 2008 04:34:51 -0000 1.105 +++ org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 16 Apr 2009 13:01:21 -0000 @@ -2137,6 +2137,65 @@ return val; } + /** + * Optimised byte[] to number parser. This code does not + * handle null values, so the caller must do checkResultSet + * and handle null values prior to calling this function. + * + * @param columnIndex The column to parse. + * @return The parsed number. + * @throws SQLException If an error occurs while fetching column. + * @throws NumberFormatException If the number is invalid or the + * out of range for fast parsing. The value must then be parsed by + * {@link #toBigDecimal(String)}. + */ + private BigDecimal getFastBigDecimal(int columnIndex) throws SQLException, + NumberFormatException { + + byte[] bytes = this_row[columnIndex - 1]; + + if (bytes.length == 0) { + throw FAST_NUMBER_FAILED; + } + + int scale = 0; + long val = 0; + int start; + boolean neg; + if (bytes[0] == '-') { + neg = true; + start = 1; + if (bytes.length > 19) { + throw FAST_NUMBER_FAILED; + } + } else { + start = 0; + neg = false; + if (bytes.length > 18) { + throw FAST_NUMBER_FAILED; + } + } + + while (start < bytes.length) { + byte b = bytes[start++]; + if (b < '0' || b > '9') { + if (b == '.') { + scale = bytes.length - start; + continue; + } else + throw FAST_NUMBER_FAILED; + } + val *= 10; + val += b - '0'; + } + + if (neg) { + val = -val; + } + + return BigDecimal.valueOf(val, scale); + } + public float getFloat(int columnIndex) throws SQLException { checkResultSet(columnIndex); @@ -2161,6 +2220,14 @@ if (wasNullFlag) return null; + Encoding encoding = connection.getEncoding(); + if (encoding.hasAsciiNumbers()) { + try { + return getFastBigDecimal(columnIndex); + } catch (NumberFormatException ex) { + } + } + return toBigDecimal( getFixedString(columnIndex), scale ); }
On Thu, 16 Apr 2009, Heikki Linnakangas wrote: > Here's another patch I put together while trying to improve performance for > the customer that bumped into the repeated Describe thing yesterday. It > speeds up ResultSet.getBigDecimal() with the same fastpath implementation we > have for getInt and getLong. To maintain the same behavior as the current code, you need to handle the period more carefully. You must ensure that there is only one and that there's at least one other number. Consider the case of SELECT '.' OR SELECT '1.......'. Kris Jurka
On Sat, 18 Apr 2009, Kris Jurka wrote: > On Thu, 16 Apr 2009, Heikki Linnakangas wrote: > >> Here's another patch I put together while trying to improve performance for >> the customer that bumped into the repeated Describe thing yesterday. It >> speeds up ResultSet.getBigDecimal() with the same fastpath implementation >> we have for getInt and getLong. > > To maintain the same behavior as the current code, you need to handle the > period more carefully. You must ensure that there is only one and that > there's at least one other number. Consider the case of SELECT '.' OR SELECT > '1.......'. > For some reason I thought this would be tricky. We just need to track the total number for periods seen and error out if > 1 or equal to the total number of characters. I've done that and committed it. Kris Jurka
Kris Jurka wrote: > > > On Sat, 18 Apr 2009, Kris Jurka wrote: > >> On Thu, 16 Apr 2009, Heikki Linnakangas wrote: >> >>> Here's another patch I put together while trying to improve >>> performance for the customer that bumped into the repeated Describe >>> thing yesterday. It speeds up ResultSet.getBigDecimal() with the same >>> fastpath implementation we have for getInt and getLong. >> >> To maintain the same behavior as the current code, you need to handle >> the period more carefully. You must ensure that there is only one and >> that there's at least one other number. Consider the case of SELECT >> '.' OR SELECT '1.......'. >> > > For some reason I thought this would be tricky. We just need to track > the total number for periods seen and error out if > 1 or equal to the > total number of characters. I've done that and committed it. Thanks. One more thing: this accepts "-.", which the normal version doesn't. Patch attached. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java =================================================================== RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v retrieving revision 1.106 diff -c -r1.106 AbstractJdbc2ResultSet.java *** org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 19 Apr 2009 05:18:31 -0000 1.106 --- org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 19 Apr 2009 08:15:03 -0000 *************** *** 2191,2197 **** val += b - '0'; } ! if (periodsSeen > 1 || periodsSeen == bytes.length) throw FAST_NUMBER_FAILED; if (neg) { --- 2191,2198 ---- val += b - '0'; } ! if (periodsSeen > 1 || ! periodsSeen == (neg ? bytes.length - 1 : bytes.length)) throw FAST_NUMBER_FAILED; if (neg) {
On Sun, 19 Apr 2009, Heikki Linnakangas wrote: > Thanks. One more thing: this accepts "-.", which the normal version doesn't. > Patch attached. > Committed. This was a problem for the getFastInt/Long methods as well where they allowed a bare negative sign. Kris Jurka
Hi, i'm just running the unit tests against my database and came across a couple of errors: For example in the date tests, a number of dates are inserted and then read back. In my setup this fails with the wrong values being returned. This seems to be an ordering problem.. The dates are inserted and then the test relies on them being returned in the same order they were inserted. I'm fairly sure that this isn't guaranteed when using SQL, however it looks like it probably worked before. I'm using server version 8.3.5 and the latest cvs jdbc source. Anyone else experienced this?
John Lister wrote: > This seems to be an ordering problem.. The dates are inserted and then > the test relies on them being returned in the same order they were > inserted. I'm fairly sure that this isn't guaranteed when using SQL, Correct. Your tests are broken, you REALLY need an ORDER BY clause if you depend on the order of the result set. Alternately, adjust your tests to not care about the result set order. > however it looks like it probably worked before. The only major change I can personally think of that might've affected that is synchronized scans. If you have more than one backend reading the table at once using a sequential scan, one of them will usually pick up part-way through the table then go back to the beginning once it hits the end. I'm not sure if that's likely to be related to what you're seeing. In any case, you REALLY need an appropriate ORDER BY clause. -- Craig Ringer
>> This seems to be an ordering problem.. The dates are inserted and then >> the test relies on them being returned in the same order they were >> inserted. I'm fairly sure that this isn't guaranteed when using SQL, > > Correct. Your tests are broken, you REALLY need an ORDER BY clause if you > depend on the order of the result set. Alternately, adjust your tests to > not care about the result set order. I thought so... >> however it looks like it probably worked before. > > The only major change I can personally think of that might've affected > that is synchronized scans. If you have more than one backend reading the > table at once using a sequential scan, one of them will usually pick up > part-way through the table then go back to the beginning once it hits the > end. I'm not sure if that's likely to be related to what you're seeing. > > In any case, you REALLY need an appropriate ORDER BY clause. It should be simple to correct the tests, by adding a serial id field and ordering on that... Thanks JOHN