Yes, I figured that is why you didn't implement this already. I was hoping
that there were a set of heuristics that could identify if the optimization
was possible. If the cases you mentioned were detected in the AST, then it
would disable the reduction. But that could be error prone as perhaps too
subtle.
Alternatively it seems like a good candidate for a static analysis rule,
like PMD. That would be to detect and hint that it may be requesting too
much data and recommend a reduced query or suppressing the warning.
There might not be any good solutions in the library, but its a common
oversight for simple queries. It would be nice to encourage users to do
better, or try to capture their intent. In the end of the day the slow
query log is still the best (and ignored) tool to fix these.
On Friday, March 10, 2017 at 1:46:14 AM UTC-8, Lukas Eder wrote:
>
> Hi Ben,
>
> When I wrote the post, I was immediately aware of how ironic it is that
> jOOQ also offers selectFrom(), and thus facilitates producing a similar
> kind of overhead as indicated in the post.
>
> There was a previous discussion on this list, which requested that the
> fetch(BOOK.TITLE) feature should influence the actual query being
> generated. At the time, I rejected the idea, because there are a lot of SQL
> features that would break if we changed the behaviour as you suggest:
>
> - FOR UPDATE (pessimistic locking, can only appear in the top level
> statement)
> - DISTINCT / UNION (we'd have to wrapt the query in a derived table, but
> that can have even worse performance implications in some databases)
> - ORDER BY (may reference SELECT columns that aren't really needed in the
> projection. Yet, the presence of ORDER BY invalidates the idea of
> generating a derived table)
> - Hints in Oracle may be misplaced
> - Many other weird SQL language quirks...
>
> As you can see: It would be unwise to let fetch(Field) have any impact on
> the query sent to the server, even if from a client perspective, it may
> look reasonable, and even if for simple queries, it would seem
> straightforward.
>
> The real problem here is that the fetch() method cannot "reason" about the
> type it is called upon. What you really want to do instead is this:
>
>
> List<String> title = db.select(BOOK.TITLE)
> .from(BOOK)
> .where(BOOK.AUTHOR.eq("Robert Heinlein"))
> .fetchXXX(); // Some method that would not wrap
> String in Record1<String>
>
>
> We could, however, have such a generic method on DSLContext, though:
>
> public <T> List<T> fetchXXX(ResultQuery<? extends Record1<? extends T>>
> query);
>
>
> Or, perhaps, there's another solution that I currently don't see.
>
> Lukas
>
> 2017-03-10 1:30 GMT+01:00 <[email protected] <javascript:>>:
>
>> Hi Lukas,
>>
>> In your blog post regarding unnecessary work, I wonder jOOQ could do
>> better when it known the fields the user is consuming.
>>
>> Take for example,
>> List<String> title = db.selectFrom(BOOK).where(BOOK.AUTHOR.eq("Robert
>> Heinlein")).fetch(BOOK.TITLE);
>>
>> This will generate a query like,
>> SELECT book.id, book.author, book.title, book.isbn FROM book WHERE
>> book.author = 'Robert Heinlein';
>>
>> This kind of shorthand is pretty common to see, instead of the more
>> correct query.
>> List<String> title =
>> db.select(BOOK.TITLE).from(BOOK).where(BOOK.AUTHOR.eq("Robert
>> Heinlein")).fetch(BOOK.TITLE);
>>
>> Ideally the renderer would optimize these queries to fetch only the
>> necessary fields. Do you think jOOQ could do this in the simple cases?
>>
>> Cheers,
>> Ben
>>
>> --
>> 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] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
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/d/optout.
