This content has been marked as final. Show 22 replies
If you lose/close the prepared statement, yes you lose the ID. If you re-prepare the same
SQL, the DBMS has to parse the SQL again, if only to find whether there is a query plan
still/already existing for the DBMS session. Depending on the DBMS, it may or may not
find/have/use the plan it created for the previous statement.
ok I undestand now..... I hope.... :).... one more doubt, is the execution plan the same for a preparedstatement than for a plain statement, or oracle treats each one differently???? assuming that the tables doesnt change a los in row numbers??
The plan itself will be the same, presuming there are no parameters. The only question is whether
the DBMS knows to keep it for re-use, and what it would have to do, to find it. Preparing give
the hint to the DBMS to store it and name it and return the name, presumably because the
client wants to re-execute it. Your one-time-per-connection example isn't ideal in that regard.
Assuming ORA-04031 hasn't gone away recently, isn't binding still recommended in any OLTP application?
Which implies, even if you are discarding a session, that you should still aim to use (with some exceptions) PreparedStatements, even if they are discarded after one use.
Melssj5 wrote:Architecture/requirements/design will have more impact. And none of those have anything do SQL (but would have to do with the database.)
Yes I know that in this case it wont affect the overall time. I am trying to understand the whole stuff about using a preparedStatement or trying to understand when its not recommendable using a preparedStatement.
After that if you profile an application and determine that a specific SQL execution is something that needs to be fixed AND it cannot be fixed in any other way (see proceeding including impacts from that on the database itself) then experimenting with different ways of calling might provide some small benefit.
But if you want to improve performance your time is better spent learning how to optimize/analyze the architecture/requirements/design.
Thanks a lot guys, I know that a good design is much better that trying to fiz this minor things..... I am worried about performance, the example I gave was just an example to show my doubt!
The DBMS just plugs and plays. There is no logical distinction between
using the 'batch' API and simply re-executing the prepared statement
in a loop, and in some cases there may be no driver-internal difference
What the, exactly, is sent in a 'batch' if the batch size is 20?
1. Twenty duplicate sql queries with the data substituted in place?
2. One query/cursor 'id' and an array of 20 sets of variables that are substituted by the server process into the query as bind variables?
3. Something else?
I don't have time now to rerun a wireshark test but I seem to recall that previous tests show that there is MUCH less physical traffic over the network when a 'batch' of data is sent.
Yes, depending on the driver, if the batch API is used, instead of sending 20 packets, all meaning
'execute <my_proc>', it may send one bigger packet with 20 'execute <my_proc>' calls. This may
help performance to a degree in some cases, if the DBMS-client API allows it, but the work to
accomplish the actual execution in the DBMS is the same, and the main gain is that the DBMS can
quickly find and execute <my_proc> rather than parsing SQL, looking up tables and columns and
indexes, and generating/finding a query plan for each call, batched or not.