Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    Unanswered: query insert and predicate on the same object

    Hey all,

    I've a querie which inserts into a table, while joining on it. Something like:

    insert into table1
    (columnA,
    columnB
    )
    select (columnA,columnB)
    from table2
    where columnA NOT EXIST(SELECT 1 from table1,table2 where table1.columnA!=table2.columnA)

    As a rule of thumb, I avoid subselects and negative logic. However, sql2k seems to pick the fastest execution plan using this query, using a left anti semi join on that subselect. My question is this; what are the repercussions of inserting into a table when sql2k has already picked a query plan based on its stats? Is deadlocking possible? I haven't been able to find any evidence one way or the other. My objects are several million rows, so I am expecting some sort of side effect.

    Any help would be greatly appreciated.

    Regards,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you referred to the execution plan on QA?
    Try to insert rows in batches and perform regular Tlog backups to avoid transaction log growth.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Kilka101
    As a rule of thumb, I avoid subselects and negative logic.

    Why? What put that notion in your head?

    You need to look at the plan...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •