🚀 go-pugleaf

Excellent explanation - Thanks

Andy Blanchard wrote:

> 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
> >