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
Tag: performance
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
How to Avoid Excessive Sorts in Window Functions
Usually, this blog is 100% pro window functions and advocates using them at any occasion. But like any tool, window functions come at a price and we must carefully evaluate if that's a price we're willing to pay. That price can be a sort operation. And as we all know, sort operations are expensive. They … Continue reading How to Avoid Excessive Sorts in Window Functions
Squeezing Another 10% Speed Increase out of jOOQ using JMC and JMH
In this post, we're going to discuss a couple of recent efforts to squeeze roughly 10% in terms of speed out of jOOQ by iterating on hotspots that were detected using JMC (Java Mission Control) and then validated using JMH (Java Microbenchmark Harness). This post shows how to apply micro optimisations to algorithms where the … Continue reading Squeezing Another 10% Speed Increase out of jOOQ using JMC and JMH