Обсуждение: Function with raise notice statements taking too long
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
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
On Wed, 26 May 2010, Altaf Malik wrote: > 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: > > [plpgsql function issuing a ton of RAISE NOTICE statements] > > Any clue why JDBC is performing too slow? > I've committed a fix to CVS to fix this performance issue. We were only tracking the head of the SQLWarning list so adding a new warning was O(N). Now we track the tail of the list as well, so adding new warnings is quick. Kris Jurka