By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,490 Members | 3,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,490 IT Pros & Developers. It's quick & easy.

postgres 8 performance

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

P: n/a
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/tutor.../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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
On Tue, 7 Sep 2004 Pa*************@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/Gener...ed_conf_e.html
* http://www.varlena.com/varlena/Gener...bits/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)

iD8DBQFBPY4ctl86P3SPfQ4RAuSaAJ0daOhwnIUVjwR1qmQu9G BeFTCB5wCfZdN/
xlW//4jr8EOIhZil1qyGYSY=
=QlCi
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4

P: n/a

On 07/09/2004 11:12 Pa*************@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 |
+------------------------------+-------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.