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
