Re: executeQuery and busy waiting

Might Garrick come up with a test case that doesn't involve the code he's 
not allowed to show?  it may just be something as simple as a dummy 
stored proc would show this behaviour on his machine.

On 27 Jun 2003, Dave Cramer wrote:

> 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 
> > > > > 
> > > > > 
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> > > > 
> > > > 
> > > -- 
> > > Dave Cramer 
> > > 
> > 
> > 
>