Telerik OpenAccess ORM Q3 2011 – Connection Pooling in Oracle environments

With the Q3 2011 release of Telerik OpenAccess ORM we introduced three connection pool types which are explained in this blog post. In this blog post we will have a look at the connection pooling behavior of the Oracle driver and what setting to be used when we want to use Telerik OpenAccess ORM with Oracle databases.

Which setting for Oracle and why?

When we want to use Oracle as our backend, it is advisable to use the connection pool type Open Access Connection Pool with OpenAccess Connection Management or the Ado Connection Pool setting. When using one of the two settings, we are able to use  the connection pooling of the underlying Oracle driver. To enable the driver connection pooling we can set the ‘Pooling=true;’ property in our connection string. But why can’t we use the OpenAccess Connection Pool setting. This is because when the Connection String setting ‘Pooling=false;’ is set, which is the case when using the OpenAccess Connection Pool, also the internal prepared statement cache of the Oracle driver is deactivated.

Why is the prepared statement cache so important?

When an statement is executed against the database the first time, it is parsed first, in order to calculate an execution plan for it and then it is executed in order to fetch the values based on the execution plan. When using binding variables, as Telerik OpenAccess ORM does, the execution plan of a statement can be reused, in order to avoid some extra work and gain more performance.

But what we have discovered is, that when the Connection String setting ‘Pooling=false;’ is set, which is the case when using the OpenAccess Connection Pool type, then also the prepared statement cache of the driver is deactivated. The result is, that every time a statement is executed, it is parsed every time and the execution plan is calculated. This will lead, as we have observed in our performance tests, to performance decreases.

For instance lets say we have 1 statement and execute it 1000 times. With the deactivated prepared statement cache, the statement is parsed 1000 times instead of only once. See the following trace output.

   1: SELECT COUNT(1)  EXPR1                  

   2: FROM

   3: "category" a 

   4:  

   5:  

   6: call     count       cpu    elapsed       disk      query    current        rows

   7: ------- ------  -------- ---------- ---------- ---------- ----------  ----------

   8: Parse     1000      0.12       0.10          0          0          0           0

   9: Execute   1000      0.15       0.16          0          0          0           0

  10: Fetch     1000      0.25       0.31          0       3996          0        1000

  11: ------- ------  -------- ---------- ---------- ---------- ----------  ----------

  12: total     3000      0.52       0.58          0       3996          0        1000

Having the pooling set to true we will have 1000 fetch calls but only one parse call which is what we want. See the following trace output.

   1: SELECT COUNT(1)  EXPR1                  

   2: FROM

   3: "category" a 

   4:  

   5:  

   6: call     count       cpu    elapsed       disk      query    current        rows

   7: ------- ------  -------- ---------- ---------- ---------- ----------  ----------

   8: Parse        1      0.00       0.00          0          0          0           0

   9: Execute   1000      0.17       0.13          0          0          0           0

  10: Fetch     1000      0.31       0.45          0       4000          0        1000

  11: ------- ------  -------- ---------- ---------- ---------- ----------  ----------

  12: total     2001      0.48       0.58          0       4000          0        1000

As we can see the new Connection Pool types introduced in the Q3 2011 of Telerik OpenAccess ORM will give us the flexibility to use the features of the underlying driver, as well as the Connection Management features of Telerik OpenAccess ORM.