All makes sense, thanks guys! We're already detecting slow queries with an ExecuteListener and logging the sql, seems like we just need to stick with that.
On Wednesday, March 19, 2014 1:16:08 AM UTC-7, Lukas Eder wrote: > > I have to agree with Roger. Many useful queries run predicates against > columns that are not indexed. Besides, you always have to carefully weigh > the benefit of an index at querying time against the cost of an index at > DML time. > > Anyway, it certainly makes sense to collect statistics on that matter. If > you're using Oracle, then I can only recommend tools like Oracle Grid > Control, which provides an excellent overview of your top ill-performing > queries. > > If you want to roll your own, you could implement an ExecuteListener that > collects and logs execution plans (and Java stack traces) immediately after > query execution, if query execution took too long. For now, you'll have to > implement this yourself. > > If you want to pursue your original thought, you will be able to implement > such a solution using the VisitListener SPI. This will allow you to > transform / listen on individual QueryPart rendering events to see if any > predicate is being rendered on a column that is not indexed. An example is > given here: > > http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation/transformation-bind-value-abbreviation/ > > Note, though, that jOOQ currently only has unique key information, not > index information. > > > 2014-03-19 0:01 GMT+01:00 Roger Thomas <[email protected] <javascript:>> > : > >> Which database engine are you using? A better profiling/query reporting >> tool may make far more sense to solve your discovery process, but this will >> depend on the database engine you are using. >> >> I can't comment on your system, but I've worked on many systems where >> querying an unindexed column was exactly the right thing to do, often >> having been advised by profiling tools to drop the original index. As such >> it would seem strange to have jOOQ extended in this way as it would have to >> take into account the possible number of rows in a table or the >> distribution of values in a column before trying to decide what is >> ill-advised. >> >> If you are looking to work with the ExecuteListener you may like to >> follow the following link as this indicates that there is an example of the >> ExecuteListener which can output query and time taken already coded, it >> just reports out to the logger and so may not be where you have been >> looking. >> >> http://www.jooq.org/doc/2.5/manual/sql-execution/logging/ >> >> >> >> >> On Tuesday, March 18, 2014 9:25:52 PM UTC, Eric Denman wrote: >>> >>> Hi folks, >>> >>> Wondering if there's a way for jOOQ to tell me when I'm writing an >>> ill-advised/slow query. jOOQ knows about all the relevant indices: I'd >>> love to see a config value that allows me to say "blow up if I run a query >>> on a non-indexed column". We currently try to detect slow queries by >>> hooking up an ExecuteListener and monitoring how long the query takes to >>> run, but it's not foolproof. I thought maybe the ExecuteListener would get >>> some analytics about the query, but I poked around in ExecuteContext and >>> didn't see anything. >>> >>> Anybody know if this is currently possible? Or if it's not, where I >>> should look in the jooq source to try to add it? >>> >>> Thanks, >>> -Eric >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
