Search code examples
javasqldatabaseinsertderby

Increasing INSERT speed

I'm currently using the following query to insert into a table only if the record does not already exist, presumably this leads to a table scan. It inserts 28000 records in 10 minutes: 

INSERT INTO tblExample(column)
(SELECT ? FROM tblExample WHERE column=? HAVING COUNT(*)=0)

If I change the query to the following, I can insert 98000 records in 10 minutes: 

INSERT INTO tblExample(column) VALUES (?)

But it will not be checking whether the record already exists.

Could anyone suggest another way of querying such that my insert speed is faster?

Solution

  • One simple solution (but not recommended) could be to simply have insert statement, catch duplicate key exception and log them. Assuming that the table has unique key constraint.