May 20, 2004 at 9:33 am
Hello all,
I wonder if there is a better way to handle the following data structure:
I have an original table with following columns (Up to 3 Price Columns):
ProductNumber Price1 Price2 Price3
1001 20.00 25.00 30.00
1002 15.00 17.00 19.00
1003 22.00 19.00 NULL
I need to write a query to transform it into the following:
ProductNumber Price
1001 20.00
1001 25.00
1001 30.00
1002 15.00
1002 17.00
......................................
Currently, I have something like that:
Select ProductNumber, Price1 from MyTable 1
UNION
Select ProductNumber, Price2 from MyTable 2
.................................................
IS there a better way to handle that?
TIA
May 20, 2004 at 9:15 pm
Hi.
If ProductNumber is primary key column, Query Optimizer using Merge Join algorithm. There is no something better I think.
Probably, better is using 3 columns in application dataset like:
rs.Open "Select * from [original table]"
while not rs.Eof
PN = rs("ProductNumber")
p1 = rs("Price1")
...
rs.MoveNext
Wend
May 21, 2004 at 12:37 am
UNION ALL performs better than UNION, since the merge join is avoided, and SQL Server uses CONCATENATION instead. Significant differences can be noticed for large tables only. For example, on my server , on a products table having 1,000,000 rows, this query (using UNION):
select count(*) from
(
select pn, p1 from products as T1
union select pn, p2 from products as T2
union select pn, p3 from products as T3
) as tmp
completes in 3 second, while this query (using UNION ALL):
select count(*) from
(
select pn, p1 from products as T1
union all select pn, p2 from products as T2
union all select pn, p3 from products as T3
) as tmp
completes in 1 second.
Of course, this stands only if it is according to the application logic.
Regards,
Goce Smilevski.
May 21, 2004 at 1:34 pm
I would also recommend UNION ALL
Of course I would prefer to see a more normalized table
Michael R. Schmidt
Developer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply