When implementing the awesome MULTISET operator in jOOQ, its implementation mostly relied on SQL/JSON support of various RDBMS. In short, while standard SQL supports nested collections via ARRAY or MULTISET operators like this: SELECT f.title, MULTISET( SELECT a.first_name, a.last_name FROM actor AS a JOIN film_actor AS fa ON a.actor_id = fa.actor_id WHERE fa.film_id = f.film_id … Continue reading Consider using JSON arrays instead of JSON objects for serialisation
Tag: performance
An Efficient Way to Check for Existence of Multiple Values in SQL
In a previous blog post, we've advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: SELECT count(*) FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = … Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL
The Performance Impact of SQL’s FILTER Clause
I've found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function? https://twitter.com/Ivan73965858/status/1622487080088600576 As a quick reminder, FILTER is an awesome standard SQL extension to filter out values before aggregating them in SQL. … Continue reading The Performance Impact of SQL’s FILTER Clause
What’s Faster? COUNT(*) or COUNT(1)?
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there's really no reason at all why one should be faster than the other. But is the myth justified? Let's measure! How does COUNT(...) work? But … Continue reading What’s Faster? COUNT(*) or COUNT(1)?
Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER
Using the right data type for some calculation sounds like some obvious advice. There are many blogs about using temporal data types for temporal data, instead of strings. An obvious reason is data integrity and correctness. We don't gain much in storing dates as 2019-09-10 in one record, and as Nov 10, 2019 in the … Continue reading Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER
Imperative Loop or Functional Stream Pipeline? Beware of the Performance Impact!
I like weird, yet concise language constructs and API usages https://twitter.com/nipafx/status/1055451667079008256 Yes. I am guilty. Evil? Don't know. But guilty. I heavily use and abuse the java.lang.Boolean type to implement three valued logic in Java: Boolean.TRUE means true (duh) Boolean.FALSE means false null can mean anything like "unknown" or "uninitialised", etc. I know - a … Continue reading Imperative Loop or Functional Stream Pipeline? Beware of the Performance Impact!
Beware of Hidden PL/SQL to SQL Context Switches
I recently stumbled upon a curious query on a customer's productive Oracle database: SELECT USER FROM SYS.DUAL Two things caught my attention: The query was executed many billions of times per month, accounting for about 0.3% of that system's load. That's 0.3% for something extremely silly! I don't think that customer would ever qualify the … Continue reading Beware of Hidden PL/SQL to SQL Context Switches
The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating
Something that has been said many times, but needs constant repeating until every developer is aware of the importance of this is the performance difference between row-by-row updating and bulk updating. If you cannot guess which one will be much faster, remember that row-by-row kinda rhymes with slow-by-slow (hint hint). Disclaimer: This article will discuss … Continue reading The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating
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
The Cost of JDBC Server Roundtrips
Or: Move That Loop into the Server Already! This article will illustrate the significance of something that I always thought to be common sense, but I keep seeing people getting this (very) wrong in their productive systems. Chances are, in fact, that most applications out there suffer from this performance problem - and the fix … Continue reading The Cost of JDBC Server Roundtrips