One of the biggest advantages of using jOOQ is that you can change all of your complex application's generated SQL with just a few lines of code. In this article, we'll look into how to solve some common bind peeking issues just like that, without touching your application code, without the need to explain this … Continue reading How to Prevent Execution Plan Troubles when Querying Skewed Data, with jOOQ
Tag: Bind variables
When Using Bind Variables is not Enough: Dynamic IN Lists
In a previous blog post, I wrote about why you should (almost) always default to using bind variables. There are some exceptions, which I will cover in another follow-up post, but by default, bind variables are the right choice, both from a performance and from a security perspective. In this article, I will show an … Continue reading When Using Bind Variables is not Enough: Dynamic IN Lists
Why SQL Bind Variables are Important for Performance
A common problem with dynamic SQL is parsing performance in production. What makes matters worse is that many developers do not have access to production environments, so they are unaware of the problem (even if there's nothing new about this topic). What exactly is the problem? Execution plan caches Most database vendors these days ship … Continue reading Why SQL Bind Variables are Important for Performance
When to Use Bind Values, and When to Use Inline Values in SQL
Users of jOOQ, PL/SQL, T-SQL are spoiled as they hardly ever need to worry about bind values. Consider the following statements: Using jOOQ public int countActors(String firstName, String lastName) { return ctx.selectCount() .from(ACTOR) .where(ACTOR.FIRST_NAME.eq(firstName)) .and(ACTOR.LAST_NAME.eq(lastName)) .fetchOneInto(int.class); ); } The method parameters firstName and lastName will be automatically mapped to bind values in the generated SQL … Continue reading When to Use Bind Values, and When to Use Inline Values in SQL
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?
Are You Binding Your Oracle DATEs Correctly? I Bet You Aren’t
We all know that Oracle's DATE is not really a date as in the SQL standard, or as in all the other databases, or as in java.sql.Date. Oracle's DATE type is really a TIMESTAMP(0), i.e. a timestamp with a fractional second precision of zero. Most legacy databases actually use DATE precisely for that, to store … Continue reading Are You Binding Your Oracle DATEs Correctly? I Bet You Aren’t