> Ok, so I found out I WAS correct. Either that or yet another article I > found on prepared statements is wrong. Here's a quote from the article: > "The PreparedStatement object contains not just an SQL statement, but an SQL > statement that has been precompiled. This means that when the > PreparedStatement is executed, the DBMS can just run the > PreparedStatementSQL statement without having to compile it first." > > So according to this, the SQL statement IS precompiled, as I had asserted. > Here's the article: > http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html
I hate to disagree, but it's not "precompiled" in the traditional sense of the word. When you compile a program, the compiler converts it from the code you wrote to a different form (binary, bytecode) that can be directly executed by the computer or a VM like the JVM or Flash Player. This isn't what happens with SQL. "Compiled" in this case means that the database server has created a corresponding execution plan. The database server can then store and reuse this execution plan if it receives the same SQL statement later. The execution plan is not a direct conversion from the original SQL, either - it will depend on the underlying structure of the database (indexes, etc). So, you could take the same SQL statement and get different execution plans. The execution plan itself is the "program" that is run. But there is no actual compilation of the SQL statement, and the client will continue to send "raw" SQL statements to the database server. And, by default, this is the behavior you see whether you use prepared statements or not. If you send this query to the database twice: SELECT * FROM mytable WHERE myfield = 1 AND myotherfield = 1 the second execution will reuse the execution plan created by the first execution. Of course, if you were to then execute this query: SELECT * FROM mytable WHERE myfield = 2 AND myotherfield = 1 the execution plan from the first query would not be reused. A prepared statement changes this behavior by letting you specify placeholders within your query. In a prepared statement, you'll have two sections - the query and the values to plug into the placeholders. So, the database server would receive this: SELECT * FROM mytable WHERE myfield = ? AND myotherfield = 1 followed by the value to plug into "?". On the first execution, it would build an execution plan for the query above, without plugging in the value. On subsequent executions, it would reuse that plan. Usually, the plan will be optimal for all values that could be placed in the query, and that's where we see performance gains. If the plan isn't optimal for all values, though, you may see decreased performance: if the suboptimal execution plan takes longer to execute than the optimal execution plan would take to create and execute. If "myotherfield" could change, you'd want to put a placeholder there too. If you don't, the database server would need to create a new execution plan for this query: SELECT * FROM mytable WHERE myfield = ? AND myotherfield = 2 But if the value of "myotherfield" never changes from 1, you don't have to store that value in a placeholder, and won't see any performance benefit if you do store it in a placeholder. Now, within the database world, you will see the use of the words "compile", "recompile", etc - but again, they don't mean what they do in other environments. For example, in MS SQL Server, you can use "WITH RECOMPILE" when building a query or stored procedure, and that tells the database server not to reuse any existing execution plan. Fortunately, you can easily see all this at work. I tend to work with MS SQL Server more than other platforms, so that's the one I'm most familiar with, but it provides all sorts of tools that let you see the underlying things going on. For example, SQL Profiler lets you see the raw queries being sent from the client. SHOWPLAN lets you view the execution plan from Query Analyzer (or whatever they call that in 2005+). And having taken the MSDBA exams, all this stuff is covered in detail there. But Oracle has similar (and probably better) tools, as Oracle guys have shown me more than once. I agree that this is a subtle difference, and not that important. But what is important is the set of conclusions that you're drawing from this concept of "compilation" - those conclusions aren't correct. In summary: - all queries, whether "prepared" or not, are "compiled" (they have a corresponding execution plan) - you don't have to have placeholders for all values in a statement to make it a prepared statement, although to get the maximum reuse from a prepared statement, you should have placeholders for all values that might change. Those two assertions address the points you raised in your original response, and the whole "compile" debate is really irrelevant to that. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsit ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337539 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
