How to speed-up inserts with jdbc
От | Michael Kleiser |
---|---|
Тема | How to speed-up inserts with jdbc |
Дата | |
Msg-id | 41921CFD.7060604@webde-ag.de обсуждение исходный текст |
Ответ на | Re: Need advice on postgresql.conf settings (Jeff <threshar@torgo.978.org>) |
Ответы |
Re: How to speed-up inserts with jdbc
Re: How to speed-up inserts with jdbc Re: How to speed-up inserts with jdbc |
Список | 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
В списке pgsql-performance по дате отправления: