Click here to Skip to main content
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 2: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 Sergey Alexandrovich Kryukov 545
1 OriginalGriff 285
2 Shweta N Mishra 244
3 Mathew Soji 225
4 BillWoodruff 200
0 OriginalGriff 6,153
1 Sergey Alexandrovich Kryukov 5,758
2 DamithSL 4,958
3 Manas Bhardwaj 4,539
4 Maciej Los 3,735


Advertise | Privacy | Mobile
Web01 | 2.8.1411019.1 | Last Updated 28 Feb 2013
Copyright © CodeProject, 1999-2014
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