A while ago, I blogged about the importance of avoiding unnecessary COUNT(*) queries:https://blog.jooq.org/sql-tip-of-the-day-be-wary-of-select-count ... and how to replace them with equivalent EXISTS queries As I'm updating the SQL training to show also PostgreSQL performance characteristics in addition to Oracle, I really have to reiterate this topic. Please repeat after me: Thou shalt not use COUNT(*) … Continue reading Avoid Using COUNT() in SQL When You Could Use EXISTS()
Using jOOλ to Combine Several Java 8 Collectors into One
With Java 8 being mainstream now, people start using Streams for everything, even in cases where that's a bit exaggerated (a.k.a. completely nuts, if you were expecting a hyperbole here). For instance, take mykong's article here, showing how to collect a Map's entry set stream into a list of keys and a list of values: … Continue reading Using jOOλ to Combine Several Java 8 Collectors into One
jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month (today, exceptionally on a Wednesday because of technical issues) 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 … Continue reading jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL
All Libraries Should Follow a Zero-Dependency Policy
This hilarious article with a click-bait title caught my attention, recently: https://medium.com/friendship-dot-js/i-peeked-into-my-node-modules-directory-and-you-wont-believe-what-happened-next-b89f63d21558 A hilarious (although not so true or serious) rant about the current state of JavaScript development in the node ecosystem. Dependency hell isn't new Dependency hell is a term that made it into wikipedia. It defines it as such: Dependency hell is a … Continue reading All Libraries Should Follow a Zero-Dependency Policy
Why Most Programmers Get Pagination Wrong
Pagination is one of those things that almost everyone gets wrong for two reasons: User experience Database performance Here's why. What's wrong with pagination? Most applications blindly produce pagination like this: This is how GMail implements pagination. With my current settings, it displays 100 E-Mails at a time and also shows how many E-Mails there … Continue reading Why Most Programmers Get Pagination Wrong
Why I Completely Forgot That Programming Languages Have While Loops
I've recently made an embarassing discovery: https://twitter.com/lukaseder/status/757987329441234944 Yes. In all of my professional work with PL/SQL (and that has been quite a bit, in the banking industry), I have never really used a WHILE loop - at least not that I recall. The idea of a WHILE loop is simple, and it is available in … Continue reading Why I Completely Forgot That Programming Languages Have While Loops
Say NO to Excessive Use of Surrogate Keys if Performance Really Matters to You
We programmers keep cargo culting these wrong ideas. Recently, we said "NO" to Venn diagrams. Today we're going to say no to surrogate keys. The surrogate keys vs. natural keys non-debate is one of the most overheated debates in data architecture, and I don't get why everyone is so emotional. Both sides claim to hold … Continue reading Say NO to Excessive Use of Surrogate Keys if Performance Really Matters to You
The Java JIT Compiler is Darn Good at Optimization
"Challenge accepted" said Tagir Valeev when I recently asked the readers of the jOOQ blog to show if the Java JIT (Just-In-Time compilation) can optimise away a for loop. Tagir is the author of StreamEx, very useful Java 8 Stream extension library that adds additional parallelism features on top of standard streams. He's a speaker … Continue reading The Java JIT Compiler is Darn Good at Optimization
How to Know if a Given Index Can be Dropped
It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove. - Antoine de Saint Exupéry in Terre des Hommes As SQL developers, we keep adding more and more indexes to our tables. Every time we run new queries that are potentially slow, a … Continue reading How to Know if a Given Index Can be Dropped
How Adding a UNIQUE Constraint on a OneToOne Relationship Helps Performance
A lot of people use SQL constraints mainly to enforce data integrity, and that's already a very good thing. A UNIQUE constraint, for instance, makes sure that there is at most one instance of any possible value (or tuple, in the case of a composite constraint) in a table. For instance: CREATE TABLE x ( … Continue reading How Adding a UNIQUE Constraint on a OneToOne Relationship Helps Performance