Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries

An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number of rows that … Continue reading Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries

JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage

The SQL language has one great advantage over procedural, object oriented, and "ordinary" functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute. How does … Continue reading JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage

SQL IN Predicate: With IN List or With Array? Which is Faster?

Hah! Got nerd-sniped again: https://stackoverflow.com/questions/43099226/how-to-make-jooq-to-use-arrays-in-the-in-clause/43102102 A jOOQ user was wondering why jOOQ would generate an IN list for a predicate like this: Java COLUMN.in(1, 2, 3, 4) SQL COLUMN in (?, ?, ?, ?) ... when in fact there could have been the following predicate being generated, instead: COLUMN = any(?::int[]) In the second case, … Continue reading SQL IN Predicate: With IN List or With Array? Which is Faster?

Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys

There are many many opinions out there regarding the old surrogate key vs. natural key debate. Most of the times, surrogate keys (e.g. sequence generated IDs) win because they're much easier to design: They're easy to keep consistent across a schema (e.g. every table has an ID column, and that's always the primary key) They're … Continue reading Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys

How to Emulate Partial Indexes in Oracle

A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called "filtered index"). That's an index that contains only "parts" of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: CREATE INDEX i ON message WHERE … Continue reading How to Emulate Partial Indexes in Oracle

Top 10 Easy Performance Optimisations in Java

There has been a lot of hype about the buzzword "web scale", and people are going through lengths of reorganising their application architecture to get their systems to "scale". But what is scaling, and how can we make sure that we can scale? Different aspects of scaling The hype mentioned above is mostly about scaling … Continue reading Top 10 Easy Performance Optimisations in Java

Java 8 Friday Goodies: Easy-as-Pie Local Caching

At Data Geekery, we love Java. And as we're really into jOOQ's fluent API and query DSL, we're absolutely thrilled about what Java 8 will bring to our ecosystem. We have blogged a couple of times about some nice Java 8 goodies, and now we feel it's time to start a new blog series, the... … Continue reading Java 8 Friday Goodies: Easy-as-Pie Local Caching