Hi Nick, You can find information about how selectivity and cardinality statistics are maintained by Derby at:
http://db.apache.org/derby/docs/dev/tuning/tuning-single.html#ctunstats18908 Cheers --francois On Fri, Apr 17, 2009 at 4:05 PM, Nick Puz <nick.de...@me.com> wrote: > 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... > > -Nick > > On Friday, April 17, 2009, at 07:09AM, "Knut Anders Hatlen" > <knut.hat...@sun.com> wrote: > >Nick Puz <nick.de...@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 > > > > >
