[SQL] How to Optimize Multiple SQL Queries with Similar Joins and Conditions in a Stored...

I'm currently working with a large dataset in SQL Server, and I have three different SELECT queries that I need to execute, each with slightly different conditions:

-- Query 1
SELECT * FROM Customers
WHERE CustomerID > 80;

-- Query 2
SELECT * FROM Customers
WHERE CustomerID = 1;

-- Query 3
SELECT * FROM Customers
WHERE Country = 'Mexico';


Each query uses the same set of JOIN clauses and additional conditions like this:

FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
LEFT JOIN OtherTables OT ON C.SomeID = OT.SomeID
WHERE C.Active = 1 -- Common condition
AND O.OrderStatus = 'Completed'

My Current Approach


I'm using a stored procedure where I execute all three queries one after another, and each result is stored in a different variable using SET:

SET @result1 = (/* Query 1 with common joins and conditions */);
SET @result2 = (/* Query 2 with common joins and conditions */);
SET @result3 = (/* Query 3 with common joins and conditions */);


However, executing these queries takes a lot of time due to the large amount of data, and sometimes it even kills the session, resulting in no output.

What I Need Help With

1. Optimization: How can I optimize this stored procedure to reduce execution time and prevent the session from getting killed?
2. Efficient Use of Joins: Is there a way to avoid repeating the common JOIN and WHERE conditions for each query while still storing the results in separate variables?
3. Best Practices: Are there any best practices for handling multiple queries with similar joins and conditions in SQL Server?

I attempted to use CTEs to reduce repetition, but CTEs only allow one SELECT statement to follow, which doesn't work for my requirement of storing multiple results into different variables.

Any suggestions on how to optimize these queries or the stored procedure itself would be greatly appreciated!

Continue reading...