The answer to your question is, it depends. From an Oracle perspective: the first stage of SQL statement execution is parsing, the second stage is binding of variables. If the statement will be run once, it really makes very little difference, sort of. If you are running the statement within a loop with changing the values of the fields in the where clause every iteration of the loop, then you most likely want to bind the variables to avoid re-parsing (compiling) the SQL statement at every pass.
Now, what about the sort of? If your Oracle tables are analyzed (with histograms) and you want the optimizer to make use of the histograms, then you do NOT want to bind the variables. This is because the optimizer needs to know the value of the fields during parsing to make use of the histograms to create the best execution plan. This particular issue of histograms is especially critical for data warehouse (large quantities of data) situations. This is probably a bit more info than you wanted... BTW, remember to close your result sets and prepared statements! When you receive complaints from your fellow developers, dbas, and you see your code fail because of the error: exceeded maximum number of open cursors, you will know why. I hope this helps, Dave -----Original Message----- From: Ramaswamy, Rathnapriya (CORP, GTS, L531741) [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 8:11 AM To: [EMAIL PROTECTED] Subject: Re: JDBC question - PreparedStatement Thanx Mark, but what I actually wanted to know was....(Sorry I did not convey properly) String query1="update table1 set field1 = ?, field2= ? where field3 ='"+someBeanInstance.getName()+"' and field4 = '"+someBeanInstance.getDesc()+"'"; String query2="update table1 set field1 = ?, field2 = ? where field3 = ? and field4=?; I wanted to know, if setting values in where clause directly rather than setting it in the PreparedStatement object has any adv/disadv? Is it better to always set the values in the PreparedStatement Object? Hope I have put across my doubt now. Thanx, Priya -----Original Message----- From: Mark Galbreath [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 6:30 PM To: [EMAIL PROTECTED] Subject: Re: JDBC question - PreparedStatement Not worth worrying about. After the first call on the prepared statement, all variables will be set for any subsequent calls. The nanosecond taken to set an additional couple of variables' values on the first call is negligible. Mark -----Original Message----- From: Ramaswamy, Rathnapriya Sent: Thursday, January 24, 2002 7:32 AM Is there any difference b/w usage of these two queries while using PreparedStatement. 1) String query = "update table1 set field1 = ?, field2= ? where field3 = 'value3' and field4 = 'value4'; 2) String query = "update table1 set field1 = ?, field2 = ? where field3 = ? and field4 = ?; Thanx, Priya. ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html "THIS E-MAIL MESSAGE ALONG WITH ANY ATTACHMENTS IS INTENDED ONLY FOR THE ADDRESSEE and may contain confidential and privileged information. If the reader of this message is not the intended recipient, you are notified that any dissemination, distribution or copy of this communication is strictly Prohibited. If you have received this message by error, please notify us immediately, return the original mail to the sender and delete the message from your system." ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
