Click here to Skip to main content
11,701,616 members (68,473 online)
Rate this: bad
good
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

Shreeniwas
Posted 28-Feb-13 1:22am
Comments
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)

  Print Answers RSS
0 OriginalGriff 324
1 Maciej Los 275
2 Sergey Alexandrovich Kryukov 250
3 Mika Wendelius 189
4 CHill60 110
0 OriginalGriff 8,274
1 Sergey Alexandrovich Kryukov 7,317
2 CPallini 4,912
3 Maciej Los 4,146
4 Mika Wendelius 3,556


Advertise | Privacy | Mobile
Web04 | 2.8.150819.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