Re: ignore unique violation OR check row exists

Samuel Gendler wrote:
> 
> 
> On Wed, Jan 4, 2012 at 1:57 AM, Zdravko Balorda 
> <zdravko.balorda@siix.com <mailto:zdravko.balorda@siix.com>> wrote:
>     Take it out of transaction. Why is there a transaction in the first
>     place?
>     If transaction is needed, ok, but take these inserts out and
>     everything will
>     work as it should. Ignoring UNIQUE VIOLATION or any other error
>     defeats the very
>     purpose of transaction. That's why you can't ignore it.
> 
> 
> Unfortunately, bulk inserts are much slower when they don't occur in a 
> transaction.  Try inserting 1 million rows with auto commit enabled vs 1 
> million rows in 1 transaction, or even 10 or 100 transactions. The 
> difference is enormous.  The bulk insert into an unconstrained table and 
> then pulling just the new rows over into the destination table in a 
> single transaction is definitely the most effective way to do this.
> 

I do a lot of bulk inserts. What helps is dropping indexes before insert
and recreating it after. Probably you need to better organize data to
avoid having primary keys on a table with a lots of data.

Zdravko