|
Home > Archive > Microsoft SQL Server forum > April 2006 > Optimizing SQL - Union
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Optimizing SQL - Union
|
|
|
| Hello all,
I have a table with thousands of rows and is in this format:
id col1 col2 col3 col4
--- ------ ----- ------ ------
1 nm 78 xyz pir
2 bn 45 abc dir
I now want to get the data from this table in this format:
field val
---------------------------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir
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?
The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.
my main concern is performance. any ideas please?
thanks
| |
| Tom Moreau 2006-03-29, 1:29 pm |
| If you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:
select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@gmail.com> wrote in message
news:1143651738.146958.160520@t31g2000cwb.googlegroups.com...
Hello all,
I have a table with thousands of rows and is in this format:
id col1 col2 col3 col4
--- ------ ----- ------ ------
1 nm 78 xyz pir
2 bn 45 abc dir
I now want to get the data from this table in this format:
field val
---------------------------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir
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?
The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.
my main concern is performance. any ideas please?
thanks
| |
|
| Ok, I will try this. I am a liitle bit confused about the cross join..
I will test and let you know. Thanks!
| |
| Hugo Kornelis 2006-03-29, 8:26 pm |
| 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
| |
|
| that's a really good advice, didn't know what 'union all' meant all
these days.
I tried Thomas approach and it is much faster than before.
thanks a lot guys.
| |
| Tom Moreau 2006-04-01, 11:26 am |
| Been away for a while. Here's a correction:
select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col2' union all
select 'col3' union all
select 'col4'
) as x (col)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:8izWf.1879$m35.157124@news20.bellglobal.com...
If you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:
select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@gmail.com> wrote in message
news:1143651738.146958.160520@t31g2000cwb.googlegroups.com...
Hello all,
I have a table with thousands of rows and is in this format:
id col1 col2 col3 col4
--- ------ ----- ------ ------
1 nm 78 xyz pir
2 bn 45 abc dir
I now want to get the data from this table in this format:
field val
---------------------------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir
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?
The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.
my main concern is performance. any ideas please?
thanks
| |
|
|
|
|
|