Thread: Good way to insert/update when you're not sure of duplicates?

Good way to insert/update when you're not sure of duplicates?

From
Curtis Stanford
Date:
I'm in a situation where I need to load an ASCII file into a database. No
sweat so far right? The records are indexed by date and I have a unique index
on date. The ASCII can overlap, meaning it can contain duplicate dates that
have been loaded before.

I started out with this ingenious idea. Just try to insert the record. If I
get an error about duplicate keys, do an update and switch to update mode. If
I'm updating and get zero rows updated, switch back to insert. Works fine,
except this whole thing is in a transaction and any errors abort the
transaction and I can't commit it. So, I decided I'd have to try to update
every single record and, if zero rows were updated, do the insert. This takes
a looooong time. Many hours for just 86000 records or so.

Is there a standard way to do this. I can't imagine I'm the only guy that need
to do this kind of thing.

I'm using Java with the postgresql JDBC driver if that matters.

Thanks for any suggestions.

Curtis Stanford
curtis@stanfordcomputing.com


Re: Good way to insert/update when you're not sure of duplicates?

From
Richard Huxton
Date:
On Thursday 25 September 2003 02:23, Curtis Stanford wrote:
> I'm in a situation where I need to load an ASCII file into a database. No
> sweat so far right? The records are indexed by date and I have a unique
> index on date. The ASCII can overlap, meaning it can contain duplicate
> dates that have been loaded before.
>
> I started out with this ingenious idea. Just try to insert the record. If I
> get an error about duplicate keys, do an update and switch to update mode.
> If I'm updating and get zero rows updated, switch back to insert. Works
> fine, except this whole thing is in a transaction and any errors abort the
> transaction and I can't commit it. So, I decided I'd have to try to update
> every single record and, if zero rows were updated, do the insert. This
> takes a looooong time. Many hours for just 86000 records or so.
>
> Is there a standard way to do this. I can't imagine I'm the only guy that
> need to do this kind of thing.

Try inserting a batch of 1024. If you get an error, drop down to 512 and try
that. Repeat until the batch works or you've reached a size of 1.
If the batch worked, try the next set of records and repeat. If you reached a
batch size of 1 with no success then, switch to updating, and repeat the
cycle increasing your batch-size as you go.

You might find it quickest to halve batch-size while having problems then
doubling while it works. The balance is going to depend on how many insert vs
update rows you have.

--
  Richard Huxton
  Archonet Ltd

Re: Good way to insert/update when you're not sure of duplicates?

From
Richard Huxton
Date:
On Thursday 25 September 2003 18:32, Curtis Stanford wrote:
> On September 25, 2003 11:27 am, Richard Huxton wrote:
[snip]
> > You might find it quickest to halve batch-size while having problems then
> > doubling while it works. The balance is going to depend on how many
> > insert vs update rows you have.
>
> Hey thanks! I actually got the time down to around 1000 rows/sec. I was
> passing in one of the int arguments in the update where clause as a double.
> I don't know why, but changing it to an int as it should be drastically
> reduced the time. Your solution is very interesting. I'll probably try it
> and see which is faster.

That'll be the infamous PG typecast issue with indexes. PG is quite flexible
about you defining your own types, operators etc. To balance this it's also
quite careful about typecasting stuff without being told to. You tend to
notice this with function-calls and indexes not being used when they should.

If the index had been on a double and you'd passed an int, it might have
figured it out, but I tend to be very strict about my types in any case.
Actually, that's probably a good thing most of the time.

--
  Richard Huxton
  Archonet Ltd

Re: Good way to insert/update when you're not sure of duplicates?

From
Shridhar Daithankar
Date:
Richard Huxton wrote:

> On Thursday 25 September 2003 02:23, Curtis Stanford wrote:
>
>>I'm in a situation where I need to load an ASCII file into a database. No
>>sweat so far right? The records are indexed by date and I have a unique
>>index on date. The ASCII can overlap, meaning it can contain duplicate
>>dates that have been loaded before.
>>
>>I started out with this ingenious idea. Just try to insert the record. If I
>>get an error about duplicate keys, do an update and switch to update mode.
>>If I'm updating and get zero rows updated, switch back to insert. Works
>>fine, except this whole thing is in a transaction and any errors abort the
>>transaction and I can't commit it. So, I decided I'd have to try to update
>>every single record and, if zero rows were updated, do the insert. This
>>takes a looooong time. Many hours for just 86000 records or so.
>>
>>Is there a standard way to do this. I can't imagine I'm the only guy that
>>need to do this kind of thing.
>
>
> Try inserting a batch of 1024. If you get an error, drop down to 512 and try
> that. Repeat until the batch works or you've reached a size of 1.
> If the batch worked, try the next set of records and repeat. If you reached a
> batch size of 1 with no success then, switch to updating, and repeat the
> cycle increasing your batch-size as you go.
>
> You might find it quickest to halve batch-size while having problems then
> doubling while it works. The balance is going to depend on how many insert vs
> update rows you have.
>

In addition to that, you can try inserting from multiple backends simaltenously
to speed up the whole process.

And I don't like the modes idea OP gave. I would rather follow
insert->if-error-update mode in a transaction for each record. And fork over say
20/40 parallel backends to achieve good speed.

  Shridhar


Re: Good way to insert/update when you're not sure of duplicates?

From
Curtis Stanford
Date:
On September 25, 2003 11:27 am, Richard Huxton wrote:
> On Thursday 25 September 2003 02:23, Curtis Stanford wrote:
> > I'm in a situation where I need to load an ASCII file into a database. No
> > sweat so far right? The records are indexed by date and I have a unique
> > index on date. The ASCII can overlap, meaning it can contain duplicate
> > dates that have been loaded before.
> >
> > I started out with this ingenious idea. Just try to insert the record. If
> > I get an error about duplicate keys, do an update and switch to update
> > mode. If I'm updating and get zero rows updated, switch back to insert.
> > Works fine, except this whole thing is in a transaction and any errors
> > abort the transaction and I can't commit it. So, I decided I'd have to
> > try to update every single record and, if zero rows were updated, do the
> > insert. This takes a looooong time. Many hours for just 86000 records or
> > so.
> >
> > Is there a standard way to do this. I can't imagine I'm the only guy that
> > need to do this kind of thing.
>
> Try inserting a batch of 1024. If you get an error, drop down to 512 and
> try that. Repeat until the batch works or you've reached a size of 1.
> If the batch worked, try the next set of records and repeat. If you reached
> a batch size of 1 with no success then, switch to updating, and repeat the
> cycle increasing your batch-size as you go.
>
> You might find it quickest to halve batch-size while having problems then
> doubling while it works. The balance is going to depend on how many insert
> vs update rows you have.

Hey thanks! I actually got the time down to around 1000 rows/sec. I was
passing in one of the int arguments in the update where clause as a double. I
don't know why, but changing it to an int as it should be drastically reduced
the time. Your solution is very interesting. I'll probably try it and see
which is faster.


Re: Good way to insert/update when you're not sure of duplicates?

From
Dennis Gearon
Date:
Shridhar Daithankar wrote:

>
> In addition to that, you can try inserting from multiple backends
> simaltenously to speed up the whole process.
>
> And I don't like the modes idea OP gave. I would rather follow
> insert->if-error-update mode in a transaction for each record. And
> fork over say 20/40 parallel backends to achieve good speed.
>
I didn't think mulitple backends worked.


Re: Good way to insert/update when you're not sure of duplicates?

From
Shridhar Daithankar
Date:
On Friday 26 September 2003 20:48, Dennis Gearon wrote:
> Shridhar Daithankar wrote:
> > In addition to that, you can try inserting from multiple backends
> > simaltenously to speed up the whole process.
> >
> > And I don't like the modes idea OP gave. I would rather follow
> > insert->if-error-update mode in a transaction for each record. And
> > fork over say 20/40 parallel backends to achieve good speed.
>
> I didn't think mulitple backends worked.

Why? Why can't you insert using multiple backends? I mean is there a problem?

Shridhar


Re: Good way to insert/update when you're not sure of duplicates?

From
Shridhar Daithankar
Date:
On Friday 26 September 2003 21:08, Dennis Gearon wrote:
> corruption of the databases, if I'm correct.

> Shridhar Daithankar wrote:
> >On Friday 26 September 2003 20:48, Dennis Gearon wrote:
> >>I didn't think mulitple backends worked.
> >
> >Why? Why can't you insert using multiple backends? I mean is there a
> > problem?

How does database gets corrupted? If you have 20 backend inserting their own
chunk of data in separate transactions, where does database corruption comes
in picture?

That's the whole idea of transactions.

 Shridhar


Re: Good way to insert/update when you're not sure of duplicates?

From
Dennis Gearon
Date:
Well, I may have my merms tixed up!

There is ONE component of Postgres that can only be accessing the
database by itself. Maybe it's not the 'backend', it's probably the
postmaster. Are they the same? I don't know.

Shridhar Daithankar wrote:

>On Friday 26 September 2003 21:08, Dennis Gearon wrote:
>
>
>>corruption of the databases, if I'm correct.
>>
>>
>
>
>
>>Shridhar Daithankar wrote:
>>
>>
>>>On Friday 26 September 2003 20:48, Dennis Gearon wrote:
>>>
>>>
>>>>I didn't think mulitple backends worked.
>>>>
>>>>
>>>Why? Why can't you insert using multiple backends? I mean is there a
>>>problem?
>>>
>>>
>
>How does database gets corrupted? If you have 20 backend inserting their own
>chunk of data in separate transactions, where does database corruption comes
>in picture?
>
>That's the whole idea of transactions.
>
> Shridhar
>
>
>
>


Re: Good way to insert/update when you're not sure of

From
"scott.marlowe"
Date:
On Fri, 26 Sep 2003, Shridhar Daithankar wrote:

> On Friday 26 September 2003 21:08, Dennis Gearon wrote:
> > corruption of the databases, if I'm correct.
>
> > Shridhar Daithankar wrote:
> > >On Friday 26 September 2003 20:48, Dennis Gearon wrote:
> > >>I didn't think mulitple backends worked.
> > >
> > >Why? Why can't you insert using multiple backends? I mean is there a
> > > problem?
>
> How does database gets corrupted? If you have 20 backend inserting their own
> chunk of data in separate transactions, where does database corruption comes
> in picture?
>
> That's the whole idea of transactions.

I think Dennis is confusing multiple backends (legal and safe, each
connection gets its own backend) with multiple postmasters (not legal,
very unsafe, causes corruption, etc...)