The patch seems good. Here are my test results: Typical query time after JVM warm-up
h2 1.3.176 - 716 ms h2 1.4.185 (but not using MV_STORE) - 350 ms h2 1.4.185 (but not using MV_STORE) with patch - 243 ms Regards, Steve On Tuesday, 10 February 2015 23:36:34 UTC+6:30, Thomas Mueller wrote: > > Hi, > > I have committed my patch, could you please test it? You would need to > compile H2 from the trunk, see > https://code.google.com/p/support/source/checkout > > Regards, > Thomas > > > On Tue, Feb 10, 2015 at 7:45 AM, Thomas Mueller <thomas.to...@gmail.com > <javascript:>> wrote: > >> Hi, >> >> It might make sense to use a concurrent and global cache in the >> StringUtils class instead, similar to StringUtils.softCache >> (StringUtils.fromCacheOrNew). That way, other components could benefit from >> it as well, and small result sets would also benefit. >> >> I will write a patch. >> >> Regards, >> Thomas >> >> >> >> >> On Tuesday, February 10, 2015, Steve McLeod <steve....@gmail.com >> <javascript:>> wrote: >> >>> Hi Thomas and Noel, >>> >>> I've created a patch that caches the results of toUpperEnglish on a per >>> JdbcResultSet basis. In my specific use case (fetching 95 columns by column >>> name over tens of thousands of rows), the query speed-up was extremely >>> significant, and utterly repeatable. Before: 510 milliseconds. After: 230 >>> milliseconds. >>> >>> The patch is attached. I wrote the code, it's mine, and I'm >>> contributing it to H2 for distribution multiple-licensed under the MPL 2.0, >>> and the EPL 1.0 (http://h2database.com/html/license.html). >>> >>> Would either of you mind taking a look to see if you think it is worth >>> committing? In particular, I'm concerned that my use of HashMap may be >>> incorrect if multiple threads are sharing the result set. >>> >>> Regards, >>> >>> Steve >>> >>> >>> >>> >>> On Monday, 9 February 2015 22:45:12 UTC+6:30, Steve McLeod wrote: >>>> >>>> Hi Noel, >>>> >>>> >>>> >>>> On Monday, 9 February 2015 19:14:32 UTC+6:30, Noel Grandin wrote: >>>>> >>>>> Hi >>>>> >>>>> >>>>> On 2015-02-09 02:21 PM, Steve McLeod wrote: >>>>> > final ResultSet resultSet = conn.prepareStatement("SELECT * >>>>> FROM foobar").executeQuery(); >>>>> > int rowCount = 0; >>>>> > while (resultSet.next()) { >>>>> > rowCount++; >>>>> > final int columnCount = >>>>> > resultSet.getMetaData().getColumnCount(); >>>>> >>>>> > for (int column = 1; column <= columnCount; column++) { >>>>> > final String columnName = >>>>> > resultSet.getMetaData().getColumnName(column); >>>>> >>>>> > final int anInt = resultSet.getInt(columnName); >>>>> > } >>>>> > } >>>>> >>>>> You should rather be retrieving the column names once, and then >>>>> retrieving the result-set columns using the getXXX(int >>>>> columnIndex) methods. >>>>> >>>>> >>>> I agree - that would help my (contrived) case. My actual code doesn't >>>> use meta-data to get column names, but sources the column names from >>>> elsewhere. I can certainly fix the problems on my end. >>>> >>>> But I think there is an opportunity to add a tiny performance >>>> improvement in general here. Iterating over a result set, using >>>> getXXX(String columnName) is very common use of JDBC. We're not talking >>>> 10% >>>> speed improvement, but perhaps 1% in some cases? I don't know for sure - >>>> I'm just guessing based on the profiling. >>>> >>>> >>>>> The only other thing I can think of that might speed it up would be to >>>>> modify the current caching code to use a TreeMap >>>>> with a custom comparator and then set the comparator to >>>>> java.lang.String.CASE_INSENSITIVE_ORDER. >>>>> That would avoid the extra String object creation, at the very least. >>>>> >>>> >>>> Sounds good. Will java.lang.String.CASE_INSENSITIVE_ORDER be >>>> satisfactory? The Javadocs state: "Note that this Comparator does not take >>>> locale into account, and will result in an unsatisfactory ordering for >>>> certain locales." I think that means it is acceptable for H2's case, but >>>> I'm not certain. >>>> >>>> >>>> Regards, >>>> >>>> Steve >>>> >>>> -- >>> You received this message because you are subscribed to the Google >>> Groups "H2 Database" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to h2-database+unsubscr...@googlegroups.com. >>> To post to this group, send email to h2-database@googlegroups.com. >>> Visit this group at http://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/d/optout. >>> >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
