Click here to Skip to main content
13,091,134 members (93,314 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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 1:22am
Mike Meinz 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 2-Mar-13 15:41pm
It's a pity one can't upvote a comment.
Member 8381937 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 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 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 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.
Member 12363210 26-Apr-16 6:40am
Check if you can use some temporary tables; and add temporary index on that table.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.170813.1 | Last Updated 28 Feb 2013
Copyright © CodeProject, 1999-2017
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