The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: SELECT actor_id, name, COUNT(*) FROM ( SELECT actor_id, first_name || ' ' || last_name AS name FROM actor ) AS a JOIN film_actor AS fa USING (actor_id) GROUP BY actor_id, name ORDER … Continue reading LATERAL is Your Friend to Create Local Column Variables in SQL
Tag: SQL Server
Using H2 as a Test Database Product with jOOQ
The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing. If you check out the DB-Engines ranking, it ranks 50th, which is quite impressive, as this rank outperforms products like: CockroachDBIgniteSingle Store (previously MemSQL)Interbase (which was forked as Firebird)Ingres (which is a predecessor to the awesome PostgreSQL)Google BigTable … Continue reading Using H2 as a Test Database Product with jOOQ
The Performance of Various To-Many Nesting Algorithms
It's been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will … Continue reading The Performance of Various To-Many Nesting Algorithms
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
Vendor Agnostic, Dynamic Procedural Logic with jOOQ
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, … Continue reading Vendor Agnostic, Dynamic Procedural Logic with jOOQ
jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL
This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn't adopt it. And those who did, didn't agree on syntax. But this is about to change. Thanks to the now ubiquitous SQL/JSON support (which jOOQ … Continue reading jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL
Use IN List Padding to Your JDBC Application to Avoid Cursor Cache Contention Problems
A problem few developers are aware of is the possibility of running into "cursor cache contention" or "execution plan cache contention" problems when using IN lists in SQL. The problem that is described in lengths in previous articles, can be summarised as this. All of these are distinct SQL queries and need to be parsed … Continue reading Use IN List Padding to Your JDBC Application to Avoid Cursor Cache Contention Problems
Having “constant” columns in foreign keys
I was asked a very interesting question on Twitter just now: https://twitter.com/connolly_s/status/1303957373107818497 Can we have "constant" foreign key columns in (PostgreSQL) tables? Luckily, yes, we can. Using a nice standard feature that is "computed columns" or "generated columns" Sometimes, you cannot completely normalise your schema for whatever reason. There may be a case where you … Continue reading Having “constant” columns in foreign keys
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)?
Calculate Percentiles to Learn About Data Set Skew in SQL
B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I'll explain later why this is the case, but let's first learn how to detect "skew" What is skew? Skew is a term from statistics when a normal distribution is not symmetric. The example given … Continue reading Calculate Percentiles to Learn About Data Set Skew in SQL