Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #13 > unrolled thread

Re: Precompiled statement

Back to article view | Back to comp.lang.java.databases

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: Precompiled statement joeNOSPAM@BEA.com.remove-dii-this - 2011-04-27 15:21 +0000
    Re: Precompiled statement "=?ISO-8859-1?Q?Arne_Vajh=" <=?iso-8859-1?q?arne_vajh=@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000

#13 — Re: Precompiled statement

  To: comp.lang.java.databases
On Apr 22, 11:46 pm, Taras_96 <taras...@gmail.com> wrote:
> 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

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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [next] | [standalone]

#21

  To: comp.lang.java.databases
joeNOSPAM@BEA.com 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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web