Skip site navigation (1) Skip section navigation (2)

Function with raise notice statements taking too long

From: Altaf Malik <mmalik_altaf(at)yahoo(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Function with raise notice statements taking too long
Date: 2010-05-26 08:50:16
Message-ID: 34816.46946.qm@web110412.mail.gq1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi,
 I have a plpgsql function with raise notice statements in a loop. When I call this function from JDBC, it takes almost 5 minutes to execute. However, if I invoke it through pgAdminIII, it gets executed in just 12 seconds. The code of the function is given below:

CREATE OR REPLACE FUNCTION f1() RETURNS void AS
$BODY$ declare 
    i integer := 0;
    start_time timestamp;
    end_time timestamp;
 begin
    start_time := now();
    LOOP
        exit when i = 100000;
        RAISE NOTICE 'Program is on the line number: %',i ;
        i := i + 1;
   END LOOP;

    end_time := now();
    
    RAISE NOTICE 'Program start time: %', start_time;
    RAISE NOTICE 'Program End time: %', end_time;

 end; $BODY$
  LANGUAGE 'plpgsql'

I invoke it from pgAdminIII using: select f1()

I used the following java program to execute it and it takes too long.

  Class.forName("org.postgresql.Driver");
  Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres","postgres","postgres");
  System.out.println("Starting ...");

   CallableStatement stmt = con.prepareCall("{call f1()}");
   stmt.execute();

        SQLWarning warn = stmt.getWarnings();
        while (warn != null) {
            System.out.println(warn.getMessage());
            warn = warn.getNextWarning();
        }
        System.out.println("Done");

Any clue why JDBC is performing too slow?

I am using: 

Databse: "PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 32-bit"
Driver: 8.5devel

Regards,
Altaf Malik



      

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2010-05-26 19:21:33
Subject: Re: Function with raise notice statements taking too long
Previous:From: Kris JurkaDate: 2010-05-24 18:25:51
Subject: Re: Using java.lang.Character for "char" data type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group