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 . . .