AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  PreparedStatement slows down          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message mo@ekontrol.com Guest Posted: Thu Jan 12, 2006 2:51 pm    Post subject: PreparedStatement slows down Hello, I try to insert many rows (up to 5000) with ca. 40 Columns into MS Access over the driver sun.jdbc.odbc.JdbcOdbcDriver. Autocommit is false. So to have a better performace I tried to use a PreparedStatement: PreparedStatement prepUpdate = m_dbAccess.getConnection().prepareStatement("INSERT INTO tb_Clients_History VALUES(?,?...)"); while(...) { prepUpdate.setString(1,"data0"); ..... prepUpdate.setString(40,"data40")); prepUpdate.executeUpdate(); } prepUpdate.close(); m_dbAccess.getConnection().commit(); This solution is very slow. So I tried to use prepUpdate.addBatch() but this came circa to the same result. Then I tried only to see how many time it will take, a hard INSERT INTO with all data in my loop and that worked 10 TIMES faster!!! How that? What I do wron in my preparedStatement? Regards Martin Oberhammer Back to top Joe Weinstein Guest Posted: Thu Jan 12, 2006 5:08 pm    Post subject: Re: PreparedStatement slows down [email]mo (AT) ekontrol (DOT) com[/email] wrote: Quote: Hello, I try to insert many rows (up to 5000) with ca. 40 Columns into MS Access over the driver sun.jdbc.odbc.JdbcOdbcDriver. Autocommit is false. So to have a better performace I tried to use a PreparedStatement: PreparedStatement prepUpdate = m_dbAccess.getConnection().prepareStatement("INSERT INTO tb_Clients_History VALUES(?,?...)"); while(...) { prepUpdate.setString(1,"data0"); ..... prepUpdate.setString(40,"data40")); prepUpdate.executeUpdate(); } prepUpdate.close(); m_dbAccess.getConnection().commit(); This solution is very slow. So I tried to use prepUpdate.addBatch() but this came circa to the same result. Then I tried only to see how many time it will take, a hard INSERT INTO with all data in my loop and that worked 10 TIMES faster!!! How that? What I do wron in my preparedStatement? Regards Martin Oberhammer Hi. There's nothing wrong with your code, but JDBC-ODBC bridges suck. I have never yet heard of a commercial-quality high-performance one. Sun's is old, buggy, not threadsafe, and unsupported. If you have to use it (and also to test any other candidate driver), you could try: Statement s = m_dbAccess.getConnection().createStatement(); StringBuffer myHomeGrownBatch = new StringBuffer(); "INSERT INTO tb_Clients_History VALUES(?,?...)"); while(...) { myHomeGrownBatch.append( "INSERT INTO tb_Clients_History VALUES(" + "'" + "data0" + "'" + ", " ..... + "'" + "data40" + "'" + ")n" ); } s.execute(myHomeGrownBatch.toString()); s.close(); m_dbAccess.getConnection().commit(); Note the newline after each line in the batch. If the DBMS accepts multiple lines like that (like MS SQLServer, Sybase etc) fine. Some DBMSes like Oracle would need a string like: "BEGIN insert into ....; insert into ....; END;" Just make one big string and send it to the DBMS in one call. A driver has to be very good to beat this technique for a single client. (This may not be optimal for many clients repeating it, and a good dirver may be a bit slower with a proper prepared statement, but allowing much more concurrency. HTH, Joe Weinstein at BEA Systems Joe Weinstein at BEA Systems. Back to top Joe Weinstein Guest Posted: Thu Jan 12, 2006 6:07 pm    Post subject: Re: PreparedStatement slows down Joe Weinstein wrote: Quote: [email]mo (AT) ekontrol (DOT) com[/email] wrote: Hello, I try to insert many rows (up to 5000) with ca. 40 Columns into MS Access over the driver sun.jdbc.odbc.JdbcOdbcDriver. Autocommit is false. So to have a better performace I tried to use a PreparedStatement: PreparedStatement prepUpdate = m_dbAccess.getConnection().prepareStatement("INSERT INTO tb_Clients_History VALUES(?,?...)"); while(...) { prepUpdate.setString(1,"data0"); ..... prepUpdate.setString(40,"data40")); prepUpdate.executeUpdate(); } prepUpdate.close(); m_dbAccess.getConnection().commit(); This solution is very slow. So I tried to use prepUpdate.addBatch() but this came circa to the same result. Then I tried only to see how many time it will take, a hard INSERT INTO with all data in my loop and that worked 10 TIMES faster!!! How that? What I do wron in my preparedStatement? Regards Martin Oberhammer Hi. There's nothing wrong with your code, but JDBC-ODBC bridges suck. I have never yet heard of a commercial-quality high-performance one. Sun's is old, buggy, not threadsafe, and unsupported. If you have to use it (and also to test any other candidate driver), you could try: Statement s = m_dbAccess.getConnection().createStatement(); StringBuffer myHomeGrownBatch = new StringBuffer(); Sorry about the typo below. Remove this part: Quote: "INSERT INTO tb_Clients_History VALUES(?,?...)"); the rest is valid... Joe Quote: while(...) { myHomeGrownBatch.append( "INSERT INTO tb_Clients_History VALUES(" + "'" + "data0" + "'" + ", " ..... + "'" + "data40" + "'" + ")n" ); } s.execute(myHomeGrownBatch.toString()); s.close(); m_dbAccess.getConnection().commit(); Note the newline after each line in the batch. If the DBMS accepts multiple lines like that (like MS SQLServer, Sybase etc) fine. Some DBMSes like Oracle would need a string like: "BEGIN insert into ....; insert into ....; END;" Just make one big string and send it to the DBMS in one call. A driver has to be very good to beat this technique for a single client. (This may not be optimal for many clients repeating it, and a good dirver may be a bit slower with a proper prepared statement, but allowing much more concurrency. HTH, Joe Weinstein at BEA Systems Joe Weinstein at BEA Systems. Back to top Jay Grubb Guest Posted: Thu Jan 12, 2006 6:07 pm    Post subject: Re: PreparedStatement slows down 2 things here: 1) The Sun jdbc-odbc bridge is not ready for production. If you look at the docs, it says basically that you are on your own, and it was made just to give something to test against. Bridges can work satisfactorily, but the Sun one is rather lowest common denominator. 2) I'm not sure you are using a prepared statement to it's best advantage. I'd need to see a little more of the code. You need to use the same statement in a loop, changing only the "prepUpdate.setString(1,"data0");" values. Example code from http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html PreparedStatement updateSales; String updateString = "update COFFEES " + "set SALES = ? where COF_NAME like ?"; updateSales = con.prepareStatement(updateString); int [] salesForWeek = {175, 150, 60, 155, 90}; String [] coffees = {"Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf"}; int len = coffees.length; for(int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); } If you interested, you get a demo jdbc-odbc bridge (Type 1), or a Type 3 jdbc driver for oracle here: http://www.openlinksw.com If you run into issues, open a support cand ask for me. Jay Grubb Technical Consultant OpenLink Software Web: http://www.openlinksw.com: Product Weblogs: Virtuoso: http://www.openlinksw.com/weblogs/virtuoso UDA: http://www.openlinksw.com/weblogs/uda Universal Data Access & Virtual Database Technology Providers [email]mo (AT) ekontrol (DOT) com[/email] wrote: Quote: Hello, I try to insert many rows (up to 5000) with ca. 40 Columns into MS Access over the driver sun.jdbc.odbc.JdbcOdbcDriver. Autocommit is false. So to have a better performace I tried to use a PreparedStatement: PreparedStatement prepUpdate = m_dbAccess.getConnection().prepareStatement("INSERT INTO tb_Clients_History VALUES(?,?...)"); while(...) { prepUpdate.setString(1,"data0"); ..... prepUpdate.setString(40,"data40")); prepUpdate.executeUpdate(); } prepUpdate.close(); m_dbAccess.getConnection().commit(); This solution is very slow. So I tried to use prepUpdate.addBatch() but this came circa to the same result. Then I tried only to see how many time it will take, a hard INSERT INTO with all data in my loop and that worked 10 TIMES faster!!! How that? What I do wron in my preparedStatement? Regards Martin Oberhammer Back to top mo@ekontrol.com Guest Posted: Fri Jan 13, 2006 7:15 am    Post subject: Re: PreparedStatement slows down Hi! To Joe Weinstein: Thank you for your propose, I will try the StringBuffer. To Jay Grubb: In my program I use the PreparedStatement with different values in the loop. Here I had should write prepUpdate.setString(1,data0[i]); ..... prepUpdate.setString(40,data40[i]); I know that the Sun Bridge is not ready for production (I already read the docs) but I don't want to buy a commercial driver. Do you know a free one for MS Access? And if I have to deal with a Oracle db I will try your driver. Thanks to all! Regards, Martin Oberhammer Back to top mo@ekontrol.com Guest Posted: Fri Jan 13, 2006 7:19 am    Post subject: Re: PreparedStatement slows down Hi! To Joe Weinstein: Thank you for your propose, I will try the StringBuffer. To Jay Grubb: In my program I use the PreparedStatement with different values in the loop. Here I had should write prepUpdate.setString(1,data0[i]); ..... prepUpdate.setString(40,data40[i]); I know that the Sun Bridge is not ready for production (I already read the docs) but I don't want to buy a commercial driver. Do you know a free one for MS Access? And if I have to deal with a Oracle db I will try your driver. Thanks to all! Regards, Martin Oberhammer Back to top Jay Grubb Guest Posted: Wed Jan 18, 2006 9:57 pm    Post subject: Re: PreparedStatement slows down Doing some quick googling.... http://jackcess.sourceforge.net/ Appears to be a java class for MS Access, but is not a jdcb driver. Not sure if that's what you want. Note that there are several ways to get you where you are going. One thing you will find is that a stable driver is worth money, since most programming is about consuming and persisting data. Look us up if you have any questions. We'll set you up with a demo, and tech supoort to get it working, if you need. Jay Grubb Technical Consultant OpenLink Software Web: http://www.openlinksw.com: Product Weblogs: Virtuoso: http://www.openlinksw.com/weblogs/virtuoso UDA: http://www.openlinksw.com/weblogs/uda Universal Data Access & Virtual Database Technology Providers [email]mo (AT) ekontrol (DOT) com[/email] wrote: Quote: Hi! To Joe Weinstein: Thank you for your propose, I will try the StringBuffer. To Jay Grubb: In my program I use the PreparedStatement with different values in the loop. Here I had should write prepUpdate.setString(1,data0[i]); ..... prepUpdate.setString(40,data40[i]); I know that the Sun Bridge is not ready for production (I already read the docs) but I don't want to buy a commercial driver. Do you know a free one for MS Access? And if I have to deal with a Oracle db I will try your driver. Thanks to all! Regards, Martin Oberhammer Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.