Optimize SELECT UNION | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Optimize SELECT UNION

There is a stored procedure in one of DBs (and the DB size is about 70 GB) which is as below. SELECT …
FROM {Inner Join of 10 tables and views}
GROUP BY …
UNION
SELECT …
FROM {Inner joins of 10 tables and views}
GROUP BY …
UNION
SELECT …
FROM {Inner joins of 10 tables and views}
GROUP BY …
UNION
SELECT …
FROM {Inner joins of 10 tables and views}
GROUP BY …
I don’t know the DB yet and have recently inherited it. Is there a better way to code the SP in order to optimize and increase its performance?
thanks,

UNION removes duplicates. Maybe it could be changed to a UNION ALL? But sine you don’t know that db yet, I would be very careful doing anything yet without seeing more of the big picture.

If it’s basically the same query statement each time, but with a different WHERE clause, then I’d check if procedure parameters are also part of the criteria, like this:
CREATE PROC MyProc (@p1 BIT, @p2 BIT)
AS
SELECT col1, col2 FROM MyTable WHERE @p1 = 1 AND col1 = ‘x’
UNION
SELECT col1, col2 FROM MyTable WHERE @p2 = 1 AND col1 = ‘y’
For this kind of conditional filtering a better option might be to use dynamic SQL in your procedure, by calling sp_ExecuteSQL with proper parameters.

Another method would be use optional parameters
SELECT col1, col2 FROM MyTable
WHERE
(@p1 = 1 AND col1 = ‘x’)
OR
(@p2 = 1 AND col1 = ‘y’)
OR
.
.
.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |