The jOOQ User Manual Reference Don't do this SQL: SELECT *

previous : next

SQL: SELECT *

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SELECT * syntax has been introduced mostly for convenience of ad-hoc SQL. It's very useful to be able to quickly check out data on a production system to see what's available. For those cases, we often don't care about supplying a meaningful SELECT clause. We just want to project everything, e.g.

SELECT * FROM book

create.select(asterisk()).from(BOOK).fetch();

In real world applications, however, we shouldn't do this practice, neither in SQL, nor with jOOQ. We should limit ourselves to project only those columns that we really need. The key here is to project only what we need, so this isn't about the * (the asterisk as a syntactic token), but it could equally be about listing all columns explicitly, e.g.

SELECT
  id,
  author_id,
  title,
  published_in,
  language_id,
  ...
FROM book

create.select(
          BOOK.ID,
          BOOK.AUTHOR_ID,
          BOOK.TITLE,
          BOOK.PUBLISHED_IN,
          BOOK.LANGUAGE_ID,
          ...)
      .from(BOOK).fetch();

This blog post explains in depth why SELECT * is bad practice (not the asterisk is at fault, but the blind projection of everything, including when you use jOOQ's selectFrom(Table)). The main problem is that you're creating unnecessary, mandatory work on the server:

• Unnecessary, because you're throwing away the data right after fetching it
• Mandatory, because the SQL optimiser doesn't know that, so it must provide the data

This has at least the following effects:

• Disk I/O: The server has to read all the data from disk, which may be offloaded to lob storages or whatever. If your tables are wide, then this can be significant!
• Memory consumption: Both on the server and on the client, you're wasting memory and the associated CPU cycles of transferring data from/to memory, just to discard it again. The server might even cache all this data in a "buffer cache", completely unnecessarily.
• Index usage: So called "covering indexes" cannot be used this way, e.g. when your relationship tables have additional columns other than the foreign keys, the projection of those columns will likely make the query much slower than if you could just have used a covering index.
• Join elimination: Some more advanced SQL transformations are impossible to do, such as the JOIN elimination transformation, where whole joins are removed from your query, as they're provably unnecessary. But they're only unnecessary if you're not projecting anything from a table. If you do, then the JOIN must be executed.

Seems obvious, no? Best not be lazy, design your queries carefully. Again, this blog post explains the topic in depth.

The jOOQ User Manual Reference Don't do this SQL: SELECT *

previous : next