make a better query

  • 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

     

     

     

     

      

     

  • 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

  • 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.

  • 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