 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / April 2008Precompiled statement?
Hi all, I've read a bit about precompiled SQL statements. However, I haven't been able to find anything which explains what the compilation process entails. What exactly is done in order to change a normal inline SQL statement into a precompiled one? Thanks Taras Taras_96 <taras.di@gmail.com> wrote in news:c9ac4fda-90b8-4b1d-b8de- 660144c7ea36@t54g2000hsg.googlegroups.com: > Hi all, > [quoted text clipped - 6 lines] > > Taras In the context of java (jdbc) these are PreparedStatement and CallableStatement class instances. Sorry, if my answer was too short, dum or ather way inappropriate. > Hi all, > [quoted text clipped - 6 lines] > > Taras The major benefit possible by re-using Prepared/CallableStatements is that when you create them, you supply the SQL, which can be sent to the DBMS for parsing and compilation. The resultant query plan is retained by the DBMS, and re-used as long as the statement remains open. The saving is that a plain statement would have to as the DBMS to parse and compile it's SQL for every execution. Joe Weinstein at BEA Systems >> Hi all, >> [quoted text clipped - 15 lines] > > Joe Weinstein at BEA Systems Other benefits of prepared statements: that they can protect against SQL injection attacks, and they can simplify the insertion of user-supplied parameters into an SQL query by taking care of vendor-specific quoting rules. David Harper Cambridge, England >>> Hi all, >>> [quoted text clipped - 20 lines] > parameters into an SQL query by taking care of vendor-specific quoting > rules. But wait, there's more! PreparedStatements also provide increased type safety between your Java data and the SQL data types.  SignatureLew >> Hi all, >> [quoted text clipped - 15 lines] > > Joe Weinstein at BEA Systems Don't forget its other benefit: security. By using Prepared Statements rather than assembling an SQL statement in a String and then executing it you protect yourself against SQL injection attacks Signaturemartin@ | Martin Gregorie gregorie. | org | Zappa fan & glider pilot I think my original question may have been unclear. What is actually being done when a SQL statement is being 'compiled'? Query plan? Parsing of SQL statement? Something else? Thanks Taras > I think my original question may have been unclear. > [quoted text clipped - 4 lines] > > Taras The driver sends the SQL string to the DBMS. The DBMS parses it, and compiles/creates a plan for executing what the SQL wants. This includes verifying the names of tables and columns etc, choosing what index to use etc. This plan may include place-holders for data values passed in as parameters. This is very similar to a session-scoped stored procedure. Then, when the driver sends the needed parameter values, and says 'go', the DBMS executes the plan. A plain statement does the same, all in one call, except the DBMS doesn't retain the plan, so even an immediate repeat of the exact same query requires the DBMS to re-parse, re-check, and re- plan, and re-execute. Joe Weinstein at BEA Systems On Apr 23, 10:33 pm, "joeNOS...@BEA.com" <joe.weinst...@gmail.com> wrote: > > I think my original question may have been unclear. > [quoted text clipped - 22 lines] > and re-execute. > Joe Weinstein at BEA Systems Excellent response - thanks Joe! > The driver sends the SQL string to the DBMS. The DBMS parses it, and > compiles/creates a plan for executing what the SQL wants. This [quoted text clipped - 6 lines] > 'go', > the DBMS executes the plan. That is what a good database and a good JDBC driver does. And what the name PreparedStatement clearly indicates is the intention. But there are AFAIK no guarantee that this is actually happening. So one should check what the specific database does before claiming that prepared statement improves performance. No need to check before choosing prepared statement, because there are so many other advantages of using it. Some years ago I tested Oracle and MySQL. Oracle obviously did the right thing and prepared statement improved performance significantly. On MySQL prepared statement reduced performance (MySQL first introduced PREPARE in version 5.0). Arne Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.