Re: ignore unique violation OR check row exists

Samuel Gendler wrote:
>
>
> On Wed, Jan 4, 2012 at 1:57 AM, Zdravko Balorda
> <zdravko(dot)balorda(at)siix(dot)com <mailto:zdravko(dot)balorda(at)siix(dot)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