Query optimization with join condition

Microsoft SQL Server (MS SQL Server) is a relational database management system (RDBMS) developed by Microsoft. Transact-SQL (T-SQL) is the specific dialect of the SQL (Structured Query Language) that you use to communicate with a Microsoft SQL Server database.

The query to be optimized

Here there is a query. The table has no index. How can we optimize the query and the table reading performance setting up indices? select k.*, 

select k.*, 
       c.[Top_segment]
      ,c.[CompanyType]
      ,c.[Potencial] from (
    select 
          a.[Dt_ym]
          ,a.[CustomerIdEx]
          ,sum(a.ErpRegisterDoc_count) ErpRegisterDoc_count
          ,sum(a.ErpValueNet_sum) ErpValueNet_sum, sum(b.ErpValueNet_sum) ErpValueNet_sum_py
    FROM [onn].[DBCust_stat_baskets] a with (nolock)
      left join [onn].[DBCust_stat_baskets] b with (nolock) 
          on dateadd(year, 1, b.dt_ym) = a.Dt_ym and b.[CustomerIdEx] = a.CustomerIdEx
group by           a.[Dt_ym]
          ,a.[CustomerIdEx]
 ) as k 
  left join [onn].[DBCust_erp] c with (nolock) on k.CustomerIdEx = c.Cust_nr

select k.*, 
       c.[Top_segment]
      ,c.[CompanyType]
      ,c.[Potencial] from (
    select 
          a.[Dt_ym]
          ,a.[CustomerIdEx]
          ,sum(a.ErpRegisterDoc_count) ErpRegisterDoc_count
          ,sum(a.ErpValueNet_sum) ErpValueNet_sum, sum(b.ErpValueNet_sum) ErpValueNet_sum_py
    FROM [onn].[DBCust_stat_baskets] a with (nolock)
      left join [onn].[DBCust_stat_baskets] b with (nolock) 
          on dateadd(year, 1, b.dt_ym) = a.Dt_ym and b.[CustomerIdEx] = a.CustomerIdEx
group by           a.[Dt_ym]
          ,a.[CustomerIdEx]
 ) as k 
  left join [onn].[DBCust_erp] c with (nolock) on k.CustomerIdEx = c.Cust_nr

The biggest performance issues here are the complete lack of indexes and a join condition that prevents the use of an index (even if one existed). You can dramatically speed this up by creating the right indexes and making a small but critical change to the query.

Setting Up the Right Indices

An index acts like a book’s index, allowing SQL Server to find data quickly without scanning the entire table. Based on your query’s JOIN and GROUP BY clauses, these are the indexes you need.

On the DBCust_stat_baskets table:

This table is joined to itself and grouped by CustomerIdEx and Dt_ym. A covering index will be most effective here. This special type of index not only includes the key columns for searching but also the data columns the query needs, allowing the entire subquery to be satisfied from the index alone.

SQL

CREATE NONCLUSTERED INDEX IX_DBCust_stat_baskets_Customer_Date
ON [onn].[DBCust_stat_baskets] ([CustomerIdEx], [Dt_ym])
INCLUDE ([ErpRegisterDoc_count], [ErpValueNet_sum]);

• ON ([CustomerIdEx], [Dt_ym]): This covers your JOIN and GROUP BY columns. The order is important; CustomerIdEx is more selective, so it comes first.
• INCLUDE (...): This “covers” the SUM calculations, so SQL Server doesn’t have to look up the data in the main table, which is a massive performance boost.

On the DBCust_erp table:

This table is joined on the Cust_nr column. It also needs an index to make the final LEFT JOIN fast.

SQL

CREATE NONCLUSTERED INDEX IX_DBCust_erp_Cust_nr
ON [onn].[DBCust_erp] ([Cust_nr])
INCLUDE ([Top_segment], [CompanyType], [Potencial]);

• ON ([Cust_nr]): Speeds up the join to the subquery k.
• INCLUDE (...): Covers the columns you are selecting, again avoiding a second lookup to the main table.

Optimizing the T-SQL Query 🚀

Your query has a “non-SARGable” join condition. SARGable means Search Argumentable, allowing SQL to use an index.

The problem is this line: dateadd(year, 1, b.dt_ym) = a.Dt_ym. By applying the dateadd function to the column b.dt_ym, you force SQL Server to calculate a new value for every single row in the table before it can perform the join. This guarantees a slow table scan.

The fix is to apply the function to the other side of the equation, so the column itself remains “clean.”

Change this: ON dateadd(year, 1, b.dt_ym) = a.Dt_ym

To this: ON b.dt_ym = dateadd(year, -1, a.Dt_ym)

This small change allows SQL Server to take a value from a, calculate the prior year’s date once, and then perform a highly efficient index seek on b.dt_ym to find the matching row.

The Final Optimized Query

For better readability, I’ve rewritten your query using a Common Table Expression (CTE). The logic is the same, but it’s easier to follow. This version incorporates the SARGable join fix.

SQL

-- Use a CTE for better readability to get the current and prior year sums
WITH CustomerMonthlyStats AS (
    SELECT
        a.[Dt_ym],
        a.[CustomerIdEx],
        SUM(a.ErpRegisterDoc_count) AS ErpRegisterDoc_count,
        SUM(a.ErpValueNet_sum) AS ErpValueNet_sum,
        SUM(b.ErpValueNet_sum) AS ErpValueNet_sum_py -- Sum from the previous year
    FROM
        [onn].[DBCust_stat_baskets] AS a
    LEFT JOIN
       [onn].[DBCust_stat_baskets] AS b
        -- OPTIMIZED: The function is now on the outer side of the join, making it SARGable
        ON a.[CustomerIdEx] = b.[CustomerIdEx]
        AND b.dt_ym = DATEADD(year, -1, a.Dt_ym)
    GROUP BY
        a.[Dt_ym],
        a.[CustomerIdEx]
)
-- Final SELECT joining the aggregated stats with customer details
SELECT
    k.*,
    c.[Top_segment],
    c.[CompanyType],
    c.[Potencial]
FROM
    CustomerMonthlyStats AS k
LEFT JOIN
    [onn].[DBCust_erp] AS c ON k.CustomerIdEx = c.Cust_nr;

By creating these indexes and applying this simple query fix, you should see a dramatic improvement in performance, going from potentially minutes to seconds. I’d also recommend removing the WITH (NOLOCK) hints after these changes, as they won’t be necessary and can lead to reading “dirty” or incorrect data.

You also converted my query to CTE, but I cant use cte in Power BI direct query, that is why I used like that. Do I don’t know something or CTE is really better then my version of “cascade” querying?

That’s an excellent point about Power BI DirectQuery’s limitations with CTEs. You are correct; you often have to use subqueries (also called derived tables) instead.

To answer your main question: for this specific query, a CTE is not better than your subquery in terms of performance. The SQL Server query optimizer is smart enough to see that both your subquery version and the CTE version are logically identical. It will generate the exact same execution plan for both, meaning they will run at the same speed.

The primary advantage of a CTE is readability and maintainability, not performance. It helps break down complex logic into clean, sequential steps, making the query easier for a human to understand and debug later.

The Important Part: The Optimization Itself

The crucial takeaway from the previous answer was not the switch to a CTE, but these two points:

1. Creating the right indexes on the tables.
2. Fixing the non-SARGable join condition.

As long as you apply those two changes, your original query structure is perfectly fine and will be just as fast as the CTE version.

Your Original Query, Optimized

Here is your original query with the single, critical change to the JOIN condition. This version avoids CTEs and is ready for Power BI DirectQuery, while still containing the essential performance fix.

SQL

SELECT
    k.*,
    c.[Top_segment],
    c.[CompanyType],
    c.[Potencial]
FROM (
    -- This subquery is functionally the same as the CTE
    SELECT
        a.[Dt_ym],
        a.[CustomerIdEx],
        SUM(a.ErpRegisterDoc_count) AS ErpRegisterDoc_count,
        SUM(a.ErpValueNet_sum) AS ErpValueNet_sum,
        SUM(b.ErpValueNet_sum) AS ErpValueNet_sum_py
    FROM
        [onn].[DBCust_stat_baskets] AS a WITH (NOLOCK)
    LEFT JOIN
        [onn].[DBCust_stat_baskets] AS b WITH (NOLOCK)
        -- The single most important optimization is right here:
        ON a.CustomerIdEx = b.CustomerIdEx AND b.dt_ym = DATEADD(year, -1, a.Dt_ym)
    GROUP BY
        a.[Dt_ym],
        a.[CustomerIdEx]
) AS k
LEFT JOIN
   [onn].[DBCust_erp] AS c WITH (NOLOCK) ON k.CustomerIdEx = c.Cust_nr;