Thread: 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 > 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.
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: > 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. )-: >