Re: executeQuery and busy waiting

Garrick,

You are correct, it should run in the same time over the jdbc
connection.
Obviously there is a problem somewhere, but given that something like

select nextval(...) or select now() both call functions and return
quickly.

I am at a loss to explain the behaviour.

If I can't see it on my machine, how can I fix it?


Dave

On Fri, 2003-06-27 at 15:10, Garrick Dasbach wrote:
> Unfortunately, I can't release the source for the function due to IP
> concerns.  However considering the function runs in about 40 seconds
> through the psql terminal, should it matter what the function does since
> it should run in the same amount of time through JDBC?
>
> Garrick
>
>
> On Fri, 2003-06-27 at 14:02, Dave Cramer wrote:
> > Garrick,
> >
> > Given that nextval('sequence') is a function and I do those all the
> > time, I would probably need to see the function.
> >
> > Dave
> > On Fri, 2003-06-27 at 14:58, Garrick Dasbach wrote:
> > > It's about as simple a JDBC program as you can get.  Source included
> > > below, minus company info ofcourse.
> > >
> > >     public static void main(String[] args) {
> > >         Connection conn = null;
> > >         Statement stmt = null;
> > >         ResultSet rs = null;
> > >         try{
> > >             Class.forName("org.postgresql.Driver");
> > >             conn = DriverManager.getConnection("jdbc:postgresql://server_ip/db_name", "user", "password");
> > >             stmt = conn.createStatement();
> > >             rs = stmt.executeQuery("select myFunction(2)");
> > >             if(rs.next()){
> > >                 System.out.println("Function Execution Successfull.");
> > >             }
> > >         } catch (Exception e){
> > >             e.printStackTrace();
> > >         } finally {
> > >             try{    rs.close();    } catch (Exception e){}
> > >             try{    stmt.close();    } catch (Exception e){}
> > >             try{    conn.close();    } catch (Exception e){}
> > >         }
> > >     }
> > >
> > > Garick Dasbach
> > >
> > >
> > > On Fri, 2003-06-27 at 13:53, Dave Cramer wrote:
> > > > Garrick,
> > > >
> > > > It would help if you posted a small program to replicate the problem.
> > > >
> > > > Dave
> > > > On Fri, 2003-06-27 at 14:44, Garrick Dasbach wrote:
> > > > > I'm working on a project with Postgresql and I'm running into a strange
> > > > > problem.
> > > > >
> > > > > I have a Java Program running on the Database server that periodicly
> > > > > connects to the Database and runs a pl/pgsql function.  This function
> > > > > should run fairly fast, but could take several minutes based on the load
> > > > > of the server and amount of information it needs to process.
> > > > >
> > > > > Running the function from psql takes 40 seconds under no load and
> > > > > minimal data, but when I run the function from java using JDBC it takes
> > > > > 20-30 minutes.
> > > > >
> > > > > Checking top, this is a Linux system, I see that the java program takes
> > > > > up 99% of the CPU when it's running this function through executeQuery.
> > > > > Is executeQuery() doing a busy wait for the data from postgres?  It
> > > > > seems a bit absurd that the executeQuery method would hijack 99% of the
> > > > > CPU waiting for results and slowing everything else on the system down.
> > > > >
> > > > > The second problem I am noticing is that if I move the java program to
> > > > > another machine, to keep java from stealing all the CPU cycles, the
> > > > > function still takes 20-30 minutes to run through java, but only takes
> > > > > 40 seconds to run through psql.  What's the deal?
> > > > >
> > > > > Any help would be appreciated.
> > > > >
> > > > > Garrick Dasbach
> > > > >
> > > > > Software Developer
> > > > > MusicRebellion.com, Inc.
> > > > > Garrick@musicrebellion.com
> > > > >
> > > > >
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 9: the planner will ignore your desire to choose an index scan if your
> > > > >       joining column's datatypes do not match
> > > > >
> > > > --
> > > > Dave Cramer <Dave@micro-automation.net>
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> > >
> > >
> > --
> > Dave Cramer <Dave@micro-automation.net>
> >
>
>
--
Dave Cramer <Dave@micro-automation.net>