pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to speed-up inserts with jdbc
7+ messages / 6 participants
[nested] [flat]

* How to speed-up inserts with jdbc
@ 2004-11-10 13:51 Michael Kleiser <mkl@webde-ag.de>
  2004-11-10 13:55 ` Re: How to speed-up inserts with jdbc Jeff <threshar@torgo.978.org>
  2004-11-10 14:05 ` Re: How to speed-up inserts with jdbc Dave Cramer <pg@fastcrypt.com>
  2004-11-11 08:04 ` Re: How to speed-up inserts with jdbc Edwin Eyan Moragas <haaktu@gmail.com>
  0 siblings, 3 replies; 7+ messages in thread

From: Michael Kleiser @ 2004-11-10 13:51 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: Shane|SkinnyCorp <shanew@skinnycorp.com>; pgsql-performance

Im PostgreSQL 7.2.2 / Linux 2.4.27  dual-processor Pentium III 900MHz,

we have this table:

create table testtable (id SERIAL PRIMARY KEY, coni VARCHAR(255), date TIMESTAMP, direction VARCHAR(255), partner VARCHAR(255), type VARCHAR(255), block VARCHAR(255) );


We using Java with JDBC-driver pg72jdbc2.jar


our Java-testgrogram is :


public class Stresser implements Runnable {
     public void run() {
         System.out.println("-> start");
         try {
	
	        Class.forName("org.postgresql.Driver");
	        Connection con = DriverManager.getConnection("jdbc:postgresql://"+prop.getProperty("host")+":"+prop.getProperty("port")+"/"+prop.getProperty("dbname"), prop.getProperty("user"), 
prop.getProperty("pwd"));
	        con.setAutoCommit(true);
	        Statement  st = con.createStatement();
	        java.sql.Timestamp datum = new java.sql.Timestamp(new Date().getTime());
	        Date start = new Date();
	        System.out.println(start);
	        for (int i=0; i<100; ++i) {
	            st.executeUpdate("insert into history(uuid,coni,date,direction,partner,type) values('uuid','content','"+datum+"','dir','partner','type')");
	        }
	        Date end = new Date();
	        System.out.println(end);
	        con.close();
         } catch (Exception e) {
             System.out.println("Exception!");
             e.printStackTrace();
         }
         System.out.println("-> ende");
     }

     public static void main(String[] args) {

         for (int i=0; i<10; ++i) {
             Stresser s = new Stresser();
             Thread t = new Thread(s);
             t.start();
         }
     }
}


It is trunning in in 10 Threads. Each thread makes 100 Inserts:

For the 1000 Inserts (10 threads a 100 inserts)
we need 8 seconds.
That's 125 Insets / Seconds.

How could we make it faster ?

Inserting 1000 rows via  INSERT AS SELECT is much faster.

regards
   Michael



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to speed-up inserts with jdbc
  2004-11-10 13:51 How to speed-up inserts with jdbc Michael Kleiser <mkl@webde-ag.de>
@ 2004-11-10 13:55 ` Jeff <threshar@torgo.978.org>
  2 siblings, 0 replies; 7+ messages in thread

From: Jeff @ 2004-11-10 13:55 UTC (permalink / raw)
  To: mkl@webde-ag.de; +Cc: Shane|SkinnyCorp <shanew@skinnycorp.com>; pgsql-performance


On Nov 10, 2004, at 8:51 AM, Michael Kleiser wrote:
> It is trunning in in 10 Threads. Each thread makes 100 Inserts:
>
> For the 1000 Inserts (10 threads a 100 inserts)
> we need 8 seconds.
> That's 125 Insets / Seconds.
> How could we make it faster ?
>

Batch the inserts up into a transaction.

So you'd have
BEGIN
insert
insert
insert
...
COMMIT

Your numbers will suddenly sky rocket.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to speed-up inserts with jdbc
  2004-11-10 13:51 How to speed-up inserts with jdbc Michael Kleiser <mkl@webde-ag.de>
@ 2004-11-10 14:05 ` Dave Cramer <pg@fastcrypt.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Dave Cramer @ 2004-11-10 14:05 UTC (permalink / raw)
  To: mkl@webde-ag.de; +Cc: Jeff <threshar@torgo.978.org>; Shane|SkinnyCorp <shanew@skinnycorp.com>; pgsql-performance

couple of things

1) That is a fairly old version of postgres, there are considerable 
performance improvements in the last 2 releases since, and even more in 
the pending release.
2) If you are going to insert more rows than that, consider dropping the 
index before, and recreating after the insert.

Dave

Michael Kleiser wrote:

> Im PostgreSQL 7.2.2 / Linux 2.4.27  dual-processor Pentium III 900MHz,
>
> we have this table:
>
> create table testtable (id SERIAL PRIMARY KEY, coni VARCHAR(255), date 
> TIMESTAMP, direction VARCHAR(255), partner VARCHAR(255), type 
> VARCHAR(255), block VARCHAR(255) );
>
>
> We using Java with JDBC-driver pg72jdbc2.jar
>
>
> our Java-testgrogram is :
>
>
> public class Stresser implements Runnable {
>     public void run() {
>         System.out.println("-> start");
>         try {
>     
>             Class.forName("org.postgresql.Driver");
>             Connection con = 
> DriverManager.getConnection("jdbc:postgresql://"+prop.getProperty("host")+":"+prop.getProperty("port")+"/"+prop.getProperty("dbname"), 
> prop.getProperty("user"), prop.getProperty("pwd"));
>             con.setAutoCommit(true);
>             Statement  st = con.createStatement();
>             java.sql.Timestamp datum = new java.sql.Timestamp(new 
> Date().getTime());
>             Date start = new Date();
>             System.out.println(start);
>             for (int i=0; i<100; ++i) {
>                 st.executeUpdate("insert into 
> history(uuid,coni,date,direction,partner,type) 
> values('uuid','content','"+datum+"','dir','partner','type')");
>             }
>             Date end = new Date();
>             System.out.println(end);
>             con.close();
>         } catch (Exception e) {
>             System.out.println("Exception!");
>             e.printStackTrace();
>         }
>         System.out.println("-> ende");
>     }
>
>     public static void main(String[] args) {
>
>         for (int i=0; i<10; ++i) {
>             Stresser s = new Stresser();
>             Thread t = new Thread(s);
>             t.start();
>         }
>     }
> }
>
>
> It is trunning in in 10 Threads. Each thread makes 100 Inserts:
>
> For the 1000 Inserts (10 threads a 100 inserts)
> we need 8 seconds.
> That's 125 Insets / Seconds.
>
> How could we make it faster ?
>
> Inserting 1000 rows via  INSERT AS SELECT is much faster.
>
> regards
>   Michael
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to speed-up inserts with jdbc
  2004-11-10 13:51 How to speed-up inserts with jdbc Michael Kleiser <mkl@webde-ag.de>
@ 2004-11-11 08:04 ` Edwin Eyan Moragas <haaktu@gmail.com>
  2004-11-11 10:04   ` Re: How to speed-up inserts with jdbc Steinar H. Gunderson <sgunderson@bigfoot.com>
  2 siblings, 1 reply; 7+ messages in thread

From: Edwin Eyan Moragas @ 2004-11-11 08:04 UTC (permalink / raw)
  To: mkl@webde-ag.de; +Cc: Jeff <threshar@torgo.978.org>; Shane|SkinnyCorp <shanew@skinnycorp.com>; pgsql-performance

On Wed, 10 Nov 2004 14:51:57 +0100, Michael Kleiser <mkl@webde-ag.de> wrote:
>                Statement  st = con.createStatement();
>                java.sql.Timestamp datum = new java.sql.Timestamp(new Date().getTime());
>                Date start = new Date();
>                System.out.println(start);
>                for (int i=0; i<100; ++i) {
>                    st.executeUpdate("insert into history(uuid,coni,date,direction,partner,type) values('uuid','content','"+datum+"','dir','partner','type')");
>                }

how about using PreparedStatment? that's on the java end.
on the pg end, maybe do a BEGIN before the for loop and 
END at the end of the for loop.
-- 
i'm not flying. i'm falling... in style.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to speed-up inserts with jdbc
  2004-11-10 13:51 How to speed-up inserts with jdbc Michael Kleiser <mkl@webde-ag.de>
  2004-11-11 08:04 ` Re: How to speed-up inserts with jdbc Edwin Eyan Moragas <haaktu@gmail.com>
@ 2004-11-11 10:04   ` Steinar H. Gunderson <sgunderson@bigfoot.com>
  2004-11-11 21:10     ` Re: How to speed-up inserts with jdbc Edwin Eyan Moragas <haaktu@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Steinar H. Gunderson @ 2004-11-11 10:04 UTC (permalink / raw)
  To: pgsql-performance

On Thu, Nov 11, 2004 at 04:04:06PM +0800, Edwin Eyan Moragas wrote:
> how about using PreparedStatment? that's on the java end.
> on the pg end, maybe do a BEGIN before the for loop and 
> END at the end of the for loop.

You don't even need a "BEGIN" and "END"; his code has a setAutoComit(true)
before the for loop, which just has to be changed to setAutoCommit(false)
(and add an explicit commit() after the for loop, of course).

/* Steinar */
-- 
Homepage: http://www.sesse.net/



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to speed-up inserts with jdbc
  2004-11-10 13:51 How to speed-up inserts with jdbc Michael Kleiser <mkl@webde-ag.de>
  2004-11-11 08:04 ` Re: How to speed-up inserts with jdbc Edwin Eyan Moragas <haaktu@gmail.com>
  2004-11-11 10:04   ` Re: How to speed-up inserts with jdbc Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2004-11-11 21:10     ` Edwin Eyan Moragas <haaktu@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Edwin Eyan Moragas @ 2004-11-11 21:10 UTC (permalink / raw)
  To: pgsql-performance

On Thu, 11 Nov 2004 11:04:18 +0100, Steinar H. Gunderson
<sgunderson@bigfoot.com> wrote:
> You don't even need a "BEGIN" and "END"; his code has a setAutoComit(true)
> before the for loop, which just has to be changed to setAutoCommit(false)
> (and add an explicit commit() after the for loop, of course).

amen. i stand corrected.

-eem



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to speed-up inserts with jdbc
@ 2004-11-10 14:26 Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Leeuw van der, Tim @ 2004-11-10 14:26 UTC (permalink / raw)
  To: mkl@webde-ag.de; Jeff <threshar@torgo.978.org>; +Cc: Shane|SkinnyCorp <shanew@skinnycorp.com>; pgsql-performance

Hi,

Try using parametrized prepared statements, does that make a difference? Or does PGSQL jdbc not support them in your version?

--Tim


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Michael Kleiser
Sent: Wednesday, November 10, 2004 2:52 PM
To: Jeff
Cc: Shane|SkinnyCorp; pgsql-performance@postgresql.org
Subject: [PERFORM] How to speed-up inserts with jdbc


[...]
>	        Statement  st = con.createStatement();
[...]
	            st.executeUpdate("insert into history(uuid,coni,date,direction,partner,type) values('uuid','content','"+datum+"','dir','partner','type')");
[...]



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-11-11 21:10 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-10 13:51 How to speed-up inserts with jdbc Michael Kleiser <mkl@webde-ag.de>
2004-11-10 13:55 ` Jeff <threshar@torgo.978.org>
2004-11-10 14:05 ` Dave Cramer <pg@fastcrypt.com>
2004-11-11 08:04 ` Edwin Eyan Moragas <haaktu@gmail.com>
2004-11-11 10:04   ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-11-11 21:10     ` Edwin Eyan Moragas <haaktu@gmail.com>
2004-11-10 14:26 Re: How to speed-up inserts with jdbc Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox