How does DB differentiate b/w Statement & Prepared Statement

[Chetan M]

Hi,
Prepared statement is precompiled.. ie when a prepared statement is for the first time goes to db , its compiled and for the next time for same stmt.. its just executed not compiled again.. HOw does DB decides which is prepared stmt.. n compiles first time & executes rest of the times.. n which is statement..n compiles each time..
What exactly happens when a statement and a prepared statement enters the database..
Can any body like to put some light on that..!!
Thanks in advance..
--------------------------------------
HAKUNAMATATA (Don't Worry Be Happy)!!!
[ December 24, 2002: Message edited by: Chetan Mishra ]

[Tina Coleman]

From 'Professional Java Data':
"It might be interesting to note that the main feature of a PreparedStatement object is that it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement will be sent to the RDBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the Prepared Statement is executed, the DBMS can just run the Prepared Statements SQL statement without having to compile it first."
My interpretation of that would be that it creates something like a temporary stored procedure in the database itself (other materials I've read have referred to the idea of caching the query in the server), and then when you execute the PreparedStatement, it's actually executing that temp SP on the server.
My follow-up question would be, then, does it make sense to use PreparedStatements in an RDBMS that doesn't support stored procedures, as in mySQL? Does mySQL temporarily hold onto compiled SQL statements, or does it need to compile with each execution? And if it needs to compile with each execution, what's the cost of using a PreparedStatement, as opposed to a Statement? (Obviously, you'd only try to eke out the performance benefit if 1) there was a measurable difference, and 2) there was no reason that the system would ever use a db other than mySQL.)

[vishwas bansal]

You just cannot guarantee that Statement will always be recompiled.But you can be sure that pre-statement would not be recompiled.
Let's understand it taking an example...Hope im correct.
1)select * from emp (stmt)
2)select * from Emp (stmt)
3)select * from Emp (stmt)
oracle does a lexical comparison before actually looking for a pre-executed query in database..so query plan for 3 is found bcos it exactly matches with 2.So we cannot say that statement would always be compiled.
then comes the case of prepared statement.
it is useful when we are just querying for different values or inserting record.
we create prepared statement ... which actually places bind variables in place of values.
for ex:
1) select * from emp where ename = :a
:a=scott (setString method)
This willbe the query submitted to oracle.
2) select * from emp where ename = :a
:a=blake
This 2 queries are lexically same.So we can always guarantees that oracle will get the query plan after first execution.
hope things are clear..

[Chetan M]

Hi Tina & Vishwas ,
Thanks, Thanks for the info..
N below is some info abt MYSQL & query chache.
From version 4.0.1, MySQL server features a Query Cache. When in use, the query cache stores the text of a "SELECT" query together with the "corresponding result that was sent to the client". If an identical query is later received, the server will retrieve the results from the query cache rather than parsing and executing the same query again.
Below is some performance data for the query cache. (These results were generated by running the MySQL benchmark suite on a Linux Alpha 2 x 500 MHz with 2GB RAM and a 64MB query cache):
* If all of the queries you're performing are simple (such as selecting a row from a table with one row); but still differ so that the queries can not be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. However, in real life, queries are much more complicated than our simple example so the overhead is normally significantly lower.
* Searches after one row in a one row table is 238% faster. This can be regarded as close to the minimum speedup to be expected for a query that is cached.
If you want to disable the query cache code set query_cache_size=0. By disabling the query cache code there is no noticeable overhead.
[ December 25, 2002: Message edited by: Chetan Mishra ]