Hi Norbert, > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Monday, May 17, 2004 6:49 PM > To: [EMAIL PROTECTED] > 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. 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! > 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? 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. --Thomas > The only way we currently see is to use dynamic statements. > Any ideas are > very welcome. > Norbert. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
