union

  • which is the optimized query?

    insert into select * from table1

    insert into select * from table2

    insert into select * from table3

    or

    insert into select * from table1

    union

    insert into select * from table2

    union

    insert into select * from table3

    The 3 tables hold unique records .........

  • Neither one. They will run differently. The first runs as 3 separate transactions, assuming you do not have implicit transactions changed. It could result in some data being inserted and some not.

    I'm not sure the second is valid. It should be

    insert into tablexx

    select * from table1

    union

    select * from table2

    union

    select * from table3

    And it will require more resources as it will pull all the data into a union query before inserting it. It will run as one transaction, so you'll get all data or none inserted.

  • You may even get a different number of rows inserted into the table in the second one. Union eliminates duplicate rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Actually, if you look at the bigger picture, you have a much larger problem... why would you select from 3 different tables into 1? Except, possibly, for reporting, you now have duplicate data in the database... and that's a larger problem.

    And, if you want the two queries to match perfectly, you must use UNION ALL instead of UNION, as Steve and Gail kinda suggested.

    On a differenet note... where has the sense of adventure and discovery in the world of programing gone? Why would anyone possibly take the word of other folks without building a test of your own? "Trust, but verify!" And, in doing so, you might learn something you hadn't intended to. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If you do a UNION ALL you will get all the rows and take less resources as there is no need to sort out the DISTINCT rows.

    http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/

    There are also some references to test trials with multiple insert commands vs one insert with mult Select/Union All clauses mostly indicating that the single Insert stmt is the better performer.

    http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

    When I tried it 3 separate ways (multiple inserts, insert with mult select/Union and insert with mult Select/Union All), Union All took the least time (Constant scan + Concat), Union took about another 30% or so (had to do Merges (for distinct) instead of Concatenation ) and multiple INSERTs was the most costly by far (each Insert was about the same cost as the single UNION ALL insert so the total is orders of Magnitude more).

    Of course your results may vary.

    Toni

    /* Multiple Inserts */

    /* Multiple Insert Statements */

    DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

    SET DATEFORMAT DMY

    INSERT@Sample

    SELECT'inv1', 'acc1', '01/01/2007'

    Insert @sample

    SELECT'inv2', 'acc1', '01/02/2007'

    Insert @sample

    SELECT'inv3', 'acc1', '01/03/2007'

    Insert @sample

    SELECT'inv4', 'acc1', '01/04/2008'

    Insert @sample

    SELECT'inv5', 'acc2', '11/05/2007'

    Insert @sample

    SELECT'inv6', 'acc2', '12/06/2007'

    Insert @sample

    SELECT'inv7', 'acc2', '01/07/2008'

    Insert @sample

    SELECT'inv8', 'acc3', '13/08/2007'

    /* SINGLE INSERT WITH UNION */

    /* SINGLE INSERT WITH UNION *?

    DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

    SET DATEFORMAT DMY

    INSERT@Sample

    SELECT'inv1', 'acc1', '01/01/2007' UNION

    SELECT'inv2', 'acc1', '01/02/2007' UNION

    SELECT'inv3', 'acc1', '01/03/2007' UNION

    SELECT'inv4', 'acc1', '01/04/2008' UNION

    SELECT'inv5', 'acc2', '11/05/2007' UNION

    SELECT'inv6', 'acc2', '12/06/2007' UNION

    SELECT'inv7', 'acc2', '01/07/2008' UNION

    SELECT'inv8', 'acc3', '13/08/2007'

    /* SINGLE INSERT WITH UNION ALL */

    /* SINGLE INSERT WITH UNION ALL */

    DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

    SET DATEFORMAT DMY

    INSERT@Sample

    SELECT'inv1', 'acc1', '01/01/2007' UNION ALL

    SELECT'inv2', 'acc1', '01/02/2007' UNION ALL

    SELECT'inv3', 'acc1', '01/03/2007' UNION ALL

    SELECT'inv4', 'acc1', '01/04/2008' UNION ALL

    SELECT'inv5', 'acc2', '11/05/2007' UNION ALL

    SELECT'inv6', 'acc2', '12/06/2007' UNION ALL

    SELECT'inv7', 'acc2', '01/07/2008' UNION ALL

    SELECT'inv8', 'acc3', '13/08/2007'

    Toni

  • Hmmm.... I missed the 3 different table part so let me know if you try that out. It would be interesting to see if things change.

    Thank you

    Toni

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply