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