Обсуждение: JAVA vs PERL : PERL wins to postgreSQL
Hi,
I ran a few bench marks on JAVA writing to a postgreSQL table using and
found that for the same number of records added to the table as a
similar PERL routine the following results :
PERL 39 seconds : JAVA 45 Seconds.
In a similar experiment where PERL and JAVA did treir output to the
screen and not to a table,
JAVA took 3 seconds and PERL 310 Seconds.
My conclusion is that the database driver to postgreSQL is still far
from efficient in the JAVA implementation.
Both tests were run on the same computer.
I would appreciate your comments and suggestions.
Andy Sewell
> I ran a few bench marks on JAVA writing to a postgreSQL table > using and > found that for the same number of records added to the table as a > similar PERL routine the following results : > PERL 39 seconds : JAVA 45 Seconds. > In a similar experiment where PERL and JAVA did treir output to the > screen and not to a table, > > JAVA took 3 seconds and PERL 310 Seconds. Was that 310 milliseconds in Perl? > My conclusion is that the database driver to postgreSQL is still far > from efficient in the JAVA implementation. One thing that may affect the Java performance when written out to the screen is the overhead of using System.out.println() calls as these are a call to native code and synchronized too for multithreaded output. Maybe if we can pump the test through a profiler and work out where the bulk of the Java operations are occurring for test 1. Regards, Joe
On Mon, 03 Sep 2001 07:47:29 +0200, you wrote: >I ran a few bench marks on JAVA writing to a postgreSQL table using and >found that for the same number of records added to the table as a >similar PERL routine the following results : > PERL 39 seconds : JAVA 45 Seconds. >In a similar experiment where PERL and JAVA did treir output to the >screen and not to a table, Can you tell us some more about the algorithm? Is it one process with one connection, or are you restarting the Java application many times? What are the queries it executes? Is autocommit on or off? What JDK/JRE did you use? I've heard that the IBM JDK performs well. It would be interesting to give it a try. Its a free download on http://www-106.ibm.com/developerworks/java/ Regards, René Pijlman <rene@lab.applinet.nl>
On Mon, 3 Sep 2001, andy wrote: > Hi, > > I ran a few bench marks on JAVA writing to a postgreSQL table using and > found that for the same number of records added to the table as a > similar PERL routine the following results : > PERL 39 seconds : JAVA 45 Seconds. How did you start the tests? For Java to really show it's strengh it usually requires a few warm-up runs, so that the JIT or Hotspot optimizers get started. But I still wouldn't be surprised if perl was faster. I'm sure it's easier to write a DBI/DBD driver than a JDBC driver. /Anders _____________________________________________________________________ A n d e r s B e n g t s s o n ndrsbngtssn@yahoo.se Stockholm, Sweden _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
i have several questions about the benchmark run that all come down to one basic question - was the benchmark fair? 1) were you trying to test the performance of the languages or were you trying to test the performance of the drivers? if you are trying to test _languages_ then what you should do is to use a common driver. if you are coding on a windoze platform you can set up the odbc driver and have both perl and java connect to the db through the same driver. i don't know how 'fair' that test is though because java will still have the overhead of jdbc:odbc bridge. 2) assuming that you are trying to test differences in drivers, what did the tests consist of? were the insertions done in the context of a transaction or outside of one? a test that i ran that inserted 250+ records in one table and then 4500 in another test through perl and odbc ran at about 1:25 out of a transaction and 0:30 inside of a transaction. there's a big difference. 3) did both of the drivers used have the same settings? did you make sure that the autocommit states were identical? 4) did you take into account ONLY the amount of time needed to insert the data or were the times listed for assembling, inserting, and reporting results? 5) did you do a "warm up" session so that the code was in the same state as if it had been running on the server or were both run from cold starts? would that make a difference in terms of loading up all the drivers, etc.? 6) did the benchmark only use one connection for the entire test or were multiple connections used? if multiple, did you optimize your code like you would for a production setting where you would be using connection pooling? rjsjr > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of andy > Sent: Monday, September 03, 2001 12:47 AM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] JAVA vs PERL : PERL wins to postgreSQL > > > Hi, > > I ran a few bench marks on JAVA writing to a postgreSQL > table using and > found that for the same number of records added to the table as a > similar PERL routine the following results : > PERL 39 seconds : JAVA 45 Seconds. > In a similar experiment where PERL and JAVA did treir output to the > screen and not to a table, > > JAVA took 3 seconds and PERL 310 Seconds. > My conclusion is that the database driver to postgreSQL is still far > from efficient in the JAVA implementation. > > Both tests were run on the same computer. > > I would appreciate your comments and suggestions. > Andy Sewell > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Andy, I would be interesting in knowing what version you did this test on, what platform, and most importantly which JDK (and if the Sun JDK which JVM: classic, hotspot client, hotspot server). thanks, --Barry andy wrote: > Hi, > > I ran a few bench marks on JAVA writing to a postgreSQL table using and > found that for the same number of records added to the table as a > similar PERL routine the following results : > PERL 39 seconds : JAVA 45 Seconds. > In a similar experiment where PERL and JAVA did treir output to the > screen and not to a table, > > JAVA took 3 seconds and PERL 310 Seconds. > My conclusion is that the database driver to postgreSQL is still far > from efficient in the JAVA implementation. > > Both tests were run on the same computer. > > I would appreciate your comments and suggestions. > Andy Sewell > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
To all those who responded , Thanks for your response, I have attached the test programs that I used in the form of text files. The contents should be self evident. Please have a look and comment. I used j2sdk1.3.1 down load to linux machine from sun.com as my java platform and And perl, v5.6.0 built for i386-linux. The PostgreSQL driver for perl came from www.perl.org The postgreSQL driver in jdbc7.0-1.2.jar Barry Lind wrote: > Andy, > > I would be interesting in knowing what version you did this test on, > what platform, and most importantly which JDK (and if the Sun JDK which > JVM: classic, hotspot client, hotspot server). > > thanks, > --Barry > > andy wrote: > > Hi, > > > > I ran a few bench marks on JAVA writing to a postgreSQL table using and > > found that for the same number of records added to the table as a > > similar PERL routine the following results : > > PERL 39 seconds : JAVA 45 Seconds. > > In a similar experiment where PERL and JAVA did treir output to the > > screen and not to a table, > > > > JAVA took 3 seconds and PERL 310 Seconds. > > My conclusion is that the database driver to postgreSQL is still far > > from efficient in the JAVA implementation. > > > > Both tests were run on the same computer. > > > > I would appreciate your comments and suggestions. > > Andy Sewell > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > /* Java bench mark against perl count to 100 000 000 */ import java.io.*; import java.util.*; public class lp{ public lp() throws ClassNotFoundException, FileNotFoundException, IOException { int last = 1000; GregorianCalendar day = new GregorianCalendar(); String time = day.getTime().toString(); int k=0; for (int i=0; i<last ; i++) { for (int p=0; p<last ; p++) {k=1+p;System.out.println(k);} } GregorianCalendar day1 = new GregorianCalendar(); String time2 = day1.getTime().toString(); System.out.println(time); System.out.println(time2 + "\n" + k); } public static void main(String[] args) { try { lp test = new lp(); } catch (Exception ex) { System.err.println("Exception :" + ex); ex.printStackTrace(); } } } /* Java equivalent */ import java.io.*; import java.sql.*; import java.util.*; public class pg{ Connection conn; Statement stmt; public pg() throws ClassNotFoundException, FileNotFoundException, IOException, SQLException { Class.forName("org.postgresql.Driver"); conn = DriverManager.getConnection("jdbc:postgresql:exkom", "andy", ""); conn.setAutoCommit(true); stmt = conn.createStatement(); int last = 10000; int res = stmt.executeUpdate("delete from junk"); GregorianCalendar day = new GregorianCalendar(); String time = day.getTime().toString(); for (int i=0; i<last ; i++) { // try { res = stmt.executeUpdate("insert into junk values(" + i + ")"); // } catch (SQLException e) { // System.out.println(e); // } } GregorianCalendar day1 = new GregorianCalendar(); String time2 = day1.getTime().toString(); // String time3 = day.getTime().toString(); System.out.println(time); System.out.println(time2); // System.out.println(time3); // res.close(); stmt.close(); conn.close(); } public static void main(String[] args) { try { pg test = new pg(); } catch (Exception ex) { System.err.println("Exception :" + ex); ex.printStackTrace(); } } } /** $conn = Pg::connectdb("dbname=exkom"); die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status; # print "Enter a state code :"; # $state_code = <STDIN>; # chomp $state_code; $result = $conn->exec("delete from junk"); $end = 10000; $t0 = new Benchmark; for ($i=1; $i < $end; $i++) { $result = $conn->exec("insert into junk values($i)"); } $t1 = new Benchmark; $td = timediff($t1, $t0); print " the $end records took :", timestr($td) , "\n" **/
Вложения
Andy, Thanks for the code. In looking at this there are two things that come to mind: 1) You probably should be running vacuum after the delete to clean up the table, but on a table this small, I doubt it will make any real difference. 2) Why have you set autocommit on? This should be much faster with autocommit off. Actually I just did a quick test of the two changes I mentioned above. The first (vacuum) doesn't make any noticable difference, however turning autocommit off causes the timings on my machine to go from 14 seconds to 6 seconds. Better than 50% improvement. thanks, --Barry andy wrote: > To all those who responded , > Thanks for your response, > > I have attached the test programs that I used in the form of text files. The > contents should be self evident. Please have a look and comment. > > I used j2sdk1.3.1 down load to linux machine from sun.com as my java platform > and > And perl, v5.6.0 built for i386-linux. > The PostgreSQL driver for perl came from www.perl.org > The postgreSQL driver in jdbc7.0-1.2.jar > > > Barry Lind wrote: > > >>Andy, >> >>I would be interesting in knowing what version you did this test on, >>what platform, and most importantly which JDK (and if the Sun JDK which >>JVM: classic, hotspot client, hotspot server). >> >>thanks, >>--Barry >> >>andy wrote: >> >>>Hi, >>> >>>I ran a few bench marks on JAVA writing to a postgreSQL table using and >>>found that for the same number of records added to the table as a >>>similar PERL routine the following results : >>> PERL 39 seconds : JAVA 45 Seconds. >>>In a similar experiment where PERL and JAVA did treir output to the >>>screen and not to a table, >>> >>>JAVA took 3 seconds and PERL 310 Seconds. >>>My conclusion is that the database driver to postgreSQL is still far >>>from efficient in the JAVA implementation. >>> >>>Both tests were run on the same computer. >>> >>>I would appreciate your comments and suggestions. >>>Andy Sewell >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/users-lounge/docs/faq.html >>> >>> >>> >>> >>>------------------------------------------------------------------------ >>> >>>/* >>> Java bench mark against perl count to 100 000 000 >>>*/ >>>import java.io.*; >>>import java.util.*; >>> >>>public class lp{ >>> >>> public lp() throws ClassNotFoundException, FileNotFoundException, IOException >>> { >>> int last = 1000; >>> >>> GregorianCalendar day = new GregorianCalendar(); >>> String time = day.getTime().toString(); >>> int k=0; >>> for (int i=0; i<last ; i++) { >>> for (int p=0; p<last ; p++) {k=1+p;System.out.println(k);} >>> } >>> >>> GregorianCalendar day1 = new GregorianCalendar(); >>> String time2 = day1.getTime().toString(); >>> System.out.println(time); >>> System.out.println(time2 + "\n" + k); >>> >>> >>> } >>> >>>public static void main(String[] args) { >>> try { >>> lp test = new lp(); >>> } >>> catch (Exception ex) { >>> System.err.println("Exception :" + ex); >>> ex.printStackTrace(); >>> } >>>} >>>} >>> >>> >>> >>>------------------------------------------------------------------------ >>> >>>/* >>> Java equivalent >>>*/ >>>import java.io.*; >>>import java.sql.*; >>>import java.util.*; >>> >>>public class pg{ >>> Connection conn; >>> Statement stmt; >>> >>> public pg() throws ClassNotFoundException, FileNotFoundException, IOException, SQLException >>> { >>> Class.forName("org.postgresql.Driver"); >>> conn = DriverManager.getConnection("jdbc:postgresql:exkom", "andy", ""); >>> conn.setAutoCommit(true); >>> stmt = conn.createStatement(); >>> int last = 10000; >>> int res = stmt.executeUpdate("delete from junk"); >>> >>> GregorianCalendar day = new GregorianCalendar(); >>> String time = day.getTime().toString(); >>> >>> for (int i=0; i<last ; i++) { >>> // try { >>> res = stmt.executeUpdate("insert into junk values(" + i + ")"); >>> >>> // } catch (SQLException e) { >>> // System.out.println(e); >>> // } >>> } >>> >>> GregorianCalendar day1 = new GregorianCalendar(); >>> String time2 = day1.getTime().toString(); >>>// String time3 = day.getTime().toString(); >>> System.out.println(time); >>> System.out.println(time2); >>>// System.out.println(time3); >>> >>>// res.close(); >>> stmt.close(); >>> conn.close(); >>> >>> >>> } >>> >>>public static void main(String[] args) { >>> try { >>> pg test = new pg(); >>> } >>> catch (Exception ex) { >>> System.err.println("Exception :" + ex); >>> ex.printStackTrace(); >>> } >>>} >>>} >>> >>>/** >>> >>> >>>$conn = Pg::connectdb("dbname=exkom"); >>>die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status; >>> >>># print "Enter a state code :"; >>># $state_code = <STDIN>; >>> >>># chomp $state_code; >>>$result = $conn->exec("delete from junk"); >>>$end = 10000; >>> >>>$t0 = new Benchmark; >>> >>>for ($i=1; $i < $end; $i++) { >>> $result = $conn->exec("insert into junk values($i)"); >>>} >>> >>>$t1 = new Benchmark; >>>$td = timediff($t1, $t0); >>>print " the $end records took :", timestr($td) , "\n" >>>**/ >>> >>> >>>------------------------------------------------------------------------ >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 3: if posting/reading through Usenet, please send an appropriate >>>subscribe-nomail command to majordomo@postgresql.org so that your >>>message can get through to the mailing list cleanly >>> >>> lp.java >>> >>> Content-Type: >>> >>> text/plain >>> Content-Encoding: >>> >>> 7bit >>> >>> >>> ------------------------------------------------------------------------ >>> lp.pl >>> >>> Content-Type: >>> >>> application/x-perl >>> Content-Encoding: >>> >>> 7bit >>> >>> >>> ------------------------------------------------------------------------ >>> pg.java >>> >>> Content-Type: >>> >>> text/plain >>> Content-Encoding: >>> >>> 7bit >>> >>> >>> ------------------------------------------------------------------------ >>> pg.pl >>> >>> Content-Type: >>> >>> application/x-perl >>> Content-Encoding: >>> >>> 7bit >>> >>> >>> ------------------------------------------------------------------------ >>> Part 1.6 >>> >>> Content-Type: >>> >>> text/plain >>> Content-Encoding: >>> >>> binary >>> >>>
The obvious question is whether the PERL-script is also executing in one transaction. If it is not, we're "cheating" setting autocommit off in the JDBC-test app. (I did not look at the code, since I am not familiar with the PGSQL's PERL-interface anyway.) Peter > -----Original Message----- > From: Barry Lind [mailto:barry@xythos.com] > Sent: Tuesday, September 04, 2001 6:49 PM > To: andy > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] JAVA vs PERL : PERL wins to postgreSQL > > > Andy, > > Thanks for the code. In looking at this there are two things > that come > to mind: > > 1) You probably should be running vacuum after the delete to > clean up > the table, but on a table this small, I doubt it will make any real > difference. > > 2) Why have you set autocommit on? This should be much faster with > autocommit off. > > Actually I just did a quick test of the two changes I > mentioned above. > The first (vacuum) doesn't make any noticable difference, however > turning autocommit off causes the timings on my machine to go from 14 > seconds to 6 seconds. Better than 50% improvement. > > thanks, > --Barry > > > andy wrote: > > To all those who responded , > > Thanks for your response, > > > > I have attached the test programs that I used in the form > of text files. The > > contents should be self evident. Please have a look and comment. > > > > I used j2sdk1.3.1 down load to linux machine from sun.com > as my java platform > > and > > And perl, v5.6.0 built for i386-linux. > > The PostgreSQL driver for perl came from www.perl.org > > The postgreSQL driver in jdbc7.0-1.2.jar > > > > > > Barry Lind wrote: > > > > > >>Andy, > >> > >>I would be interesting in knowing what version you did this test on, > >>what platform, and most importantly which JDK (and if the > Sun JDK which > >>JVM: classic, hotspot client, hotspot server). > >> > >>thanks, > >>--Barry > >> > >>andy wrote: > >> > >>>Hi, > >>> > >>>I ran a few bench marks on JAVA writing to a postgreSQL > table using and > >>>found that for the same number of records added to the table as a > >>>similar PERL routine the following results : > >>> PERL 39 seconds : JAVA 45 Seconds. > >>>In a similar experiment where PERL and JAVA did treir output to the > >>>screen and not to a table, > >>> > >>>JAVA took 3 seconds and PERL 310 Seconds. > >>>My conclusion is that the database driver to postgreSQL is > still far > >>>from efficient in the JAVA implementation. > >>> > >>>Both tests were run on the same computer. > >>> > >>>I would appreciate your comments and suggestions. > >>>Andy Sewell > >>> > >>> > >>>---------------------------(end of > broadcast)--------------------------- > >>>TIP 5: Have you checked our extensive FAQ? > >>> > >>>http://www.postgresql.org/users-lounge/docs/faq.html > >>> > >>> > >>> > >>> > >>>----------------------------------------------------------- > ------------- > >>> > >>>/* > >>> Java bench mark against perl count to 100 000 000 > >>>*/ > >>>import java.io.*; > >>>import java.util.*; > >>> > >>>public class lp{ > >>> > >>> public lp() throws ClassNotFoundException, > FileNotFoundException, IOException > >>> { > >>> int last = 1000; > >>> > >>> GregorianCalendar day = new GregorianCalendar(); > >>> String time = day.getTime().toString(); > >>> int k=0; > >>> for (int i=0; i<last ; i++) { > >>> for (int p=0; p<last ; p++) > {k=1+p;System.out.println(k);} > >>> } > >>> > >>> GregorianCalendar day1 = new GregorianCalendar(); > >>> String time2 = day1.getTime().toString(); > >>> System.out.println(time); > >>> System.out.println(time2 + "\n" + k); > >>> > >>> > >>> } > >>> > >>>public static void main(String[] args) { > >>> try { > >>> lp test = new lp(); > >>> } > >>> catch (Exception ex) { > >>> System.err.println("Exception :" + ex); > >>> ex.printStackTrace(); > >>> } > >>>} > >>>} > >>> > >>> > >>> > >>>----------------------------------------------------------- > ------------- > >>> > >>>/* > >>> Java equivalent > >>>*/ > >>>import java.io.*; > >>>import java.sql.*; > >>>import java.util.*; > >>> > >>>public class pg{ > >>> Connection conn; > >>> Statement stmt; > >>> > >>> public pg() throws ClassNotFoundException, > FileNotFoundException, IOException, SQLException > >>> { > >>> Class.forName("org.postgresql.Driver"); > >>> conn = > DriverManager.getConnection("jdbc:postgresql:exkom", "andy", ""); > >>> conn.setAutoCommit(true); > >>> stmt = conn.createStatement(); > >>> int last = 10000; > >>> int res = stmt.executeUpdate("delete from junk"); > >>> > >>> GregorianCalendar day = new GregorianCalendar(); > >>> String time = day.getTime().toString(); > >>> > >>> for (int i=0; i<last ; i++) { > >>> // try { > >>> res = stmt.executeUpdate("insert into > junk values(" + i + ")"); > >>> > >>> // } catch (SQLException e) { > >>> // System.out.println(e); > >>> // } > >>> } > >>> > >>> GregorianCalendar day1 = new GregorianCalendar(); > >>> String time2 = day1.getTime().toString(); > >>>// String time3 = day.getTime().toString(); > >>> System.out.println(time); > >>> System.out.println(time2); > >>>// System.out.println(time3); > >>> > >>>// res.close(); > >>> stmt.close(); > >>> conn.close(); > >>> > >>> > >>> } > >>> > >>>public static void main(String[] args) { > >>> try { > >>> pg test = new pg(); > >>> } > >>> catch (Exception ex) { > >>> System.err.println("Exception :" + ex); > >>> ex.printStackTrace(); > >>> } > >>>} > >>>} > >>> > >>>/** > >>> > >>> > >>>$conn = Pg::connectdb("dbname=exkom"); > >>>die $conn->errorMessage unless PGRES_CONNECTION_OK eq > $conn->status; > >>> > >>># print "Enter a state code :"; > >>># $state_code = <STDIN>; > >>> > >>># chomp $state_code; > >>>$result = $conn->exec("delete from junk"); > >>>$end = 10000; > >>> > >>>$t0 = new Benchmark; > >>> > >>>for ($i=1; $i < $end; $i++) { > >>> $result = $conn->exec("insert into junk values($i)"); > >>>} > >>> > >>>$t1 = new Benchmark; > >>>$td = timediff($t1, $t0); > >>>print " the $end records took :", timestr($td) , "\n" > >>>**/ > >>> > >>> > >>>----------------------------------------------------------- > ------------- > >>> > >>> > >>>---------------------------(end of > broadcast)--------------------------- > >>>TIP 3: if posting/reading through Usenet, please send an > appropriate > >>>subscribe-nomail command to majordomo@postgresql.org so that your > >>>message can get through to the mailing list cleanly > >>> > >>> lp.java > >>> > >>> Content-Type: > >>> > >>> text/plain > >>> Content-Encoding: > >>> > >>> 7bit > >>> > >>> > >>> > -------------------------------------------------------------- > ---------- > >>> lp.pl > >>> > >>> Content-Type: > >>> > >>> application/x-perl > >>> Content-Encoding: > >>> > >>> 7bit > >>> > >>> > >>> > -------------------------------------------------------------- > ---------- > >>> pg.java > >>> > >>> Content-Type: > >>> > >>> text/plain > >>> Content-Encoding: > >>> > >>> 7bit > >>> > >>> > >>> > -------------------------------------------------------------- > ---------- > >>> pg.pl > >>> > >>> Content-Type: > >>> > >>> application/x-perl > >>> Content-Encoding: > >>> > >>> 7bit > >>> > >>> > >>> > -------------------------------------------------------------- > ---------- > >>> Part 1.6 > >>> > >>> Content-Type: > >>> > >>> text/plain > >>> Content-Encoding: > >>> > >>> binary > >>> > >>> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > http://www.postgresql.org/users-lounge/docs/faq.html