Mahler Thomas <thomas.mahler <at> itellium.com> writes: > > Subject: Dynamic statement, urgent > > > > > > Hello, > > OJB uses prepared statements to run select statements. Is > > there a way to > > convince OJB to use dynamic statements instead? > > There are two options: > 1. > On the class-descriptor > you can specify three attributes: > > insert-procedure > update-procedure > delete-procedure > > to specify your own stored procedures for the OJB kerel operations.
Stored procedures are no option for us. > > 2. You can plugin your own derived version of the SqlGenerator component > to let it generate dynamic SQL instead. > This will be a lot of work though! Thanks, I'll have a look at that. There was already an idea about wrapping the jdbc-driver, similar to p6spy, which builds dynamic statements also when a PreparedStatement has been requested. But of course it's much better to avoid the problem rather than fix it in another layer (with loads of pitfalls). So I prefer your approach unless it's too much work to do. Do you have some more hints or may I get back on you if there are further questions? > > > We are about to refactor a running system and want to use OJB > > instead of > > coded sql-statements. The problem is, that a prepared > > statement (=static > > statement) seems to limit the possibilities of the database > > query optimizer. > > We have quite big tables where each query *must* use an > > appropriate index. > > A prepared statement is compiled and optimized without any concrete > > information about the values of the query. As a result, the > > database (DB2 in > > our case) generates different access-pathes what is killing > > our application > > (query execution takes 30-40 minutes instead of around 0.4 secs). > > The reason seems to be a bad value-distribution: We have > > around 2,5 million > > values each appearing once, and one value appearing 11 > > million times. If a > > query uses that one value in a where-condition, the index on > > that column > > doesn't help at all (ends in a almost full table scan). The > > optimizer seems > > to recognize this in a dynamic statement and chooses another > > index, but > > naturally not in a static one. > > We cannot change the indizes. Runstats are uptodate and ran > > with option > > 'with distribution and detailled indexes all'. > > How can the dynamic statement be executed faster if the where-clause contains the same 11 million times value? > Are there any other columns used in the where clause? Yes there are. And having the values available, the optimizer seems to be intelligent enough to 'see' this problem and uses the other index (I guess here the runstat option 'with distribution' gets into business). > > Are there any options to use a different runstats or use different optimizer options? > Maybe a cost based optimization could help to reduce the problem. According to IBM there are no more possibilities. Static statements are optimized and ready to execute (optimized query and access plan are saved, only host-variables are replaced prior to execution). There is no optimization anymore when running the real query (what is the core problem). The only time a query is newly optimized is when statistic information have changed (i.e. after running runstats). Norbert. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
