I have to execute a query that takes some input parameters. I have to execute this query only once. I read that when u have to execute a statement only once use Statement and not PreparedStatement as this is more efficient. But arent preparedstatements to be used when u have input parameters to supply? What is the best thing for me to do? Thanks in advance. Janis
I would be very suprised if PreparedStatements had a significant negative impact on performance. Of course, you could write a test application an find out.
An advantage of PreparedStatements is that they allow you to bind values that are hard to handle in Statements - Dates being the most common problem type. I'd always use a PreparedStatement, unless I was dynamically building the actual query, rather than just dynamically assigning values. [ May 09, 2006: Message edited by: Paul Sturrock ]
Originally posted by Paul Sturrock: I would be very suprised if PreparedStamenets had a significant negative impact on performance. Of course, you could write a test application an find out.
It actually depends a bit on the particular driver and database you're using. For Oracle database and driver, there is no significant difference in performance between a Statement and a PreparedStatement that is execute only once, ever. However, if the PreparedStatement is executed again an hour or a day or a week later and it has bind variables ("?"s in the SQL), then the statement might still be in the database's cache and you'll get a performance improvement on the following executions.
Sadly, a widely published book on Oracle (an O'Reilly book) did a side-by-side test of the two and made a very significant mistake in the test, creating the false conclusion that Statement was faster for a small number of repetitions. The mistake was that the Oracle driver loads the Statement class when a connection is obtained and the test was including the time to load the PreparedStatement class with the time to execute it and not including the class loading time against the time to execute a Statement. [ May 09, 2006: Message edited by: stu derby ]
Any difference in performance would be marginal and should not be the deciding factor. Use what makes the code clearest and the least error-prone. In that department, the PreparedStatement wins hands-down for any SQL statement that takes parameterized values.
Originally posted by Bear Bibeault: Any difference in performance would be marginal and should not be the deciding factor.
Not true, at least on some databases. On Oracle, for highly repeated, highly concurrent statements, PreparedStatement can be very significantly faster.
Tom Kyte wrote a test that showed 1,000 executions of a particular bit of SQL took half the time using PreparedStatement, single threaded. Because of the way Oracle works, the difference with a mutlithreaded test would be even more dramatic.