I've read that PreparedStatements are faster than Statements. But when I wrote a small piece of code to compare the speeds, it turned out that the latter is faster... Is there any special condition, only under which PreparedStatements are faster?
Thanks<br />Karthik<br />SCJP 1.4, CCNA.<br /> <br />"Success is relative. More the success, more the relatives."
Prepared Statements are used only if you need to run a the same sql query in a loop ( with the value of parameters changing). If your requirement doesnt mach this functionality its better you go for ordinary Statemets. Thanks renju
Basically, a Statement is compiled only when its execute is called. A PreparedStatement, on the other hand, is pre-compiled during its initialisation, with unfilled parameter values. As such, multiple updations to the SQL Area of the database (having the same query) using Prepared statement takes less time as compared to multiple updations using Statement. So, in your test piece of code, if you iterate over the insertions/updations for, say, 500 times, you'll clearly see the expected difference. regards
There is another reason other than iterations that make PreparedStatements more performant than standard Statements. If you reuse identical queries but the only part of the query that changes is the id of the record(s) that you are looking for then if you use a PreparedStatement the database is much more likely to cache and re-use the SQL execution plan. This can have a considerable benefit to the performance of the application. I also personally find that PrepareStatements are much easier to read than SQL statements that contain a lot of string concatenation, addition of quotes, date formatting etc. As an added bonus PreparedStatements also handle potentially awkward situations where a String may contain characters such as single or double quotes.
Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
Prepared Statements being in the pre complied format, they are always executed fater then the general statements. However if you are comparing it for the first execution, then the vauue may differ becuase prepared statements are bit slow when complied for the first time. Also please make sure that you use the same statement changing the parameters and also the number of parameters you use in the statement and prepared statement be the same. If you have prepared statement with 5 parameters and statement object with 2 parameters, i think the excution time will be faster for statement object. If you realy find any reason opposing my statement, please send it to me at email@example.com. I would appreciate wyou help in this regard.