Re: Record.getValue() very slow

[Lukas Eder]

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

[Lukas Eder]

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.

I have just now released jOOQ 2.6.2. Please consider upgrading and
providing feedback over any performance improvement you may experience

Cheers
Lukas

[Lukas Eder]

> Just downloaded it and got much better times! Getting values from a record
> by field reference is finishing in about 10 seconds now. Thanks a lot!

Hmm, there might still be room for improvement. Would you mind posting
your data access algorithm for me to see whether it differs from my
benchmarks?

Cheers
Lukas