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
Tag: performance
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
A Basic Programming Pattern: Filter First, Map Later
In recent days, I've seen a bit too much of this: someCollection .stream() .map(e -> someFunction(e)) .collect(Collectors.toList()) .subList(0, 2); Something is very wrong with the above example. Can you see it? No? Let me rename those variables for you. hugeCollection .stream() .map(e -> superExpensiveMapping(e)) .collect(Collectors.toList()) .subList(0, 2); Better now? Exactly. The above algorithm is O(N) … Continue reading A Basic Programming Pattern: Filter First, Map Later
How to Find Redundant Indexes in SQL
The following two indexes are redundant in most SQL databases: CREATE INDEX i_actor_1 ON actor (last_name); CREATE INDEX i_actor_2 ON actor (last_name, first_name); It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is … Continue reading How to Find Redundant Indexes in SQL
Don’t Use the String Concatenation “Trick” in SQL Predicates
In SQL, quite often, we want to compare several values with each other. For instance, when we're looking for a specific user by their first and last names, we'll write a query like this one: SELECT * FROM customer WHERE first_name = 'SUSAN' AND last_name = 'WILSON'; We're getting: CUSTOMER_ID FIRST_NAME LAST_NAME ------------------------------------ 8 SUSAN … Continue reading Don’t Use the String Concatenation “Trick” in SQL Predicates
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 thus … Continue reading Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys
Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work”
Probably the most impactful thing you could learn about when writing efficient SQL is indexing. A very close runner-up, however, is the fact that a lot of SQL clients demand tons of "unnecessary, mandatory work" from the database. Repeat this after me: Unnecessary, Mandatory Work What is "unnecessary, mandatory work"? It's two things (duh): Unnecessary … Continue reading Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work”
jOOQ Tuesdays: Brett Wooldridge Shows What it Takes to Write the Fastest Java Connection Pool
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. I'm very excited to … Continue reading jOOQ Tuesdays: Brett Wooldridge Shows What it Takes to Write the Fastest Java Connection Pool
Why You Should (Sometimes) Avoid Expressions in SQL Predicates
I've recently discovered a rather significant performance issue on a productive Oracle 11g customer database. And I'm sure you have this issue too, which is why I'm documenting it here. This is a simplified representation of the setup at the customer site: ID PAYMENT_DATE TEXT ---------- ------------ ----------------------------------- 33803 21.05.16 DcTNBOrkQIgMtbietUWOsSFNMIqGLlDw... 29505 09.03.16 VIuPaOAQqzCMlFBYPQtvqUSbWYPDndJD... 10738 … Continue reading Why You Should (Sometimes) Avoid Expressions in SQL Predicates
Don’t Even use COUNT(*) For Primary Key Existence Checks
In a recent blog post, I've advocated against the use of COUNT(*) in SQL, when a simple EXISTS() would suffice. This is important stuff. I keep tuning productive queries where a customer runs a COUNT(*) query like so: SELECT count(*) INTO v_any_wahlbergs FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = 'WAHLBERG' ... … Continue reading Don’t Even use COUNT(*) For Primary Key Existence Checks