{"id":10130,"date":"2015-08-07T00:00:48","date_gmt":"2015-08-07T04:00:48","guid":{"rendered":"http:\/\/bangla.salearningschool.com\/?p=10130"},"modified":"2015-08-01T09:22:18","modified_gmt":"2015-08-01T13:22:18","slug":"preparedstatements-java-an-example-to-test-the-timing-differences-resulting-from-repeated-raw-queries-vs-repeated-calls","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=10130","title":{"rendered":"PreparedStatements.java An example to test the timing differences resulting from repeated raw queries vs. repeated calls"},"content":{"rendered":"<pre>package cwp;\r\n\r\nimport java.sql.*;\r\n\r\n\/** An example to test the timing differences resulting\r\n\u00a0*\u00a0 from repeated raw queries vs. repeated calls to\r\n\u00a0*\u00a0 prepared statements. These results will vary dramatically\r\n\u00a0*\u00a0 among database servers and drivers. With my setup\r\n\u00a0*\u00a0 and drivers, Oracle prepared statements took only half\r\n\u00a0*\u00a0 the time that raw queries required when using a modem\r\n\u00a0*\u00a0 connection, and took only 70% of the time that\r\n\u00a0*\u00a0 raw queries required when using a fast LAN connection.\r\n\u00a0*\u00a0 Sybase times were identical in both cases.\r\n\u00a0* \u00a0\r\n\r\n\r\n\u00a0*\/\r\n\r\npublic class PreparedStatements {\r\n\u00a0 public static void main(String[] args) {\r\n\u00a0\u00a0\u00a0 if (args.length &lt; 5) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 printUsage();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 return;\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 String vendorName = args[4];\r\n\u00a0\u00a0\u00a0 int vendor = DriverUtilities.getVendor(vendorName);\r\n\u00a0\u00a0\u00a0 if (vendor == DriverUtilities.UNKNOWN) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 printUsage();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 return;\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 String driver = DriverUtilities.getDriver(vendor);\r\n\u00a0\u00a0\u00a0 String host = args[0];\r\n\u00a0\u00a0\u00a0 String dbName = args[1];\r\n\u00a0\u00a0\u00a0 String url =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DriverUtilities.makeURL(host, dbName, vendor);\r\n\u00a0\u00a0\u00a0 String username = args[2];\r\n\u00a0\u00a0\u00a0 String password = args[3];\r\n\u00a0\u00a0\u00a0 \/\/ Use \"print\" only to confirm it works properly,\r\n\u00a0\u00a0\u00a0 \/\/ not when getting timing results.\r\n\u00a0\u00a0\u00a0 boolean print = false;\r\n\u00a0\u00a0\u00a0 if ((args.length &gt; 5) &amp;&amp; (args[5].equals(\"print\"))) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 print = true;\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 Connection connection =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 getConnection(driver, url, username, password);\r\n\u00a0\u00a0\u00a0 if (connection != null) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 doPreparedStatements(connection, print);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 doRawQueries(connection, print);\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0 }\r\n\r\n\u00a0 private static void doPreparedStatements(Connection conn,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 boolean print) {\r\n\u00a0\u00a0\u00a0 try {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 String queryFormat =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"SELECT lastname FROM employees WHERE salary &gt; ?\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 PreparedStatement statement =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 conn.prepareStatement(queryFormat);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 long startTime = System.currentTimeMillis();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 for(int i=0; i&lt;40; i++) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 statement.setFloat(1, i*5000);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ResultSet results = statement.executeQuery();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (print) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 showResults(results);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 long stopTime = System.currentTimeMillis();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 double elapsedTime = (stopTime - startTime)\/1000.0;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 System.out.println(\"Executing prepared statement \" +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"40 times took \" +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elapsedTime + \" seconds.\");\r\n\u00a0\u00a0\u00a0 } catch(SQLException sqle) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 System.out.println(\"Error executing statement: \" + sqle);\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0 }\r\n\r\n\u00a0 public static void doRawQueries(Connection conn,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 boolean print) {\r\n\u00a0\u00a0\u00a0 try {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 String queryFormat =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"SELECT lastname FROM employees WHERE salary &gt; \";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Statement statement = conn.createStatement();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 long startTime = System.currentTimeMillis();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 for(int i=0; i&lt;40; i++) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ResultSet results =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 statement.executeQuery(queryFormat + (i*5000));\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (print) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 showResults(results);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 long stopTime = System.currentTimeMillis();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 double elapsedTime = (stopTime - startTime)\/1000.0;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 System.out.println(\"Executing raw query \" +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"40 times took \" +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elapsedTime + \" seconds.\");\r\n\u00a0\u00a0\u00a0 } catch(SQLException sqle) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 System.out.println(\"Error executing query: \" + sqle);\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0 } \r\n\r\n\u00a0 private static void showResults(ResultSet results)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 throws SQLException {\r\n\u00a0\u00a0\u00a0 while(results.next()) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 System.out.print(results.getString(1) + \" \");\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 System.out.println();\r\n\u00a0 }\r\n\u00a0\u00a0 \u00a0\r\n\u00a0 private static Connection getConnection(String driver,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 String url,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 String username,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 String password) {\r\n\u00a0\u00a0\u00a0 try {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Class.forName(driver);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Connection connection =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DriverManager.getConnection(url, username, password);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 return(connection);\r\n\u00a0\u00a0\u00a0 } catch(ClassNotFoundException cnfe) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 System.err.println(\"Error loading driver: \" + cnfe);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 return(null);\r\n\u00a0\u00a0\u00a0 } catch(SQLException sqle) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 System.err.println(\"Error connecting: \" + sqle);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 return(null);\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0 }\r\n\r\n\u00a0 private static void printUsage() {\r\n\u00a0\u00a0\u00a0 System.out.println(\"Usage: PreparedStatements host \" +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"dbName username password \" +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"oracle|sybase [print].\");\r\n\u00a0 }\r\n}<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>package cwp; import java.sql.*; \/** An example to test the timing differences resulting \u00a0*\u00a0 from repeated raw queries vs. repeated calls to \u00a0*\u00a0 prepared statements. These results will vary dramatically \u00a0*\u00a0 among database servers and drivers. With my setup \u00a0*\u00a0 and drivers, Oracle prepared statements took only half \u00a0*\u00a0 the time that raw queries required &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"http:\/\/bangla.sitestree.com\/?p=10130\">Continue reading<\/a><\/p>\n","protected":false},"author":130,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1417,1424],"tags":[706,308,1453,285],"class_list":["post-10130","post","type-post","status-publish","format-standard","hentry","category-code-programming-samples--","category-javaj2eej2me","tag-code","tag-java","tag-querie","tag-285","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":26854,"url":"http:\/\/bangla.sitestree.com\/?p=26854","url_meta":{"origin":10130,"position":0},"title":"PreparedStatements.java  An example to test the timing differences resulting from repeated raw queries vs. repeated calls #Programming Code Examples #Java\/J2EE\/J2ME #JDBC","author":"Author-Check- Article-or-Video","date":"May 3, 2021","format":false,"excerpt":"package cwp; import java.sql.*; \/** An example to test the timing differences resulting * from repeated raw queries vs. repeated calls to * prepared statements. These results will vary dramatically * among database servers and drivers. With my setup * and drivers, Oracle prepared statements took only half * the\u2026","rel":"","context":"In &quot;FromSitesTree.com&quot;","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10425,"url":"http:\/\/bangla.sitestree.com\/?p=10425","url_meta":{"origin":10130,"position":1},"title":"The class that actually gets the strings over the network by means of an ObjectInputStream via HTTP tunneling.","author":"","date":"August 28, 2015","format":false,"excerpt":"import java.net.*; import java.io.*; \/** When this class is built, it returns a value \u00a0*\u00a0 immediately, but this value returns false for isDone \u00a0*\u00a0 and null for getQueries. Meanwhile, it starts a Thread \u00a0*\u00a0 to request an array of query strings from the server, \u00a0*\u00a0 reading them in one fell\u2026","rel":"","context":"In &quot;Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8&quot;","block_context":{"text":"Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8","link":"http:\/\/bangla.sitestree.com\/?cat=1417"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":27064,"url":"http:\/\/bangla.sitestree.com\/?p=27064","url_meta":{"origin":10130,"position":2},"title":"The class that actually gets the strings over the network by means of an ObjectInputStream via HTTP tunneling. #Programming Code Examples #Java\/J2EE\/J2ME #Applets and Basic Graphics","author":"Author-Check- Article-or-Video","date":"May 9, 2021","format":false,"excerpt":"import java.net.*; import java.io.*; \/** When this class is built, it returns a value * immediately, but this value returns false for isDone * and null for getQueries. Meanwhile, it starts a Thread * to request an array of query strings from the server, * reading them in one fell\u2026","rel":"","context":"In &quot;FromSitesTree.com&quot;","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":78391,"url":"http:\/\/bangla.sitestree.com\/?p=78391","url_meta":{"origin":10130,"position":3},"title":"Code Refactoring in Java EE","author":"Sayed","date":"August 23, 2025","format":false,"excerpt":"From AI Tools\/OpenAI\/Internet \" Absolutely! Here\u2019s a concise, copyright-free summary of Java EE code refactoring Java EE Code Refactoring: Best Practices Refactoring in Java EE helps improve code quality, maintainability, and performance without changing functionality. Here are some key practices: 1. General Refactoring Remove duplicate code with helper methods or\u2026","rel":"","context":"In &quot;Anything JAVA&quot;","block_context":{"text":"Anything JAVA","link":"http:\/\/bangla.sitestree.com\/?cat=1975"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":27062,"url":"http:\/\/bangla.sitestree.com\/?p=27062","url_meta":{"origin":10130,"position":4},"title":"An applet that reads arrays of strings packaged inside a QueryCollection and places them in a scrolling TextArea. #Programming Code Examples #Java\/J2EE\/J2ME #Applets and Basic Graphics","author":"Author-Check- Article-or-Video","date":"May 9, 2021","format":false,"excerpt":"import java.applet.Applet; import java.awt.*; import java.awt.event.*; import java.net.*; \/** Applet reads arrays of strings packaged inside * a QueryCollection and places them in a scrolling * TextArea. The QueryCollection obtains the strings * by means of a serialized object input stream * connected to the QueryGenerator servlet. * * Taken\u2026","rel":"","context":"In &quot;FromSitesTree.com&quot;","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10423,"url":"http:\/\/bangla.sitestree.com\/?p=10423","url_meta":{"origin":10130,"position":5},"title":"An applet that reads arrays of strings packaged inside a QueryCollection and places them in a scrolling TextArea.","author":"","date":"August 28, 2015","format":false,"excerpt":"import java.applet.Applet; import java.awt.*; import java.awt.event.*; import java.net.*; \/** Applet reads arrays of strings packaged inside \u00a0*\u00a0 a QueryCollection and places them in a scrolling \u00a0*\u00a0 TextArea. The QueryCollection obtains the strings \u00a0*\u00a0 by means of a serialized object input stream \u00a0*\u00a0 connected to the QueryGenerator servlet. \u00a0* \u00a0 \u00a0*\u00a0\u2026","rel":"","context":"In &quot;Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8&quot;","block_context":{"text":"Code . Programming Samples . \u09aa\u09cd\u09b0\u09cb\u0997\u09cd\u09b0\u09be\u09ae \u0989\u09a6\u09be\u09b9\u09b0\u09a8","link":"http:\/\/bangla.sitestree.com\/?cat=1417"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/10130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/130"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10130"}],"version-history":[{"count":1,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/10130\/revisions"}],"predecessor-version":[{"id":10131,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/10130\/revisions\/10131"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10130"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}