Hello Christopher, > Thanks for sharing your knowledge of the Oracle database behaviors!
Same to you for sharing SQL Server knowledge >> In my job, we run a >> database with 700k users 2k-10k concurrent sessions and several tables >> with about 1 billion records. There is a total of around 50 Weblogic >> servers querying a single Oracle instance (quite a "classic" setup). > > If all of them execute the same limited set of queries, then the > considerations are different from a handful of users executing > hundreds of different queries. That is true. Some rather complex OLAP queries tend to have similar behaviour as in your setup, when many possible combinations of filtering and sorting may be applied. But these queries are not the normal use-case in our application, which is mostly an OLTP one. > I read that Oracle prepares the statement as part of the first fetch, > which means there is no round trip before actual execution. This is > how it seems to happen on SQLServer though: > http://msdn.microsoft.com/en-us/library/ms175528.aspx > > "Excess use of the prepare/execute model can degrade performance. If a > statement is executed only once, a direct execution requires only one > network round-trip to the server. Preparing and executing an SQL > statement executed only one time requires an extra network round-trip; > one trip to prepare the statement and one trip to execute it." Good to know! On the other hand, T-SQL can be used with two different JDBC drivers. The official Microsoft one (sqljdbc) and an also "official" open source one (jtds). Do these claims hold true for both drivers? I'd be curious to know... >> I'm not sure what you mean by "ad-hoc" queries...? > > I think I read this terminology on SQLServer documentation when > talking about SQL statements in String form (not prepared). I see. So I understood you correctly. "ad-hoc" queries are in fact java.sql.Statement statements with inlined parameters instead of bind variables >> We found Oracle 11g to be extremely good at this. > > That is the part of the joys of working with different implementations > of the same concepts, what is applicable to one may be totally not > recommended on another one! :) Well, sometimes "challenge" is a more suited term than "joy" :-) > Again, I am not a database expert so I may have misconceptions. But if > I am right, then the exclusive approach of using prepared statements > in jOOQ may make it not practical for us to use the fetch methods and > we would have to call getSQL() to switch to external execution. Note that getSQL() renders bind variables as "?". What you would need is Factory.renderInlined(QueryPart). Right now, this might be your only choice, indeed. On the other hand, now that I know this, I think I should add some runtime configuration to the org.jooq.impl.Factory, to decide whether bind variables should be inlined and the query executed as a java.sql.Statement, or whether the current behaviour is more desireable. As a Factory configuration, this setting could be applied on a per-query basis, i.e. if you have 10-20 queries that are executed 100's of times, you could still profit from preparing statements. This is an important improvement for jOOQ, so I'll add this as a feature request to the road map: https://sourceforge.net/apps/trac/jooq/ticket/1145 It should be fairly easy to implement, so expect it soon! Cheers Lukas
