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?

thanksIf 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.googlegr oups.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!|||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.|||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.googlegr oups.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|||THANKS THOMAS!sql

Optimization with ORs

Hi- I notice that many times when I use an "or" in a query with an and
select from table where col1=x and (col2 = y or col3=z)
It results in terrible performance. It seems to me that there is no way to
make an index which really covers a statement like this. I can either index
on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
any value. Is this correct? If so, how can I improve performance in this
situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
Thanks,
ChrisYou can use the Show Execution Plan feature of Query Analyzer to see exactly
how your query is utilizing indexes.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
If col2 or col3 is highly selective (for example zipcode or date of birth),
then an additional index here would be useful.
http://www.sql-server-performance.c...s_not_equal.asp
SQL Server's query optimizer may even ignore an index when building the
execution plan if the index is not judged as selective enough for it's case
usage.
http://www.sql-server-performance.com/statistics.asp
http://msdn.microsoft.com/library/d.../>
sz_4fxv.asp
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:63A055ED-F3D7-4D95-B887-5F9C16C03F35@.microsoft.com...
> Hi- I notice that many times when I use an "or" in a query with an and
> select from table where col1=x and (col2 = y or col3=z)
> It results in terrible performance. It seems to me that there is no way to
> make an index which really covers a statement like this. I can either
> index
> on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
> any value. Is this correct? If so, how can I improve performance in this
> situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
> Thanks,
> Chris|||"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:63A055ED-F3D7-4D95-B887-5F9C16C03F35@.microsoft.com...
> Hi- I notice that many times when I use an "or" in a query with an and
> select from table where col1=x and (col2 = y or col3=z)
> It results in terrible performance. It seems to me that there is no way to
> make an index which really covers a statement like this. I can either
> index
> on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
> any value. Is this correct? If so, how can I improve performance in this
> situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
> Thanks,
> Chris
Have you tried a covering index on col1/col2 and another on col1/col3?
Also, try rewritting the query using Union All:
select from table where col1=x and col2 = y
union all
select from table where col1=x and col3 = z|||Chris,
a lot depends on selectivity of the columns. For instance, if col1 is
highly selective, then an index on (col1, col2, col3) might be very
useful.|||Raymond,
a minor correction:
select from table where col1=x and col2 = y
union all
select from table where col1=x and col3 = z
-- do not introduce duplicates
and not(col2 = y)|||Would a Union really be any faster than an or? Isn't it essentially the same
thing?
Chris
"Alexander Kuznetsov" wrote:

> Raymond,
> a minor correction:
> select from table where col1=x and col2 = y
> union all
> select from table where col1=x and col3 = z
> -- do not introduce duplicates
> and not(col2 = y)
>|||You've mentionned queries with terrible performance.
Modify one of them using Union and compare.
I can't say that this will solve all your problems though.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:400D4757-8B2B-4DB2-9A78-92683567A820@.microsoft.com...
> Would a Union really be any faster than an or? Isn't it essentially the
> same
> thing?
> Chris
> "Alexander Kuznetsov" wrote:
>|||Chris,
in some cases a query with OR will be executed as a table/clustered
index scan. Rewriting it as a UNION, you may get the same results
retrieved by 2 index ss, possibly followed by a sort, if it's UNION
not a UNION ALL. I don't think anybody can tell you what's better in
your case without getting more information|||Good catch Alex.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1140796390.674385.23120@.v46g2000cwv.googlegroups.com...
> Raymond,
> a minor correction:
> select from table where col1=x and col2 = y
> union all
> select from table where col1=x and col3 = z
> -- do not introduce duplicates
> and not(col2 = y)
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:587028
If you have many queries like that, then maybe your schema is not
properly normalized. Do you have a real life example?
Gert-Jan
querylous wrote:
> Hi- I notice that many times when I use an "or" in a query with an and
> select from table where col1=x and (col2 = y or col3=z)
> It results in terrible performance. It seems to me that there is no way to
> make an index which really covers a statement like this. I can either inde
x
> on col1, col2 or col1, col3; an index on col1, col2, col3 is not really of
> any value. Is this correct? If so, how can I improve performance in this
> situation? Do I need 2 indexes? Do I just index col1 and leave it at that?
> Thanks,
> Chrissql