|
|
Posted by Hugo Kornelis on 03/30/06 01:20
On 29 Mar 2006 09:02:18 -0800, das wrote:
(snip)
>In order to do this I am doing a union:
>
>select * into #tempUpdate
>(
> select 'col1' as field, col1 as val from table1
> union
> select 'col2' as field, col2 as val from table1
> union
> select 'col3' as field, col3 as val from table1
>)
>
>the above example query is smaller - I have a much bigger table with
>about 80 columns (Imagine the size of my union query :) and this takes
>a lot of time to execute. Can someone please suggest a better way to do
>this?
Hi das,
Somewhat simpler than the suggestions Tom posted (and probably less
efficient, but still a major win over your present version) is the
following simple change:
select 'col1' as field, col1 as val from table1
union ALL
select 'col2' as field, col2 as val from table1
union ALL
select 'col3' as field, col3 as val from table1
UNION without ALL will attempt to remove duplicates; with large result
sets, checking for duplicates can be a major performance killer. With
UNION ALL, you say "don't attempt to remove duplicates" - either because
you want them or (in this case) because you're sure there aren't any.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|