SQL Query Optimization: Grouping by REFId in a Complex Scenario

In this article, we’ll delve into the world of SQL query optimization, focusing on grouping data based on a specific field. We’ll explore common pitfalls and provide solutions for optimizing complex queries.

Understanding the Current Query

The provided SQL query is designed to retrieve data from multiple tables, including ts, poi, and t. The goal is to group related projects together based on a shared REFId.

SELECT ts.ID,
       ts.RequestName,
       ts.OncorStationName,
       ts.Category,
       ts.ReceptionDate,
       ts.County,
       ts.Voltage,
       ts.TSCAssignment,
       ts.MWCapacity,
       ts.InServiceDateRequested,
       ts.FEASignedDate,
       ts.DSASignedDate,
       ts.TransmissionServicesComments,
      
      (CASE 
         When (t.id = 1353 OR t.id = 1527) then 6
         When (t.id = 1468 OR t.id = 1462) then 7
         When (t.id = 1458 OR t.id = 1592) then 13
         When (t.id = 1594 OR t.id = 1622) then 15 
         When (t.id = 1369 OR t.id = 1602 OR  t.id = 1616 OR t.id = 1627) then 39
         When (t.id = 1367 OR t.id = 1419) then 4       
         When (t.id = 1549 Or t.id = 1625 ) then 93
         Else Null End) As Ref 
      
FROM [rpt].[TransmissionServicesRpt] ts
INNER JOIN [dbo].[TransmissionServicePOI] poi ON ts.id = poi.TransmissionServiceId
INNER JOIN [rpt].[TransmissionServicesRpt] t ON t.id = ts.id
WHERE ts.Status = 'active'
ORDER BY ts.FEASignedDate DESC;

Grouping by REFId: The Challenge

The provided query is trying to group related projects together based on a shared REFId, but it’s encountering an error. To address this, we need to revisit the join logic and consider alternative approaches.

Join Logic Analysis

Upon closer inspection, we notice that the inner join between ts and t may be unnecessary, as both tables have the same structure and are joined on a common column (id). We can simplify this part of the query by removing the second instance of the inner join:

SELECT ts.ID,
       ts.RequestName,
       ts.OncorStationName,
       ts.Category,
       ts.ReceptionDate,
       ts.County,
       ts.Voltage,
       ts.TSCAssignment,
       ts.MWCapacity,
       ts.InServiceDateRequested,
       ts.FEASignedDate,
       ts.DSASignedDate,
       ts.TransmissionServicesComments,
      
      (CASE 
         When (t.id = 1353 OR t.id = 1527) then 6
         When (t.id = 1468 OR t.id = 1462) then 7
         When (t.id = 1458 OR t.id = 1592) then 13
         When (t.id = 1594 OR t.id = 1622) then 15 
         When (t.id = 1369 OR t.id = 1602 OR  t.id = 1616 OR t.id = 1627) then 39
         When (t.id = 1367 OR t.id = 1419) then 4       
         When (t.id = 1549 Or t.id = 1625 ) then 93
         Else Null End) As Ref 
      
FROM [rpt].[TransmissionServicesRpt] ts
INNER JOIN [dbo].[TransmissionServicePOI] poi ON ts.id = poi.TransmissionServiceId
WHERE ts.Status = 'active'
ORDER BY ts.FEASignedDate DESC;

Alternative Approach: Using a Common Table Expression (CTE)

To tackle the grouping issue, we can introduce a common table expression (CTE) to simplify the query. The CTE will help us extract the REFId values and then join this result with the original tables.

WITH RefGroups AS (
  SELECT t.id AS REFId,
         CASE 
           When (t.id = 1353 OR t.id = 1527) then 6
           When (t.id = 1468 OR t.id = 1462) then 7
           When (t.id = 1458 OR t.id = 1592) then 13
           When (t.id = 1594 OR t.id = 1622) then 15 
           When (t.id = 1369 OR t.id = 1602 OR  t.id = 1616 OR t.id = 1627) then 39
           When (t.id = 1367 OR t.id = 1419) then 4       
           When (t.id = 1549 Or t.id = 1625 ) then 93
           Else Null End) AS RefValue
  FROM [rpt].[TransmissionServicesRpt] t
)
SELECT ts.ID,
       ts.RequestName,
       ts.OncorStationName,
       ts.Category,
       ts.ReceptionDate,
       ts.County,
       ts.Voltage,
       ts.TSCAssignment,
       ts.MWCapacity,
       ts.InServiceDateRequested,
       ts.FEASignedDate,
       ts.DSASignedDate,
       rg.RefValue AS RefId,
      
      -- Additional calculations or join with other tables here
FROM [rpt].[TransmissionServicesRpt] ts
INNER JOIN [dbo].[TransmissionServicePOI] poi ON ts.id = poi.TransmissionServiceId
INNER JOIN RefGroups rg ON ts.id = rg.REFId
WHERE ts.Status = 'active'
ORDER BY ts.FEASignedDate DESC;

Conclusion

By revisiting the join logic and introducing a common table expression (CTE), we’ve simplified the query to address the grouping issue based on REFId. This alternative approach should provide a more efficient solution for extracting related projects together.

Last modified on 2025-01-01