Thread: Performance Problem

Performance Problem

From
Adam Sherman
Date:
I am trying to isolate a performance problem I'm having.

I have some code that does something like this, iterating through a list:


BEGIN
while(list)
lookup for existing entry (SELECT)
if not, INSERT
if yes, UPDATE
log a message
COMMIT

With approx. 30K items the process takes way too long. On the order of 10+
minutes.

I notice that the process seems to slow down as it completes more and more
items. ie, the first 10K takes just a few moments while the last 10K takes
most of the time. Note that I'm running test where the only existing
entries will have been inserted in the same transaction.

I need to know if the bottleneck is PG or higher up so I can go hunting
there.

Thanks,

A.

--
Adam Sherman
Tritus CG Inc.
http://www.tritus.ca/
+1 (613) 797-6819


Re: Performance Problem

From
Bruno Wolff III
Date:
On Mon, May 12, 2003 at 09:57:12 -0400,
  Adam Sherman <adam@tritus.ca> wrote:
>
> BEGIN
> while(list)
> lookup for existing entry (SELECT)
> if not, INSERT
> if yes, UPDATE
> log a message
> COMMIT
>
> With approx. 30K items the process takes way too long. On the order of 10+
> minutes.
>
> I notice that the process seems to slow down as it completes more and more
> items. ie, the first 10K takes just a few moments while the last 10K takes
> most of the time. Note that I'm running test where the only existing
> entries will have been inserted in the same transaction.
>
> I need to know if the bottleneck is PG or higher up so I can go hunting
> there.

Are there deferred constraints? They are still order n squared. Some
fixes went in to 7.3 but the last order n squared issue wasn't fixed
until 7.4. There was some discussion of back porting the fix to 7.3.3
(which might be available in about a month) but I am not sure whether
or that is going to happen.


Re: Performance Problem

From
Adam Sherman
Date:
On 05/12/03 10:42:43 -0500 Bruno Wolff III wrote:

> Are there deferred constraints? They are still order n squared. Some
> fixes went in to 7.3 but the last order n squared issue wasn't fixed
> until 7.4. There was some discussion of back porting the fix to 7.3.3
> (which might be available in about a month) but I am not sure whether
> or that is going to happen.

Ah, over my head now. I don't think any of the constraints are deferred...

Thanks,

A.

--
Adam Sherman
Tritus CG Inc.
http://www.tritus.ca/
+1 (613) 797-6819


Re: Performance Problem

From
Joseph Shraibman
Date:
Are you using jdbc by any chance?

--
Joseph Shraibman
joseph@xtenit.com
Increase signal to noise ratio.  http://xis.xtenit.com


Re: Performance Problem

From
Adam Sherman
Date:
On 05/12/03 19:18:13 -0400 Joseph Shraibman wrote:

> Are you using jdbc by any chance?

Uh oh. Yes, I am using JDBC. )-:

A.

--
Adam Sherman
Tritus CG Inc.
http://www.tritus.ca/
+1 (613) 797-6819


Re: Performance Problem

From
Joseph Shraibman
Date:
I was having a similar probelm.  Turns out there were a bunch of warnings that kept being
added to the Connection.  Try explicitly clearing the connection warnings.  The jdbc
drivers in the cvs add the warnings to the Statement, not the Connecton.

Adam Sherman wrote:
> On 05/12/03 19:18:13 -0400 Joseph Shraibman wrote:
>
>> Are you using jdbc by any chance?
>
>
> Uh oh. Yes, I am using JDBC. )-:
>