Re: Precompiled statement?

joeNOSPAM@xxxxxxx wrote:
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.

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
.

Relevant Pages

  • Re: [PHP] PDO statement/resultset
    ... So logically it would make sense to make use PDO wherever possible, however, one of PDO's design decisions was to make a prepared statement object also a result set (another question: ... a script can not make use of the a statement more than once without completely fetching all the data it needs from the first query, before executing the next. ... From what I understand the only solution would be to prepare a new statement for each query, even for queries with the same structure which defeats the purpose of a prepared statementIs there a solution that is a bit better? ...
    (php.general)
  • PDO statement/resultset
    ... So logically it would make sense to make use PDO wherever possible, however, one of PDO's design decisions was to make a prepared statement object also a result set (another question: ... a script can not make use of the a statement more than once without completely fetching all the data it needs from the first query, before executing the next. ... A likely scenario is that the prepared statement is executed and then before the script is done with processing the result, the same statement will need to be executed again, also using the result set from that statement. ...
    (php.general)
  • Error with simple maintenance plan
    ... Executing the query "EXECUTE master.dbo.xp_delete_file ... I really haven't done anything too fishy with this maint plan; ...
    (microsoft.public.sqlserver.tools)
  • Re: different plan based on connection
    ... boil down to differences in the libraries you used to connect to SQL Server, ... I noticed that when executing sql from VB via ADO2.8 that the query plan ... was different when executed from SSMS and C#. the vb plan was a parallel ...
    (microsoft.public.sqlserver.programming)
  • Re: DB Maintenance Plan Problems
    ... The jobs have all the information for executing the sqlmaint tasks; ... they will also refer to a plan name. ... same databases exist on the other server, you most certainly do not have the ... The job was created but executing seems not to work. ...
    (microsoft.public.sqlserver.programming)