In my SQL Masterclass, I frequently remind participants of the fact how important statistics are for a modern cost based optimiser. For instance, if you consider the fact that in an average E-Banking system's bookings table, you will probably have a transaction amount histogram like the following: In other words, most of your transactions are … Continue reading Why You Should Design Your Database to Optimise for Statistics
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
When to Choose SQL and When to Choose NoSQL
Some people make architecture decisions purely based on the loudest consultant: https://twitter.com/gareth/status/778575385600688129 For most others, however, decisions are not as simple as this. For instance: When should we start evaluating NoSQL storage systems as an alternative to RDBMS? RDBMS as the hammer for all nails This question obviously assumes that you're starting out with an … Continue reading When to Choose SQL and When to Choose NoSQL
A Hidden jOOQ Gem: Foreach Loop Over ResultQuery
A recent question on Stack Overflow about jOOQ caught my attention. The question essentially asked: Why do both of these loops work? // With fetch() for (MyTableRecord rec : ctx .selectFrom(MY_TABLE) .orderBy(MY_TABLE.COLUMN) .fetch() // fetch() here ) { doThingsWithRecord(rec); } // Without fetch() for (MyTableRecord rec : ctx .selectFrom(MY_TABLE) .orderBy(MY_TABLE.COLUMN) // No fetch() here ) … Continue reading A Hidden jOOQ Gem: Foreach Loop Over ResultQuery
Avoid Using COUNT() in SQL When You Could Use EXISTS()
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()
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
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
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