Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL Server
Hi Friends ,

There is a SP which has more than 15 Unions and each returns result set with huge rows
How can i increase performance of that SP

Thanks in Advance
Posted 28-Feb-13 2:22am
Mike Meinz at 28-Feb-13 7:59am
A good first step would be to let those that you want help from see the database table schema and the SELECT statement(s) that require optimization.
A second step would be to question why a database is designed such that a query requires 15 UNION operators.
Jörgen Andersson at 2-Mar-13 15:41pm
It's a pity one can't upvote a comment.
Member 8381937 at 28-Feb-13 8:15am
I replaced union to union all
and with common table expression i used with distinct due to duplicacy
Like that

with cte_result
select colums.... from A
union all
select colums.... from B
union all
select colums.... from C
select distinct * from cte_result
can this increase some performance????
Mike Meinz at 28-Feb-13 8:53am
If there are Where clauses associated with each Select statement, then (generally) having well-constructed indexes for the keys used in the Where clauses will help. I'm sorry but I don't have anything further to offer you.
AspDotNetDev at 28-Feb-13 12:17pm
I was going to suggest a UNION ALL, but it seems you've already done that. Not sure what type of performance you're getting, but the problem may something aside from the unions. You could avoid the DISTINCT and then filter the results on the machine that's getting the results.
SudhirKankal at 3-Jun-13 6:07am
SQL tuning requires knowledge of data, columns and its relationship. To have better tuned SQL, you need to share the actual SQL.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advertise | Privacy | Mobile
Web04 | 2.8.150327.1 | Last Updated 28 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100