db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Stored Prepared Statements? -- Thoughts on exposing StorablePreparedStatements to user/external statements
Date Fri, 17 Apr 2009 14:09:15 GMT
Nick Puz <nick.derby@me.com> writes:

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

Hi Nick,

I think you're right that it should be possible to build something on
top of the stored prepared statements to get what you need. Cloudscape
(Derby's predecessor) used to give you that functionality with CREATE
STATEMENT and EXECUTE STATEMENT. The CREATE STATEMENT statement was
removed from the code before it was donated to Apache, but EXECUTE
STATEMENT is still there, so you can actually still do this to execute
one of the statements stored in SYS.SYSSTATEMENTS:

PreparedStatement ps =
       conn.prepareStatement("execute statement sys.\"getSchemas\"");
ps.setString(1, "%");
ps.setString(2, "%");
ResultSet rs = ps.executeQuery();

The old CREATE STATEMENT syntax is described here:
http://www.dwfa.ca/Library/Java/Cloudscape/v3.6.1/doc/html/coredocs/sqlj15.htm

My guess is that it was removed because the SQL standard doesn't define
a syntax for storing compiled statements (as far as I'm aware). If we
find a solution to the problem with the lack of a standardized way to do
it, I think such a functionality would be useful. (At least, I don't see
why persisting user-defined statements should be less useful than
persisting the meta-data queries, which we already do.)

Persisting the statement cache on shutdown is an interesting thought,
and it would be transparent to the users. It should probably be optional
so that we don't start writing lots of data on shutdown for users that
don't need/want it, and I'd guess there would be some upgrade concerns
too. I think I'd prefer a per-statement solution, though, as it would
give the user better control over the behaviour.

-- 
Knut Anders

Mime
View raw message