We are currently running CF11 Update 6 and are connecting to Oracle 11g. In previous upgrades to CF11, we discovered that use of the most recent macromedia_drivers.jar file has caused significant performance problems on queries. As a result, we had rolled back to a previous version of the drivers file and are running with macromedia.jdbc.oracle.OracleDriver version 4.2.
We are now upgrading to oracle 12c and are required to run macromedia.jdbc.oracle.OracleDriver 5.1. However, the peformance is still a problem. I have a sample .cfm file that does a basic query and dumps the query. The execution time on the query on driver 5.1 averages 4000 ms and on driver 4.2 is 200 ms.
Sample .cfm Code:
<cfset variables.strUsername = "dm_full" />
<cfset variables.strPassword = "test" />
<cfset variables.strProtId = "5" />
<cfquery name="qProtTrack" datasource="asthmanet_dsn" username="#variables.strUsername#" password="#variables.strPassword#">
SELECT prot_track_id, prot_track_name
WHERE prot_id = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="3" value="#strProtId#">
AND net_id = <cfqueryparam cfsqltype="cf_sql_integer" value="3" null="no" />
<!--- ORDER BY TO_NUMBER(min_age) --->
Any help is appreciated.
Were you able to resolve this, or are you still in need of assistance?
I don't know if this will make any difference, but what if you put the credentials to your connection in your datasource configuration in CF Administrator? I'm just wondering if it's possible that specifying credentials in your <cfquery> tag is forcing CF to create a new database connection on each request.
-Carl V. - Moderator
Thanks for the response. We are using the new drivers and keeping maintain connections checked in the datasource settings for all datasources but one. The one is left to authenticate to the database. We've found that if you keep the maintain connections checked, then the datasource pool keeps a connection with an old password if the password was just changed.
That makes sense. How often do you change passwords on your database server? When you do, a restart of the ColdFusion server should clear connections/old passwords.
In one of our applications, the users each have a database account and pass their own credentials with each database call. The database forces the password be changed every 90 days. Therefore, restarting the service or clearing the connection pool wouldn't be feasible for each password change.
That also makes sense, and is indeed a good reason to not use "Maintain Connections" for that datasource.
I've found that adding -Dcoldfusion.disablejsafe=true to the JVM arguments resolved the performance issue. Creating a new database connection now consistently takes 300-400 ms.