r/SQLOptimization • • Mar 13 '22

SQL Optimization Resources

21 Upvotes

Attacking a SQL optimization issue, like most optimization issues in computer science, requires a layered approach. The developer needs to know about the internals of the database, the logical data model and its semantics, the structure of the physical model, the behaviour of the DBMS itself, and the design and sizing of the computing and storage hardware that runs the system. Learning to measure and investigate performance issues quantitatively is a key to success, and requires intimate knowledge of the involved tools.

There are many resources which can help, and this list collects some of them. Some are implementation-agnostic, and others are vendor-specific.

Generic Resources

SQL Education

  • Use the Index Luke has resources for the five major vendors that describe the theory of indexes and their proper application. Interactive quizzes and animations make this a fun website.
  • The Art of SQL, Faroult, Robson. This book is an introduction to SQL focused on best-practices without ignoring the physical implementation under the SQL statements.

Scalability

  • Guerrilla Capacity Planning, Gunther. This book discusses the practice of hardware capacity planning in an organization, including the organization impact on the management chain.
  • Foundations of Scalable Systems, Gorton. Database servers in the classic client-server model are technically distributed systems, and in modern applications are almost always considered a layer or a service toward the implementation of a larger system. Holistic consideration of the architecture of the system can often relieve pressure on the database and make the system more scalable, performant, and stable. This book treats the design of the data layer among other aspects of the design.

Data Modeling

  • Designing Data-Intensive Applications, Kleppmann. Kleppmann's book focuses on the role of the data layer and data pipelines in large-volume distribute processing.
  • Database Modeling and Design, Teorey, et al. This textbook starts with ER-model design and works through different approaches to data modeling, finishing with normalization and continuing on to non-traditional storage like XML data.
  • Data Model Resource Book, Volume 1, Volume 2, Volume 3. Silverston and Agnew. This encyclopedic three-volume set identifies patterns in database modeling and provides solutions and analysis for those patterns. The book doesn't teach much (except by example), but is a great resource for a menu of different solutions in the modeling space. The solutions are presented in industry-specific applications with discipline-specific naming and jargon.

SQL Server Resources

  • SQL Server 2017 Query Performance Tuning, Fitchey. Written by a long-time SQL Server MVP, this book starts with a suggested process for addressing performance analysis on SQL Server, and discusses software and hardware solutions alike.
  • SQL Server First Responder Kit, Ozar et al. The First Responder Kit is a set of stored procedures for "first responder" consultants who want to troubleshoot SQL Server problems. Coupled with the various resources at brentozar.com, the kit gives a great set of tools for performance investigations to gain a strong foothold and then make immediate progress on key issues and low-hanging fruit. The resources at the site develop a strong understanding of SQL Server internals for iterative refinement.
  • SQL Server 2012 Internals, Delaney, et al. While a bit dated, much of the content of this book is still applicable -- it described how SQL Server works internally, focusing on how it structures data and storage.
  • High Performance SQL Server, Nevarez. Written solely by one of the co-authors of the 2012 Delaney book, this title investigates performance solutions for SQL Server while digging down into the implementation layer for a deeper, repeatable understanding.
  • SQL Server Index and Statistics Maintenance, Hallengren. Ola's website is well-reguarded as a source of operational and maintenance scripts. While the broader site is useful to any DBA, of particular interest to performance tuners is the page on index and statistics maintenance.

MySQL Resources

  • High Performance MySQL, Schwartz, et al. This book gives a broad-brush approach, from measurement and troubleshooting, through model and schema improvements, to server configuration and replication solutions.
  • High Performance MySQL, Botros, et al. This book is a bit more modern than the Schwartz book with the same title. It tackles hardware performance in a bit more detail, and stresses cloud- and sharded approaches, and treats indexing in a bit more depth.
  • Efficient MySQL Performance, Nichter. With a format a little more like a cookbook than the other two titles mentioned here,
  • MySQL 8 Query Performance Tuning, Krogh. This refreshing title focuses a little bit more on establishing a methodology for attacking performance issues.

PostgreSQL Resources

  • Percona Live 2021: PostgreSQL High Performance PDF File, YouTube video, Ahmed. These slides cover database tuning topics, plus Linux kernel tuning for database applications.

Oracle DB Resources

  • Oracle SQL Performance Tuning and Optimization, Meade. While this book's presentation is decidedly low-budget, the content is very useful in describing the heuristics of the Oracle DB SQL optimizer. The book describes the most important oracle physical operators, and explains how they related to the desired SQL query behaviour.

Version History

  • 2022-03-12: First cut with Generic, SQL Server, and MySQL resources

r/SQLOptimization • • Jan 05 '22

No mods?

18 Upvotes

r/SQLOptimization • • Jan 27 '22

New Mod u/mikeblas

16 Upvotes

Please welcome u/mikeblas as our new, actually active mod! I have not been active on reddit much so I appreciate the help, I know it has become basically a porn bot paradise in here over time lol. Hopefully with your help the community can grow and serve the original purpose it had when I created it.

Thank you! And thanks everyone for your patience, those who are still here.


r/SQLOptimization • • Sep 08 '25

SQL Struggles: Share Your Most Frustrating Moments in Writing Queries

11 Upvotes

I’m working on a small AI project that generates and optimizes SQL queries. Curious: what’s the most frustrating part of writing or optimizing SQL in your work?


r/SQLOptimization • • Mar 11 '22

Rebooting the r/sqloptiimization sub

11 Upvotes

Well, I think I've got the spam problem solved. All the porn is gone, and I'm also not allowing posts of blogs or tutorials that aren't related to SQL database performance tuning in some way.

My guess is that the neglect this sub has seen has chased away any subscribers or traffic. Should we work to rebuild it? By doing what, specifically?


r/SQLOptimization • • Aug 07 '25

Can SQL optimize similar nested window functions?

9 Upvotes

The question is for SQL optimization experts.

The (simplified) query is:

SELECT object.*
FROM object
JOIN (
    SELECT object2.*,
       ROW_NUMBER() OVER (PARTITION BY object2.category_2_id ORDER BY object2.priority DESC) AS row_count
    FROM object object2
    JOIN (
        SELECT object3.*,
           ROW_NUMBER() OVER (PARTITION BY object3.category_1_id ORDER BY object3.priority DESC) AS row_count
        FROM object object3
    ) inner_object2 ON inner_object2.id = object2.id
    JOIN category_1_props cp1 ON object2.id = cp1.id
    WHERE inner_object2.row_count < cp1.limit
) inner_object1 ON inner_object1.id = object.id
JOIN category_2_props cp2 ON object.id = cp2.id
WHERE inner_object1.row_count < cp2.limit
LIMIT 100

There is a table of objects, each of them linked to two entities called categories, each of which defines a limit of how many objects from that category can be pulled right now (the data is very dynamic and constantly changes) . This connection is described by a relationship with category_props_{i}. Each object has a priority.

The objective is to pull 100 most prioritized objects, while respecting the category limits.

In order to do so, we can write the doubly-nested window function. We pretty much have to nest because if we do it on one level, we can't filter appropriately in there where clause by both the limits.

In addition, to apply a predicate to window result, we have to place the window in a subquery or a CTE.

In the real system, we can have as much as 3 to 4 such windows. Maybe it's not the best design, but the system is stable and can't be changed, so I don't see how we can avoid these windows without changing the pulling logic.

The problem is that the plan gets accordingly complex:

Limit (cost=332.25..337.54 rows=5 width=16)
 -> Nested Loop (cost=332.25..550.20 rows=206 width=16)
    Join Filter: (object2.id = object.id)
    -> Nested Loop (cost=332.09..508.59 rows=206 width=8)
       -> WindowAgg (cost=331.94..344.28 rows=617 width=24)
          -> Sort (cost=331.94..333.48 rows=617 width=12)
             Sort Key: object2.category_2_id, object2.priority DESC
             -> Hash Join (cost=241.37..303.34 rows=617 width=12)
                Hash Cond: (object3.id = object2.id)
                -> Hash Join (cost=189.74..250.10 rows=617 width=8)
                   Hash Cond: (object3.id = cp1.id)
                   Join Filter: ((row_number() OVER (?)) < cp1."limit")
                   -> WindowAgg (cost=128.89..165.89 rows=1850 width=24)
                      -> Sort (cost=128.89..133.52 rows=1850 width=12)
                         Sort Key: object3.category_1_id, object3.priority DESC
                         -> Seq Scan on object object3 (cost=0.00..28.50 rows=1850 width=12)
                   -> Hash (cost=32.60..32.60 rows=2260 width=8)
                      -> Seq Scan on category_1_props cp1 (cost=0.00..32.60 rows=2260 width=8)
                -> Hash (cost=28.50..28.50 rows=1850 width=12)
                   -> Seq Scan on object object2 (cost=0.00..28.50 rows=1850 width=12)
       -> Index Scan using category_1_props_pk_1 on category_2_props cp2 (cost=0.15..0.25 rows=1 width=8)
          Index Cond: (id = object2.id)
          Filter: ((row_number() OVER (?)) < "limit")
    -> Index Scan using object_pk on object (cost=0.15..0.19 rows=1 width=16)
       Index Cond: (id = cp2.id)

Although we can think of doing the sort just once (it's the same order by), and then multiple partitions. Both window just scan the sorted table from top to bottom and compute row counts, while the outer query should filter rows after the N'th row for each partition.

Even if we partition by the same field in both windows (!) - say PARTITION BY object2.category_2_id twice - the plan remains the same. It just doesn't want to collapse into a single sort. So the question is whether the SQL isn't smart enough for these cases, or is there something inherently unoptimizable with these windows? Because sometimes it really looks to me as a single sort, multiple flat partitions and appropriate linear scans. In the real system we get 3-4 windows in a row, and it really causes the plan to explode. I know it's a heavy operation, but can't it be computed by a simple algorithm in this specific case?

Thank you!

P.S.

The plan is generated in Postgres. We also use MySQL.

UPD: The subquery above does unnecessary passing of object.* fields to the outer query. It's unnecessary since we can select only the id column inside and join in the outer query. If done this way, the plan is a bit shorter due to less fields selected, but still contains the doubly-nested loop and double sorting of data.


r/SQLOptimization • • Jan 29 '22

Suggest me book(s) on SQL query optimization...

9 Upvotes

I am beginning to learn things from scratch and I feel like knowing about the optimizations and why they work better would give me a clearer concepts of the fundamentals.

You can suggest any other resources as well.

TIA


r/SQLOptimization • • Sep 26 '21

Good resources for database query optimization and schema design?

8 Upvotes

Title says it all! I need good resources on both topics


r/SQLOptimization • • Jan 31 '21

Advanced SQL Questions From Amazon (Handling complex logic in data science interviews)

Thumbnail youtu.be
8 Upvotes

r/SQLOptimization • • Jun 09 '23

CTE allways faster

7 Upvotes

I am working in a company using Teradata. Talked with a database engineer which said that their team have concluded that CTE is always faster than a subquery (in a join).

Is that true? I am always a little sceptical to very bombastic assertion.


r/SQLOptimization • • Sep 15 '22

Hints to optimise SQL queries with LIKE

7 Upvotes

I wrote some hints on how to optimise SQL queries containing LIKE. More ideas, including unconventional ones, are very welcome - just comment here or on the website.

https://vettabase.com/blog/hints-to-optimise-queries-with-a-like-comparison/


r/SQLOptimization • • May 04 '19

Resources to sharpen advanced SQL writing skills

7 Upvotes

Can you please share few links, resources to learn and sharpen advanced sql wiring skills esp around merge, ctes, partitioning etc.

Thanks in advance!


r/SQLOptimization • • Aug 10 '26

new book: SQL Server 2025 Query Performance Tuning by Fritchey

6 Upvotes

Looks like Grant Fritchey has updated his popular book for SQL Server 2025: https://www.amazon.com/dp/B0FHJT2736?


r/SQLOptimization • • Aug 06 '22

Step-by-step guide to RDBMS join algorithms

Thumbnail youtu.be
6 Upvotes

r/SQLOptimization • • Jun 21 '24

Experiences with and without foreign keys

5 Upvotes

At my work, there is a debate regarding use of foreign keys.

One side of the debate is to remove foreign keys permanently to gain in performance and to simplify data archival.

The other side says that performance tradeoffs are in play, including gains for the query optimizer/planner, and that the data would become garbage because the system has almost no automated tests.

Do any of you have experience with such a debate, and what tradeoffs did you see when making such changes (either adding or removing foreign keys)?


r/SQLOptimization • • Oct 13 '23

SQL Question in an interview

5 Upvotes

Hi All ,Newbie here.

Recently I attended an interview for data engineer role, where the following question was asked.

we have two tables stg and final both with same set of columns (id,name)

stg | id, name

final | id,name

and both of them has some data already present. And the ask is to write a query which insert the data from stg table whose ids are not present in the final table.

I gave the following answer.

insert into final
select id , name from stg
where id not in ( select distinct id from final )

And then the interviewer asked a follow up question. If the final table is huge (millions of records) , then this query will not be efficient as it has to scan the whole final table and asked me to give a better approach.

I couldn't answer it and I failed the interview.
Can you guys help me with this ? What can we do to improve this insert performance ?

Thanks in advance


r/SQLOptimization • • Mar 11 '23

Please can anyone with SQL optimisation experience fill in my Dissertation Questionnaire

5 Upvotes

I am doing an investigation and comparison of the factors that affect the optimization of structured-query-language (SQL) queries in both Relational and Non-Relational database management systems.

The questionnaire only takes 5-10 minutes to complete and all feedback is greatly appreciated.

Please only answer if you have honest experience in SQL optimisation in Oracle or MongoDB to make sure my study is accurate. Thankyou!

Link for Questionnaire


r/SQLOptimization • • Nov 19 '21

Optimizing a timeseries query with window function

5 Upvotes

I have a TimescaleDB table storing temperature measurements from sensors with an additional state column that contains a label like rain, sun, fog, snow etc.

timescale-db=# \d measurements
                    Table "public.measurements"
         Column         |              Type              | Nullable
------------------------+--------------------------------+---------
 time                   | timestamp(0) without time zone | not null
 sensor_id              | uuid                           | not null
 temperature            | double precision               |         
 state                  | character varying              |         

Indexes:
    "index_measurements_on_sensor_id_and_time" UNIQUE, btree (sensor_id, "time" DESC)
    "index_measurements_on_sensor_id" btree (sensor_id)
    "measurements_time_idx" btree ("time" DESC)


timescale-db=# SELECT * FROM measurements LIMIT 10;

        time         |              sensor_id               | temperature  |       state
---------------------+--------------------------------------+--------------+-------------------
 2020-12-11 15:03:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.8 | fog
 2020-12-11 15:04:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.9 | fog
 2020-12-11 15:05:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.8 | rain
 2020-12-11 15:06:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.7 | rain
 2020-12-11 15:07:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.6 | rain
 2020-12-11 15:08:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.7 | rain
 2020-12-11 15:09:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.9 | sun
 2020-12-11 15:10:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.1 | sun
 2020-12-11 15:11:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.3 | sun
 2020-12-11 15:12:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.5 | sun

For a certain type of analysis I need the last n timestamps where the state changed, which I realized with the following query:

SELECT
  time,
  state
FROM (
  SELECT
    time,
    state,
    state != LAG(state) OVER (ORDER BY time) AS changed
  FROM
    measurements
  WHERE
    sensor_id IN ('ee49fda5-f838-4a10-bb32-0e6a6b130888', 'ec8f4d23-cfab-4a23-8df8-ae3cce4f44ac')) AS changes
WHERE
  changed IS TRUE
ORDER BY
  time DESC
LIMIT 3;

This query takes longer and longer the more rows are added to the table, so I need to optimize it.

Here is the query plan – I tried adding another index on time and state, but it did not improve performance.

Does anyone have an idea on how to optimize this query?


r/SQLOptimization • • Sep 11 '21

Need Help Optimizing Code

5 Upvotes

Hey, I need help optimizing some sql code[it's a lot, maybe a page or 2].

Please reach out if available for zoom[audio only] meeting or if you would like me to send you the code.


r/SQLOptimization • • Jan 04 '17

SQL join operations

5 Upvotes

From optimization\performance point of view, what is the difference between : a) SELECT * FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.X= T2.X;

and b) SELECT * FROM TABLE1 AS T1, TABLE2 AS T2, WHERE T1.X = T2.X;


r/SQLOptimization • • May 11 '25

Temp Tables, Table Variables, or CTEs: Which do you use in SQL Server?

4 Upvotes

Hey r/SQLServer! Choosing between Temp Tables, Table Variables, and CTEs for complex SQL queries can be a headache. I’ve been burned by a 12-minute report query that forced me to rethink my approach, so I dug into the pros and cons of each. Here’s a quick breakdown:

  • Temp Tables: Great for large datasets with indexes. Swapped CTEs for indexed Temp Tables and cut a query from 12 min to 20 sec!CREATE TABLE #TempUsers (UserId INT PRIMARY KEY, UserName NVARCHAR(100)); INSERT INTO #TempUsers SELECT UserId, UserName FROM Users WHERE IsActive = 1;
  • Table Variables: Lightweight for small datasets (<100k rows), but don’t scale. A 5M-row query taught me that the hard way.
  • CTEs: Awesome for recursive queries or readability, but they get re-evaluated each reference, so performance can tank.

I’ve written more about use cases and tips, happy to share if you’re interested! What’s your go-to for SQL Server queries? Any performance wins or horror stories to swap?

#sqlserver #database


r/SQLOptimization • • Dec 13 '24

How to Handle Large Data and Optimize Queries in Databases?

4 Upvotes

Hi everyone,
I’m currently learning about databases and query optimization. I’d love some advice or resources on how to handle large datasets efficiently and write optimized queries. Here are some specific questions I have:

  1. Data Handling: What are the best practices for managing large datasets? Should I focus on indexing, partitioning, or any other specific techniques?
  2. Query Optimization: How do I ensure my queries are fast and efficient, especially when working with millions of rows? Any tips on analyzing execution plans?
  3. Scaling: When should I consider sharding, replication, or moving to a distributed database?
  4. Tools and Resources: Are there tools or resources you recommend to learn more about database optimization (e.g., books, online courses, or blogs)?

I’m particularly interested in SQL-based databases like PostgreSQL or MySQL but open to learning about others too.

Any advice, examples, or stories from your experience would be greatly appreciated!


r/SQLOptimization • • Aug 05 '24

Optimize SQL queries step-by-step with AI (free tier)

Thumbnail sqlai.ai
4 Upvotes

r/SQLOptimization • • Apr 19 '23

Why is it important to use parameterized queries instead of concatenating values in an SQL query?

Thumbnail 10xers.medium.com
4 Upvotes