Re: [PERFORM] How to speed-up inserts with jdbc
On Wed, 10 Nov 2004 14:51:57 +0100, Michael Kleiser [EMAIL PROTECTED] 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; i100; ++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.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] How to speed-up inserts with jdbc
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/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] How to speed-up inserts with jdbc
On Thu, 11 Nov 2004 11:04:18 +0100, Steinar H. Gunderson [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] How to speed-up inserts with jdbc
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; i100; ++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; i10; ++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 [EMAIL PROTECTED]
Re: [PERFORM] How to speed-up inserts with jdbc
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 [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] How to speed-up inserts with jdbc
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; i100; ++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; i10; ++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 [EMAIL PROTECTED]
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] How to speed-up inserts with jdbc
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Michael Kleiser
Sent: Wednesday, November 10, 2004 2:52 PM
To: Jeff
Cc: Shane|SkinnyCorp; [EMAIL PROTECTED]
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'));
[...]
---(end of broadcast)---
TIP 8: explain analyze is your friend
