April 22, 2012 at 6:59 pm
I am trying to see if i can replace 35 Union ALL's on 35 table in my view. I will try to post the actual code, but i am open for any suggestion which could enhance the performance.
April 22, 2012 at 7:11 pm
You should read this blog posting:
Which contains:
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
The blog post also contains sample code so that you can test for yourself which, in your case, would be the most efficient.
April 23, 2012 at 5:14 am
What I'm about to suggest may or may not help you. Performance is dependent on your schema and data. So test this out for performance before you put it into Production.
1) Use a temp table and do individual inserts, then SELECT the resultant data from the temp table.
2) Use CTEs to put the data together.
3) Look at your 35 UNION ALLs and determine if someone wrote them that way because they were being lazy with WHERE clauses or if you can combine them into one SELECT, using CASE expressions and other such tricks to get the individual columns to say what they should.
April 23, 2012 at 10:31 am
Please post the actual code.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 12:12 pm
I'd have to see the reason for the Union All statements (evident from the query, most likely) to be more specific, but if it's all coming from one table or the same tables, then it's probably just someone trying to avoid OR statements in a Where clause. That's the most usual reason I see for that. It was a very valid performance-tuning technique in older versions of SQL Server, but not so valid since SQL 2005.
It can often be avoided and simplified by using a parameterized dynamic query. That's usually faster, too, as well as easier to maintain.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 2:37 pm
GSquared (4/23/2012)
I'd have to see the reason for the Union All statements (evident from the query, most likely) to be more specific, but if it's all coming from one table or the same tables, then it's probably just someone trying to avoid OR statements in a Where clause. That's the most usual reason I see for that. It was a very valid performance-tuning technique in older versions of SQL Server, but not so valid since SQL 2005.It can often be avoided and simplified by using a parameterized dynamic query. That's usually faster, too, as well as easier to maintain.
Union is done across 35 different tables.
April 23, 2012 at 2:46 pm
Why do you want to replace the union all then? For concatenating multiple tables, there isn't usually a faster way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 9:37 pm
Not such a great solution but I replaced this with a table.I have a job which will basically populate the table and the stored procedure will just read from the table.
April 24, 2012 at 8:12 am
sqldba_newbie (4/23/2012)
GSquared (4/23/2012)
I'd have to see the reason for the Union All statements (evident from the query, most likely) to be more specific, but if it's all coming from one table or the same tables, then it's probably just someone trying to avoid OR statements in a Where clause. That's the most usual reason I see for that. It was a very valid performance-tuning technique in older versions of SQL Server, but not so valid since SQL 2005.It can often be avoided and simplified by using a parameterized dynamic query. That's usually faster, too, as well as easier to maintain.
Union is done across 35 different tables.
As far as I know, UNION is the only construct for combining multiple resultsets. If this union is used frequently, and you want to simplify your T-SQL coding, then implementing those 35 seperate tables as one partitioned table is an option. However, that would probably require much refactoring and would have an unknown impact on performance. A partitioned table could perform worse than a partitioned view; you would have to mock this up in a development database to confirm.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 25, 2012 at 2:57 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply