SQL Server: How to Optimize Performance of Complex Queries Involving Multiple Nested Subqueries
I'm prototyping a solution and I'm working through a tutorial and I've searched everywhere and can't find a clear answer... I'm working with serious performance optimization with a SQL Server query that involves multiple nested subqueries. The query is supposed to extract sales data along with product information for reports, but it takes way too long to execute. I've already indexed the relevant columns, but the execution plan shows a lot of expensive operations, and I'm getting a timeout behavior when trying to run it in our application. Here's the query I'm using: ```sql SELECT s.SaleID, s.SaleDate, p.ProductName, (SELECT SUM(Quantity) FROM OrderDetails od WHERE od.SaleID = s.SaleID) AS TotalQuantity, (SELECT COUNT(*) FROM Feedback f WHERE f.ProductID = p.ProductID AND f.Rating > 4) AS PositiveFeedbackCount FROM Sales s JOIN Products p ON s.ProductID = p.ProductID WHERE s.SaleDate BETWEEN '2023-01-01' AND '2023-12-31'; ``` I tried breaking down the query and executing the subqueries separately, and they seem to return results quickly, but when I combine them, the performance degrades significantly. I also looked into using Common Table Expressions (CTEs), but I'm not sure if they would help in this case since I still need nested aggregations. Is there a way to optimize this query further? Should I consider using joins instead of subqueries, or is there a better way to structure this to improve performance? Any insights or suggestions would be greatly appreciated! I'm working on a application that needs to handle this. Any ideas what could be causing this? Cheers for any assistance! For context: I'm using Sql on Ubuntu 20.04. Any advice would be much appreciated.