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(at)musicrebellion(dot)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(at)micro-automation(dot)net>
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> > >
> > >
> > --
> > Dave Cramer <Dave(at)micro-automation(dot)net>
> >
>
>
--
Dave Cramer <Dave(at)micro-automation(dot)net>