Hi Christopher, These are interesting questions. I can give you a somewhat authoritative answer as far as Oracle is concerned. I do not have production-quality experience with SQL Server. 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). So database performance is critical to us too.
What I can say is that we're using JDBC's prepared statements explicitly for Oracle to be able to apply soft-parses rather than hard-parses. I'm not sure what you mean by "ad-hoc" queries...? For this kind of performance, it is very important that the SQL uses bind variables and that two consecutive statements render the same SQL (including the same hash) to Oracle. This means that Oracle keeps an open cursor in the cursor cache, and calculates and compares several execution plans using statistics, SQL baselines, and bind-variable peeking. We found Oracle 11g to be extremely good at this. In order to take advantage of this, you only have to be sure that jOOQ renders the same SQL for the same query every time. In short, PreparedStatements are better than Statements with hard-wired SQL, as you can avoid hard-parses and reuse previous execution plans many times. Whether or not you use jOOQ to transform your java.sql.ResultSet into a org.jooq.Result is irrelevant (unless you have very big result sets). I hope this sheds some light on SQL performance? I imagine, SQL server is quite similar. You may get more hints and options on http://stackoverflow.com I'll reply to your other question in a separate mail Cheers Lukas 2012/2/14 Chrriis <[email protected]>: > Hi, > > In our application, the various functionalities are divided into > services, most of which access the database for related data. The > number of SQL queries that are implemented in total considering all > these services is in the order of 700-1000 queries. Most are ad-hoc > queries and only a limited number of prepared statements. > > Bear in mind that I am far from being an SQL guru, so please correct > any misconception I may have with what I am going to say! > > So, last time I checked, having more prepared statements would reduce > performance of our application. The point is that we execute so many > different queries that we can't keep prepared statements in a pool: > the server would have to maintain the prepared data plan for each > query which does not make sense for such an amount and retaining them > on the client would be a hell too. Also, SQLServer tries to make sense > of ad-hoc queries to have a reusable data plan, which means that if 2 > similar ad-hoc queries happen to be executed twice not too far apart, > the data plan is reused. Moreover, with ad-hoc queries, we do not have > the cost of the creation of the prepared statement, which from what I > read makes a round-trip to the database (well, depending on the RDBMS) > before actual execution. > > It seems to me that jOOQ uses prepared statements exclusively (for > normal SQL statements). Does it mean it has this round-trip execution > for every call? If the statements is prepared and a similar one is > constructed, is the data plan going to somehow be reused or is it lost > due to the reconstruction of the jOOQ DSL chain? Or am I missing > something and there is no such round-trip to start with? Does it have > ad-hoc-like capabilities or would it mean constructing the SQL with > the DSL and then call getSQL() (which means no parsing of the > ResultSet through jOOQ API)? Or is my understanding of the way things > work wrong and you could shed light (especially in the case of Oracle > and SQLServer)? > > Another question: when using fetch, is there a way to get the raw > ResultSet instead of the jOOQ structures? Because for transition > purposes, it is possible that first the request part is migrated and > then the parsing part. The intermediary step would be to obtain the > ResultSet from the jOOQ structure or someting. > > Cheers, > -Christopher
