🚀 go-pugleaf

The answer to #1 is: "It depends".
    A prepared statement *can* save substantial time (overall) if a
particular query is run often (or several times in a row).  When you prepare
a statement, the DB server pre-works the execution plan (and often runs it
through the query optimizer).  Then, when you execute the prepared
statement, the server already has a [optimized] execution plan for the
query, eliminating *at least* some of the work (perhaps alot of work for
complex SQL statements).
    The downside of prepared statements is that they consume DB server
resources.  Most database instances will have a max number of prepared
statements (either per-user or per-database instance).  If you over-use
prepared statements you'll quickly run into a "resources exhausted"
situation.  This will also happen if you forget to release prepared
statements.  Remember that these are kept *on the server*, so until you
explicitly tell the server to release the prepared statement, the server
will hang on to it, anticipating that you will be using it again in the
future.
    Prepared Statements can be great -- especially for mass (bulk)
transactions and such, with bound parameters.  For example, you wanna upload
50,000 records:  prepare a statement such as "INSERT INTO customer (id,
name, address, phone) VALUES (?,?,?,?)".  Then begin binding variables and
executing the prepared statement.  Then, release the prepared statement when
you're finished!

The answer to #2: No.
    Do NOT create a prepared statement every time you want to execute a
query.  Preparing a statement (a) takes time, (b) uses valuable DB
resources, and (c) should be treated as a protected resource.  Use a normal
statement for your on-the-fly queries.  Use a prepared statement for
often-executed queries.  Store the prepared-statement in an instance (or
static) variable so that you can reference it on the next call.  By creating
a "new" PreparedStatement (event if its identical to the previous one), your
DB will grind through the process of preparing the new statement (killing
any performance improvement) AND use up another prepared-statement block of
DB memory.

Hope this helps,
Andy

"TBerry" <tberry@vhb.com> wrote in message news:3C1E52A9.80F240C@vhb.com...
> I have two questions on PreparedStatements:
>
> 1. For basic queries (e.g. SELECT a, b, c, d FROM table Z ORDER BY a),
> is there any benefit to making this a PreparedStatement versus a
> Statement?
>
> 2. My web app. is an interactive GUI front end to a database. SELECT,
> INSERT and UPDATE queries are routinely executed. I have not yet
> implemented connection pooling but will be. Regarding executing a
> PreparedStatement, after the execution, do I recreate the statement with
> new values (if any), each time? That's what the documentation seems to
> imply. And since I've no connection pooling yet, I'm closing each
> Statement/PreparedStatement when it's completed. If it matters I'm using
> Microsoft's type 4 JDBC driver.
>
> Thank you
> Tom Berry
>