SQL Performance Tips

Tips to get best SQL performance.
Covering all major SQL databases.
 is my book.

[SQL Performance Tips @SQLPerfTips]

SQL Performance Explained — A Book For Developers
It focuses on what developers need to know.
Just 200 pages…yet covering all major databases.

https://t.co/fkbYE74LQt

[Markus Winand @MarkusWinand]

€10 for a frontier AI subscription are gone in one workday

€10 for my book SQL Performance Explained has a lasting impact on you*

https://t.co/uCueteZdDz

Learning timeless fundamentals always has a lasting effect

* you also need to read it, which takes about 1 day ;)

[Markus Winand @MarkusWinand]

New post: Structured Primary Keys

For consistency and performance.

https://t.co/0ZoqVaV6sl

[SQL Performance Tips @SQLPerfTips]

14 years online, still relevant and current.

Those who did the quiz 14 years ago can still benefit for their lessons at that time.

https://t.co/bgYh8tKg8K

[SQL Performance Tips @SQLPerfTips]

A B-tree is not a binary tree.

A B-tree does not go out of balance. A B-tree is self-balancing: it does *not* require expensive operations to keep the balance.

[SQL Performance Tips @SQLPerfTips]

Completley free web-book about SQL indexing:

https://t.co/AhN07mt3Lt

Covering many systems such as MySQL, PostgreSQL, Oracle, ...

[SQL Performance Tips @SQLPerfTips]

Short-Circuiting Correlated Subqueries in SQLite

https://t.co/ZJXbPtnNhB

[SQL Performance Tips @SQLPerfTips]

Another look into PostgreSQL CTE materialization and non-idempotent subqueries

https://t.co/BprzbiV7bU

[SQL Performance Tips @SQLPerfTips]

For this WHERE, what index to create?

WHERE subsidiary_id = 42 AND last_name LIKE '%INA%'

Is (subsidiary_id) enough or should it be (subsidiary_id, last_name)?

https://t.co/Ax09WlDtOB

[Markus Winand @MarkusWinand]

My favorite change is that MariaDB got an improved "WHERE year(...) = ?" rewriting. https://t.co/9liw4kyyp4

In picture: the green check mark used to be yellow, with the "a" footnote: Unconditionally rewritten—even if only a matching function-based index exists

[Markus Winand @MarkusWinand]

Survey on performance optimization for database systems (2023)

https://t.co/hZu8wWoEJy

[Shreyansh Gupta @_backslashzero]

#DoYouKnow? If your index is getting too big in size, you can index only a subset of the values using Partial Indexes.

Read more here - https://t.co/6aOT5T1Lck

#Databases #SQL

[SQL Performance Tips @SQLPerfTips]

Improved RCSI Ghost Cleanup in SQL Server 2022
— by Paul White

As always, Paul provides deep insight into the SQL Server implementation.

https://t.co/Nc0dfTOnXW

[SQL Performance Tips @SQLPerfTips]

Farm to TABLE: Local(e) Providers
https://t.co/0HGYKYWrYC

The Doom That Came To PostgreSQL: When Collations Change
https://t.co/GmGHx3zwES

[SQL Performance Tips @SQLPerfTips]

Collations are a wonderful feature.

They solve problems way beyond case-insensitive search: ignoring accents and "numeric" sorting ('10' > '2') are just two more examples.

However, they do have a performance impact as shown by this micro-benchmark:
https://t.co/UOcEoQA38H

[SQL Performance Tips @SQLPerfTips]

If you are not sure what collations are about, please read the entire series by Christophe Pettus:

“Gentlemen, this is a 🏈”: Glyphs, Encodings, Collations, and Locales
https://t.co/JDJ2Vm0hUs

Speaking in Tongues: PostgreSQL and Character Encodings
https://t.co/aLHuWPPM68

[Markus Winand @MarkusWinand]

At about this time of the day, I run my open-to-everyone trainings.

The last training this year is "SQL Performance Explained", for which I have one spot left. It starts Dec 2nd and is delivered in seven 2-hour long in-person sessions each Mon/Wed/Fri.

https://t.co/86X2Fp1igu

[SQL Performance Tips @SQLPerfTips]

@softilium Well, then read the article to see that it does make a big difference. https://t.co/Ax09WlCVZ3

[SQL Performance Tips @SQLPerfTips]

For this WHERE, what index to create?

WHERE subsidiary_id = 42 AND last_name LIKE '%INA%'

Is (subsidiary_id) enough or should it be (subsidiary_id, last_name)?

https://t.co/Ax09WlDtOB

[SQL Performance Tips @SQLPerfTips]

@davidrowley_pg Check again :

[SQL Performance Tips @SQLPerfTips]

Finding for the most recent, best matching, highest ranking rows (“Top-N”) is a common problem.

The window functions ROW_NUMBER, RANK and DENSE_RANK can be used for such queries.

Not all systems optimize these queries well—PostgreSQL 15 just learned it.

https://t.co/TX0HBY1iHh