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

Optimizing a query

Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi
I have a query which I would like to optimize and thought I had found a
solution.
The query is a join of several selects from several different tables.
So we have in general the following:
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
or something like that. Of course my query was a lot larger than this,
but it is just to illustrate the problem.
The above was too slow, and I made an attempt to optimize it and
thought it did work. The following is what I did:
Instead of the SELECT's I could create temporary tables and select into
those so I get the following:
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
And then join those two tables:
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
and at the end drop the temporary tables.
DROP Table #temp_table1
DROP Table #temp_table2
This gives the same result as the original statement.
I profiled a bit and saw that if I created the temp tables, and ran the
join statement several times after each other, the first time it is run
it take a lot of time compared to the following. Can anyone explain to
me why this is the case? I thought that the temporary tables were
created and loaded into the cache, and therefore all calls to the big
JOIN statement should take equally long time.
The thing is that I cannot reuse the temporary tables, so I get no
benefit from all the above.
Thank you very much in advance
Jeeji|||Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote: