Hello, > I'm currently doing a rather large select from a table with 200k rows with 5 > joins for 5 foreign keys. I fetch it into a Result<Record> object, iterate > through it and call Record.getValue multiple times for each row. It's taking > about 2 minutes to do this, which seems unnecessarily long.
Yes, this is a known issue. I've been doing a lot of optimisation in that area recently. Some of which is included in jOOQ 2.6.1, some of which is due for 2.6.2. The optimisations include: hashCode() optimisation for some QueryParts https://github.com/jOOQ/jOOQ/issues/1938 equals() optimisation for some QueryParts https://github.com/jOOQ/jOOQ/issues/2144 Letting generated records access fields by index, not by field reference: https://github.com/jOOQ/jOOQ/issues/2154 Improving Result.into(Class) by reusing reflection artefacts https://github.com/jOOQ/jOOQ/issues/1170 The above issues included various little improvements, which accelerated the following benchmark: @Test public void testBenchmarkFieldAccess() throws Exception { B book = create().newRecord(TBook()); for (int i = 0; i < REPETITIONS_FIELD_ACCESS; i++) { book.setValue(TBook_ID(), i); book.setValue(TBook_AUTHOR_ID(), book.getValue(TBook_ID())); } } Before optimising various equals() methods: 14.3s (1M repetitions), 1018s (100M repetitions) Today (jOOQ 3.0 on GitHub master): 1.02s (1M), 9.33s (100M) > I then tried converting the Result<Record> into a map and got much better > speeds (entire thing took 2 seconds). Yes, the "heavy" operation is extracting a field from a record. If you access fields / values by index or by iterating over them, things get much faster. > For example, let's say I have a table CASES with a field ID (String), and > PERSON_ID (foreign key to PERSONS table, which only has an ID field > (String)). When I do the join and convert it into a map, the > AbstractRecord.intoMaps() method puts only the field.getName() string, which > for CASES.ID and PERSONS.ID is the same ("id"), resulting in the exception. > > Is this a bug in jOOQ [...] No, this works as designed. Not all fields provide full qualification. E.g. some fields are plain SQL fields, or functions, etc. You can still rename columns using Field.as(String) to disambiguate various ID columns in your case. > and is there a better and faster way to iterate > through all rows in a table and get multiple column values for each row? Before all optimisations are published, you could access values by index, not by name or by field reference. Of course, this will lead to a loss of type-safety. I'll try to publish jOOQ 2.6.2 very soon though Cheers Lukas -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
