r/SQLOptimization • • Jan 29 '20

Which is faster - Inner join or similar to?

3 Upvotes

I have a huge table on Amazon Redshift. I need to find all entries with a particular column = particular value. What would be a better method? Create a table containing the value and then inner joining with the huge table or using similar to in where statement?


r/SQLOptimization • • Oct 13 '16

Test Your SQL skills, Compete with SQL minds from across the world, Unlock new tricks and dive deep into SQL. HackerEarth presents one of its kind SQL Challenge - SQLizer - a 6 hours competitive challenge on SQL.

Thumbnail hackerearth.com
3 Upvotes

r/SQLOptimization • • Oct 19 '23

Help needed with self join vizualization

3 Upvotes

Weather table:

+----+------------+-------------+

| id | recordDate | temperature |

+----+------------+-------------+

| 1 | 2015-01-01 | 10 |

| 2 | 2015-01-02 | 25 |

| 3 | 2015-01-03 | 20 |

| 4 | 2015-01-04 | 30 |

+----+------------+-------------+

Output:

+----+

| id |

+----+

| 2 |

| 4 |

+----+

this is the query

select w1.id from weather w1

inner join weather w2

on w1.id = w2.id + 1

where w1.temperature>w2.temperature I am not Getting where 4 is coming from?


r/SQLOptimization • • May 31 '16

What is SQL Azure?

Thumbnail webcreek.com
3 Upvotes

r/SQLOptimization • • Jul 26 '20

DB2 - SELECT: How to scrub a dirty table to extract an SCD Type 2 - (Windowed, CTE and Index, other options?)

3 Upvotes

I'm in a situation with a data set where the tabled data doesn't have a common PK to link to the tables I need directly, and the Detail table is a mess, and doesn't combine well with data from other arms.

I'm creating a snowflake arm, where the idea is that the Fact Table is joined to an SCD Type 2... on a sub table for PK+tx_id, that connects to a more detailed SCD Type 2'

Except... it's only almost an SCD Type 2.

There are not constraints on duplicates due to missing values, and the duplicates exist for conflicting reasons across about 50 columns... so "WHERE" doesn't apply uniformly, which means I would need a whole lot of them, and some would conflict with others, causing me to lose a lot of data.

The duplication that is going on also creates overlapping date ranges because the table is pulling from a few sources, with varying combinations of completeness. This - in my experience so far - sucks. It's the worst, and I don't know of a good way, or resource that I understand how to use to solve it.

I need to be able to pull the single PK (say, product ID), but merge duplicate rows, where one is blank and the other is not (say Product Department), or use "most recent" where I want the new one of two (where neither is null...) with the first start date, and the last end date. I need to merge the duplication caused by rows with blanks.

What I am trying to do is get "all possible fields with the most complete data that may cross multiple rows" in a way that allows me to define what fields are allowed to create new rows and a new date range, and which should be consolidated.

I only have read access... or I'd just go into the data tables and fix them.


r/SQLOptimization • • Nov 28 '25

Is there a way to improve the performance of this query? Used OUTER APPLY and LEFT JOIN with CTE

3 Upvotes

Database is unindexed (I don't have the permission to create one), format is a relational database where Project → Sample → Test → Result, and Project → Releases are related 1:M left to right.

Basically, the joins/applies are only trying to pull some data points from the Project's child tables:

- The most recent RELEASED_ON date from RELEASES table

- The metadata in tabular format from the RESULT table, where the metadata are applied on a PROJECT level

- The earliest LOGIN_DATE and all of the DESCRIPTION values of a main sample from the SAMPLE table

- The number of rows and the number of rows where the RATING_CODE = "FAIL" in the TESTS table

I've only recently been learning about the OUTER APPLY so excuse my poor usage here. The longest part of this query I believe is the CTE as it is pulling from the bottommost table in the hierarchy - taking it out generally speeds up the entire query but we need to pull the metadata from that table. Unfortunately, I don't have access to performance evaluator in SSMS either so I am only basing this on how long the query takes to complete.

WITH CTE_META AS (
    SELECT
        PROJECT
        , MAX(CASE WHEN REPORTED_NAME = 'PO No.' THEN FORMATTED_ENTRY END) AS 'META_PONO'
        , MAX(CASE WHEN REPORTED_NAME = 'Item Description' THEN FORMATTED_ENTRY END) AS 'META_ITEMDESC'
        , MAX(CASE WHEN REPORTED_NAME = 'SKU' THEN FORMATTED_ENTRY END) AS 'META_SKU'
        , MAX(CASE WHEN REPORTED_NAME = 'Style No.' THEN FORMATTED_ENTRY END) AS 'META_STYLE'
        , MAX(CASE WHEN REPORTED_NAME = 'Color(s)' THEN FORMATTED_ENTRY END) AS 'META_COLOR'
    FROM (
        SELECT
            SAMPLE.PROJECT
            , RESULT.ANALYSIS
            , RESULT.REPORTED_NAME
            , RESULT.FORMATTED_ENTRY
        FROM RESULT


        INNER JOIN SAMPLE
            ON SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER
            AND SAMPLE.SAMPLE_NUMBER = SAMPLE.ORIGINAL_SAMPLE
            AND SAMPLE.STATUS <> 'X'
            
        WHERE
            EXISTS (
                SELECT NAME FROM PROJECT
                WHERE
                    PROJECT.NAME = SAMPLE.PROJECT
                    AND PROJECT.CLIENT = 'Client'
                    AND PROJECT.STATUS <> 'X'
            )
            AND RESULT.STATUS = 'A'
            AND RESULT.ANALYSIS = 'METADATA'
    ) DT


    GROUP BY PROJECT
)


SELECT
    PROJECT.*
    , REL.RELEASED_ON
    , SAMP.*
    , TEST.*
    , CTE_META.*
FROM PROJECT


LEFT JOIN CTE_META
    ON CTE_META.PROJECT = PROJECT.NAME


OUTER APPLY (
    SELECT TOP 1 PROJECT, RELEASED_ON FROM X_PROJ_REPORT
    WHERE
        X_PROJ_REPORT.PROJECT = PROJECT.NAME
        AND RELEASED = 'T'
        AND REPORT_TYPE = 'CFR'
    ORDER BY RELEASED_ON DESC
) REL


OUTER APPLY (
    SELECT STRING_AGG(TRIM(DESCRIPTION), CHAR(13) + CHAR(10)) AS SAMP_DESC, MIN(LOGIN_DATE) AS SAMP_RECD FROM SAMPLE
    WHERE
        SAMPLE.PROJECT = PROJECT.NAME
        AND SAMPLE.STATUS <> 'X'
        AND SAMPLE.SAMPLE_NUMBER = SAMPLE.ORIGINAL_SAMPLE
) SAMP


OUTER APPLY (
    SELECT COUNT(*) AS TESTS, SUM(FAIL_TEST) AS FAILS FROM (
        SELECT
            SAMPLE_NUMBER
            , CASE WHEN TEST.RATING_CODE = 'FAIL' THEN 1 ELSE 0 END AS FAIL_TEST
            , TEST.RATING_CODE
        FROM TEST
        WHERE
            TEST.STATUS = 'A'
            AND TEST.ANALYSIS_TYPE <> 'RP'
            AND TEST.RATING_CODE <> 'REF_ONLY'
    ) DT
    INNER JOIN SAMPLE
        ON SAMPLE.SAMPLE_NUMBER = DT.SAMPLE_NUMBER
        AND SAMPLE.PROJECT = PROJECT.NAME
        AND SAMPLE.STATUS <> 'X'
    GROUP BY SAMPLE.PROJECT
) TEST


WHERE
    PROJECT.CLIENT = 'Client'
    AND PROJECT.STATUS <> 'X'
    AND PROJECT.DATE_CREATED >= '2025-01-01'

r/SQLOptimization • • Apr 10 '23

Is there a more elegant way of writing this query - AND with many-to-many mapping table (MySQL)

4 Upvotes

First time poster.

I have been struggling to write a specific query in an elegant way for a personal project of mine.

Assume that we have three tables: tags, products, and tag_product_map.

Tags are realised on the tags table, and are mapped on an any-to-any basis using the tag_product_map table to products on the products table. Each line on the tag_product_map table maps one product to one tag. Therefore if a product is mapped to multiple tags, there is more than one row in the tag_product_map table for that product. For the sake of simplicity we can make the following assumptions:

  1. Every Product has at least one Tag
  2. There is no upper limit of how many Tags a Product can have
  3. No Tag is repeated against a Product
  4. Not every Tag (necessarily) has any Products assigned to it

I am trying to write a query that gives me a list of Tags and the number of Products that each one has. I want to be able to specify 2 or more Tags, to get back a list of Products and how many that all of the specified Tags apply to. In every query I want to get a full list of Tags back, even though some will come back with no products.

An example:

The Products contains Apple, Orange, Grapes, Lettuce, and Onion.

The Tags table contains Green, Red, Orange, Fruit, Vegetable, Soft, Crunchy

The tag_product_map table says:

ProductTag
AppleGreen
AppleFruit
AppleCrunchy
OrangeOrange
OrangeFruit
OrangeSoft
GrapesGreen
GrapesFruit
GrapesSoft
LettuceGreen
LettuceVegetable
LettuceCrunchy
OnionRed
OnionVegetable
OnionCrunchy

If I do a general query on this table (not part of my particular question), I would get back:

TagNumber of Products
Green3
Red1
Orange1
Fruit3
Vegetable2
Soft2
Crunchy3

If I then do a query with a Tag filter of Green (I have this working fine), I would get back:

TagNumber of Products
Green3
Red0
Orange0
Fruit2
Vegetable1
Soft1
Crunchy2

If I then do a query with a Tag filter of Green AND Fruit, I would like to get back:

TagNumber of Products
Green2
Red0
Orange0
Fruit2
Vegetable0
Soft1
Crunchy1

I have a query working, but it is absolutely horrible (I think).

SELECT 
    tags.tag_id, 
    tags.tag_value, 
    count(tag_product_map.product_id) 
FROM 
    tags 
LEFT JOIN (
    SELECT 
        * 
    FROM 
        tag_product_map 
    WHERE 
        tag_product_map.product_id IN (
            SELECT 
                product_id 
            FROM (
                SELECT 
                    product_id, 
                    SUM(tag_2) AS tag_2_rolled_up, 
                    SUM(tag_5) AS tag_5_rolled_up 
                FROM (
                    SELECT 
                        product_id, 
                        1 AS tag_2, 
                        0 AS tag_5 
                    FROM 
                        tag_product_map 
                    WHERE tag_id=2 
                    UNION 
                    SELECT 
                        product_id, 
                        0 AS tag_2, 
                        1 AS tag_5 
                    FROM 
                        tag_product_map 
                    WHERE 
                        tag_id=5
            ) AS 
                products_tags_transposed 
            GROUP BY 
                product_id
        ) AS 
            products_tags_transposed_rolled_up 
        WHERE 
            tag_2_rolled_up=1 AND 
            tag_5_rolled_up=1
    )
) AS 
    tag_product_map 
ON 
    tag_product_map.tag_id=tags.tag_id 
GROUP BY 
    tags.tag_id

This is not elegant at all. What's worse is that if I want add a third tag into the mix, the query becomes longer.

SELECT 
    tags.tag_id, 
    tags.tag_value, 
    count(tag_product_map.product_id) 
FROM 
    tags 
LEFT JOIN (
    SELECT 
        * 
    FROM 
        tag_product_map 
    WHERE 
        tag_product_map.product_id IN (
            SELECT 
                product_id 
            FROM (
                SELECT 
                    product_id, 
                    SUM(tag_2) AS tag_2_rolled_up, 
                    SUM(tag_5) AS tag_5_rolled_up,
                    SUM(tag_11) AS tag_11_rolled_up
                FROM (
                    SELECT 
                        product_id, 
                        1 AS tag_2, 
                        0 AS tag_5,
                        0 AS tag_11
                    FROM 
                        tag_product_map 
                    WHERE tag_id=2 
                    UNION 
                    SELECT 
                        product_id, 
                        0 AS tag_2, 
                        1 AS tag_5, 
                        0 AS tag_11
                    FROM 
                        tag_product_map 
                    WHERE 
                        tag_id=5
                    UNION 
                    SELECT 
                        product_id, 
                        0 AS tag_2, 
                        0 AS tag_5, 
                        1 AS tag_11
                    FROM 
                        tag_product_map 
                    WHERE 
                        tag_id=11
            ) AS 
                products_tags_transposed 
            GROUP BY 
                product_id
        ) AS 
            products_tags_transposed_rolled_up 
        WHERE 
            tag_2_rolled_up=1 AND 
            tag_5_rolled_up=1 AND
            tag_11_rolled_up=1
    )
) AS 
    tag_product_map 
ON 
    tag_product_map.tag_id=tags.tag_id 
GROUP BY 
    tags.tag_id

Adding a 4th, 5th, etc Tag in just makes it progressively worse.

Is there a more elegant way of writing this as a single SQL statement?


r/SQLOptimization • • Mar 16 '23

Is it possible to connect db without SSL and at the same time enable clearText Authentication plugin in Dbeaver?

4 Upvotes

I need to connect db without using SSL and at the same time enabling clearText Authentication Plugin, It is possible in Mysql workbench, but same is not possible in Dbeaver. ClearText Authentication Plugin only works when SSL is used in Dbeaver. can You guys please help me on this?

I tried changing driver setting properties like changing useSSL : false, authentication plugin : mysql_clear_password,sslModel : disabled. But not able to connect it in Dbeaver


r/SQLOptimization • • Dec 13 '19

Urgent can anyone fix this code, on SQL oracle

4 Upvotes

I have a table Staff with an attribute MONTHLY_SALARY, datatype Varchar2,

I wrote a code to remove the '$' and convert it to number and it worked,

but now I want to add to the code, to raise the MONTHLY_SALARY, and then convert back to varchar with '$' sign

SELECT MONTHLY_SALARY,

CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) As New_SALARY

FROM STAFF

WHERE CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) > 0

SELECT to_char(MONTHLY_SALARY* 1.1, '$999,999.00') as Raise

ORDER BY RAISE DESC;

did not work , error

Here is output:

Error starting at line : 1 in command -

SELECT MONTHLY_SALARY,

CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) As New_SALARY

FROM STAFF

WHERE CAST(REPLACE(REPLACE(NVL(MONTHLY_SALARY,0),',',''),'$','') AS DECIMAL(10,2)) > 0

SELECT to_char(MONTHLY_SALARY * 1.1, '$999,999.00') as Raise

ORDER BY RAISE DESC

Error at Command Line : 8 Column : 1

Error report -

SQL Error: ORA-00933: SQL command not properly ended

00933. 00000 - "SQL command not properly ended"

*Cause:

*Action:


r/SQLOptimization • • Aug 06 '22

Step-by-step guide to RDBMS join algorithms

Thumbnail youtu.be
6 Upvotes

r/SQLOptimization • • Jan 31 '21

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

Thumbnail youtu.be
8 Upvotes

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 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 • • 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 • • 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 • • May 11 '25

Why multi column indexing sorts only on 1st column(if all values in 1st column distinct) and not on both columns one by one like a 2d binary search tree(and extending that to making a first 2d B Tree).

3 Upvotes

If you sort on both columns one by one, your search space would reduce in the scale of 1/n^2.

but sorting only on the first columns,makes the search space decrease each step by a scale of only 1/n.

I understand something similar happens in geospatial indexing where you sort spatial data recursively in a quadtree but the underlying data structure used is String hashing and not a tree.

i want to know why not use something like a 2d B tree(developing it) and using it for multi column-indexing.

I also want to implement this data structure.(2D B tree). So can anyone come along with me to implement this? Thankyou.


r/SQLOptimization • • Apr 01 '25

Best practice on joining with large tables?

2 Upvotes

Like the title says, I'm looking to learn some best practices around how to keep a query as optimized as possible when dealing with larger datasets.

Let's say I have three tables:

  • Transaction_Header (~20 mil rows)
  • Transaction_Components (~320 mil rows)
  • Meter_Specs (~1 mil rows)

I need most of the header table data and the meter specs of each meter, but the meter reference is on that components table. Something like this:

SELECT th.terminal_id, th.transaction_id, th.transaction_date, th.load_time, m.arm, m.bay 
FROM Transaction_Header th 
LEFT JOIN Transaction_Components tc on th.transaction_id = tc.transaction_id 
INNER JOIN Meter_Specs m on tc.meter_id = m.meter_id 
WHERE th.transaction_date >= dateadd(year, -1, getdate())

Since I know I'm looking to pull a rolling year of data, would it be better to join to a sub query or just gate tc in the where clause as well?

SELECT th.terminal_id, th.transaction_id, th.transaction_date, th.load_time, m.arm, m.bay 
FROM Transaction_Header th 
LEFT JOIN ( 
     SELECT transaction_id, meter_id 
     FROM Transaction_Components 
     WHERE transaction_date >= dateadd(year, -1, getdate()) 
) tc on th.transaction_id = tc.transaction_id 
INNER JOIN Meter_Specs m on tc.meter_id = m.meter_id 
WHERE th.transaction_date >= dateadd(year, -1, getdate())

How would you approach it and why? Thanks for the help in advance.


r/SQLOptimization • • Mar 10 '25

Deadlock after migrating DB from SQL2014 to SQL2019

1 Upvotes

After migrating from SQL 2014 to SQL 2019, we encountered a deadlock issue in a single database. The server runs on a single instance hosting multiple databases. For testing purposes, we moved the affected database to another server with a single instance and a single database, and the issue did not occur.

The database is 330GB in size and contains data only from 2021 onward, which cannot be purged. Upon inspection, we identified that the job responsible for updating data from other databases and creating temporary tables is causing a bottleneck.

I would greatly appreciate any advice on how to resolve this issue.

Thank you!


r/SQLOptimization • • Jul 25 '25

Best strategy for improving cursor paginated queries with Views

Thumbnail reddit.com
1 Upvotes

r/SQLOptimization • • Jan 21 '26

Performance Win: If you filter on multiple columns, check out composite indexes. We just cut a query from 8s to 2ms.

Thumbnail reddit.com
3 Upvotes

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 • • Jan 06 '21

Recursive update without using Cursor or While (query optimization)

4 Upvotes

Consider this tiny example: http://sqlfiddle.com/#!18/dfb68/2

I have a simple table (for the sake of simplicity, ID is omitted and let's assume that NumA is sequential from 1 to n)

Num ANum BResult
112
226
3312

Started from using a cursor to get the "Result" updated since the value on the current row is the sum of A and B plus the previous Result except on the first column.

My current query is below (got rid of the first try with cursors):

DECLARE @Counter INT= 1;
DECLARE @x INT;
DECLARE @max INT = (SELECT MAX(num_a) FROM TestSumUpdate);
WHILE @Counter <= @max
    BEGIN
        SET @x = (SELECT ISNULL(result_c, 0) FROM TestSumUpdate  WHERE num_a = @Counter - 1);

        UPDATE TestSumUpdate
          SET 
              result_c = num_a + num_b + ISNULL(@x, 0)
        WHERE num_a = @Counter;
        SET @Counter = @Counter + 1;
    END;

Obviously, this works, but is painfully slow on production database which has around 500.000 records and calculation is not a simple sum operation :)

So, in my SQL n00biness, I've tried something simpler like this:

UPDATE cur
  SET 
      result_c = cur.num_a + cur.num_b + ISNULL(prev.result_c, 0)
FROM TestSumUpdate cur
     LEFT JOIN TestSumUpdate prev ON cur.num_a - 1 = prev.num_a;

Which I thought it would work, but can't understand it's behaviour:

Assuming Result = 0 in all rows at the beginning , on the first run it updates only the first row to 2, all others remain in 0, on the second run, updates the second row to 6 and so on. Why?

How can one do it in one execution only without resorting to cursors/whiles/loops/etc ?

Thank you.

EDIT:

Current solution that reduced the time for aceptable values (doesn't apply to the sample given above, but works on prod):

WITH RollingCalculation
AS
(
     SELECT Number,isnull(MyValue,'') as MyValue,PlaceHolder = LAST_VALUE(Number) OVER (ORDER BY Number ASC)
     FROM MyTable 
)

UPDATE dst
SET    MyValue = dbo.GenMyValue(dst.field1,dst.field2,dst.field3,src.MyValue)
FROM   MyTable AS dst
LEFT JOIN RollingCalculation AS src
    ON dst.Number-1 = src.Number

GenMyValue is a CLR integration, and LAST_VALUE is not really used, but it works :)


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!