Performance Problem
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
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
COMMITWith 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.
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
Are you using jdbc by any chance?
--
Joseph Shraibman
joseph@xtenit.com
Increase signal to noise ratio. http://xis.xtenit.com
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
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:
Show quoted text
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. )-: