Thread: postgres 8 performance

Hi all!
I am running postgres 8 beta1 and for some reason it is really slow in
execution. I am not able to figure out why.
On a fresh install of postgres, the following code executed through jdbc


********************
for (int i=0; i < 10000; i++) {
                  s.execute("insert into tab values (" + i + ",'" + i +
"')") ;
}
****************


This took like 700 secs to execute.
However, the weird part is that a stored proc doing the same thing via


******************************
create or replace function perftest() returns integer as '
declare
      i integer := 0 ;
begin
      for i in 0..10000 loop
            execute ''insert into tab values ( '' || i || '' ,''''xxx''''
)'' ;
      end loop ;
      return  0;
end ;

does the same thing in less than a second.

*************************************


I can't figure out what the problem is.
The app using jdbc and the postmaster instance were running on the same
comp so I can't believe that communication would make that big a hit on
performance.

Is there something else that I am doing wrong?
Finally, where can I look up some useful performance tuning tips to get my
server to run faster anyway?

thanks a lot
paramveer singh

    Add a begin/commit on that so you dont have 10k transactions ?

BEGIN
> ********************
> for (int i=0; i < 10000; i++) {
>                   s.execute("insert into tab values (" + i + ",'" + i +
> "')") ;
> }
> ****************
COMMIT

> I can't figure out what the problem is.
> The app using jdbc and the postmaster instance were running on the same
> comp so I can't believe that communication would make that big a hit on
> performance.

It's the query parsing that takes a lot of time.
You might want to look at prepared statements:
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Also set autocommit to off and do a hundert or so inserts inside
one transaction, not one transaction for each insert.

Bye, Chris.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,
On Tue, 7 Sep 2004 Paramveer.Singh@trilogy.com wrote:

<snip>
> Finally, where can I look up some useful performance tuning tips to get my
> server to run faster anyway?

* http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
* http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
* Join pgsql-perform list.

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org                devrim.gunduz~linux.org.tr
             http://www.tdmsoft.com
             http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBPY4ctl86P3SPfQ4RAuSaAJ0daOhwnIUVjwR1qmQu9GBeFTCB5wCfZdN/
xlW//4jr8EOIhZil1qyGYSY=
=QlCi
-----END PGP SIGNATURE-----

On 07/09/2004 11:12 Paramveer.Singh@trilogy.com wrote:
> Hi all!
> I am running postgres 8 beta1 and for some reason it is really slow in
> execution. I am not able to figure out why.
> On a fresh install of postgres, the following code executed through jdbc
>
> [snip]
> I can't figure out what the problem is.
> The app using jdbc and the postmaster instance were running on the same
> comp so I can't believe that communication would make that big a hit on
> performance.

Each of your JDBC inserts is being performed in a different transaction
whilst your stored procedure is being executed is a single transaction. If
you want to speed up the JDBC inserts, use setAutoCommit(false) on the
connection object then do a connection.commit() at the end. If you really
are taking 700 seconds, you've probably also got a network misconfiguration
somewhere as well. 50-100 seconds would be more realistic even on fairly
slow hardware.

--
Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business           |
| Computer Consultants         | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+