Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database - as with each write to the table, the index needs to be updated as well in the same transaction. … Continue reading Does Your Database Really Use Your Index?
Tag: performance
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
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
Correlated Subqueries are Evil and Slow. Or are They?
A common myth in SQL is the idea that correlated subqueries are evil and slow. For example, this query here: SELECT first_name, last_name, (SELECT count(*) FROM film_actor fa WHERE fa.actor_id = a.actor_id) FROM actor a It "forces" the database engine to run a nested loop of the form (in pseudo code): for (Actor a : … Continue reading Correlated Subqueries are Evil and Slow. Or are They?
SQL JOIN or EXISTS? Chances Are, You’re Doing it Wrong
A lot of developers get the distinction between JOIN and SEMI-JOIN wrong. Let me explain... What are JOIN and SEMI-JOIN A little bit of relational algebra first. What is an (INNER) JOIN? An JOIN is nothing but a filtered cartesian product. And what is a cartesian product? Wikipedia explains this very nicely: for sets A … Continue reading SQL JOIN or EXISTS? Chances Are, You’re Doing it Wrong
Do Not Think That One Second is Fast for Query Execution
I keep encountering situations where RDBMS users think that one second for query execution is anything near fast. Most recently, in this Stack Overflow question: Hibernate SQL In clause making CPU usage to 100% The poster's original question was why a similar query executes in one second when executed in SQL Server Management Studio whereas … Continue reading Do Not Think That One Second is Fast for Query Execution
Is Your Eclipse Running a Bit Slow? Just Use This Simple Trick!
You wouldn't believe it until you try it yourself. I've been using the Eclipse Mars developer milestones lately, and I've been having some issues with slow compilation. I always thought it was because of the m2e integration, which has never been famous for working perfectly. But then, it dawned upon me when I added a … Continue reading Is Your Eclipse Running a Bit Slow? Just Use This Simple Trick!
Top 10 Easy Performance Optimisations in Java
There has been a lot of hype about the buzzword "web scale", and people are going through lengths of reorganising their application architecture to get their systems to "scale". But what is scaling, and how can we make sure that we can scale? Different aspects of scaling The hype mentioned above is mostly about scaling … Continue reading Top 10 Easy Performance Optimisations in Java
Leaky Abstractions, or How to Bind Oracle DATE Correctly with Hibernate
We've recently published an article about how to bind the Oracle DATE type correctly in SQL / JDBC, and jOOQ. This article got a bit of traction on reddit with an interesting remark by Vlad Mihalcea, who is frequently blogging about Hibernate, JPA, transaction management and connection pooling on his blog. Vlad pointed out that … Continue reading Leaky Abstractions, or How to Bind Oracle DATE Correctly with Hibernate