db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nick Puz <nick.de...@me.com>
Subject Stored Prepared Statements? -- Thoughts on exposing StorablePreparedStatements to user/external statements
Date Wed, 15 Apr 2009 19:03:22 GMT
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.


View raw message