Re: Insert performance (OT?) - Mailing list pgsql-performance

I will use 2 queries.  They run within a function fnUpload(), so I'm
going to keep it simple.



On 19 Jul 2005, at 12:51, Richard Huxton wrote:


<excerpt>Yves Vindevogel wrote:

 >>> So, I must use a function that will check against u1 and u2, and
then

<excerpt><excerpt><excerpt>insert if it is ok.

I know that such a function is way slower that my insert query.

</excerpt>

So - you have a table, called something like "upload" with 20,000 rows
and you'd like to know whether it is safe to insert them. Well, it's
easy enough to identify which ones are duplicates.


SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f3;

SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f4;


</excerpt>That is a good idea.  I can delete the ones that would fail
my first unique index this way, and then delete the ones that would
fail my second unique index and then upload them.

Hmm, why did I not think of that myself.

</excerpt>

I've spent a lot of time moving data from one system to another,
usually having to clean it in the process. At 9pm on a Friday, you
decide that on the next job you'll find an efficient way to do it :-)


<excerpt><excerpt>Are you saying that deleting these rows and then
inserting takes too long?


</excerpt>This goes very fast, but not with a function that checks
each record one by one.

</excerpt>

You could get away with one query if you converted them to left-joins:

INSERT INTO ...

SELECT * FROM upload LEFT JOIN ... WHERE f3 IS NULL

UNION

SELECT * FROM upload LEFT JOIN ... WHERE f4 IS NULL


The UNION will remove duplicates for you, but this might turn out to
be slower than two separate queries.


--

  Richard Huxton

  Archonet Ltd



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>I will use 2 queries.  They run within a function fnUpload(), so I'm
going to keep it simple.


On 19 Jul 2005, at 12:51, Richard Huxton wrote:

> Yves Vindevogel wrote:
>  >>> So, I must use a function that will check against u1 and u2, and
> then
>>>> insert if it is ok.
>>>> I know that such a function is way slower that my insert query.
>>>
>>> So - you have a table, called something like "upload" with 20,000
>>> rows and you'd like to know whether it is safe to insert them. Well,
>>> it's easy enough to identify which ones are duplicates.
>>>
>>> SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f3;
>>> SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f4;
>>>
>> That is a good idea.  I can delete the ones that would fail my first
>> unique index this way, and then delete the ones that would fail my
>> second unique index and then upload them.
>> Hmm, why did I not think of that myself.
>
> I've spent a lot of time moving data from one system to another,
> usually having to clean it in the process. At 9pm on a Friday, you
> decide that on the next job you'll find an efficient way to do it :-)
>
>>> Are you saying that deleting these rows and then inserting takes too
>>> long?
>>>
>> This goes very fast, but not with a function that checks each record
>> one by one.
>
> You could get away with one query if you converted them to left-joins:
> INSERT INTO ...
> SELECT * FROM upload LEFT JOIN ... WHERE f3 IS NULL
> UNION
> SELECT * FROM upload LEFT JOIN ... WHERE f4 IS NULL
>
> The UNION will remove duplicates for you, but this might turn out to
> be slower than two separate queries.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

<smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.
Mahatma Ghandi.