Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

INSERT clause with NOT EXISTS performance problem

Status
Not open for further replies.

luvcal

Programmer
Aug 10, 2001
54
US
I'm running into what appears to be a performance problem while issuing an INSERT statement to my DB. I have a table, t1, with approximately 40k records. I would like to insert these records into another table, t2, which could already contain 200k+ records. I have designed the statement so that I would not generate any primary key violations by using an NOT EXISTS clause. Problem is that the INSERT statement now takes forever (was never patient enough to let it finish) to run rather than just 1 minute. Here is the shell of the statements:

No NOT EXISTS clause (about 1 minute to run):
INSERT INTO t2
SELECT
keyField,
otherField1,
otherFields
FROM
t1
WHERE
otherField1 = 'SOME_CONST'

With NOT EXISTS clause (never returns):
INSERT INTO t2
SELECT
keyField,
otherField1,
otherFields
FROM
t1
WHERE
otherField1 = 'SOME_CONST' AND
NOT EXISTS
(SELECT 1 FROM t2 WHERE t2.keyField = t1.keyField)

Am I missing something? Is there another way I should do this?

Thanks for the help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top