Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of nick.derby@me.com designates
 17.148.16.86 as permitted sender)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; charset=ISO-8859-1
Date: Wed, 15 Apr 2009 12:03:22 -0700
From: Nick Puz <nick.derby@me.com>
To: derby-dev@db.apache.org
Message-id: <157681730964895697200108835802389536937-Webmail@me.com>
Subject: Stored Prepared Statements? -- Thoughts on exposing
 StorablePreparedStatements to user/external statements

Hi Derby Developers, 

I'm evaluating derby for use in the backend of a internet (web+http direct) based service. We have a bunch of mid-tier app server type boxes that all access data mounted on a bunch of nfs filers (so any mid-tier can handle a user request) and so the current thinking is for usage pattern (mimicing what is done in other places in this 5+ year old live system) would be to:
1) lock db directory for user (using symlinks -- atomic nfs op) ... would be done external to derby.
2) open database for the user
3) do operation to satisfy caller's request
4) close db then remove lock.

Unfortunately with this usage model the perf benefits of prepared statements go away (parameters still nicer then encoding for string sql stmts). I've done a bit of performance testing and as expected a ton of time is spent preparing a simple primary key lookup query  (primarily due to opening/reading the many system tables and few indexes on the table), while the execution goes quite fast. 

In digging around the code I saw that the statements used for trigger actions are stored to remove this cost on each action invocation, would it be possible to expose this end user statements. In our case a mode that just keeps a persistent cache of the last N statements would be fine, no need to expose at all at the jdbc/sql level. I'm comfortable making the code change but would like to know before embarking on this the thoughts/advice of experienced derby developers. 

-Nick