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 Re: Stored Prepared Statements? -- Thoughts on exposing StorablePreparedStatements to user/external statements
Date Fri, 17 Apr 2009 23:05:52 GMT
 Hi Knut,

Thanks for the response, I'll take a look into the cloudscape docs and how execute is implemented.
I'll also look more at the statement cache to see about persisting it on shutdown and restoring
on startup, so that then it could be done globally for all statements (as an option). Another
alternative would be to let the user specify which to persist as a comment in the sql (as
is done with the optimizer overrides to specify index usage, etc.), possibly as a different
runtime option. The advantage of the first (global) is that apps could take advantage with
no sql changes. Perhaps persist the N most recently used statements...

In terms of invalidating, a crude but simple way would be to have the version of derby stored
with the prepared statements and just to discard them if the version numbers differ. The same
mechanism coudl be used as when deciding to recompile the system stored statements. What do
you think?

Besides version changes the statements should probably be removed/regenerated if the statistics
used to determine the execution plan change. How does derby manage the statistics, are they
automatically kept up to date or does the user have to do something specific? Do you happen
to know what happens with the stored trigger actions, since they should have the same problem.
I'll take a look at the code to try to figure it out in the next few days but just curious
if you or another derby dev knows...


On Friday, April 17, 2009, at 07:09AM, "Knut Anders Hatlen" <Knut.Hatlen@Sun.COM> wrote:
>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
>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:
>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

View raw message