Highlighted

macromedia_drivers.jar performance problems

Explorer ,
Oct 21, 2015

Copy link to clipboard

Copied

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

      FROM asthmanet.protocol_track

     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) --->

</cfquery>

<cfdump var="#qProtTrack#">

Any help is appreciated.

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.

Views

689

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

macromedia_drivers.jar performance problems

Explorer ,
Oct 21, 2015

Copy link to clipboard

Copied

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

      FROM asthmanet.protocol_track

     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) --->

</cfquery>

<cfdump var="#qProtTrack#">

Any help is appreciated.

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.

Views

690

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 21, 2015 0
Most Valuable Participant ,
Feb 01, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 01, 2016 0
Explorer ,
Feb 10, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 10, 2016 0
Most Valuable Participant ,
Feb 12, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 12, 2016 0
Explorer ,
Feb 15, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 15, 2016 0
Most Valuable Participant ,
Feb 16, 2016

Copy link to clipboard

Copied

That also makes sense, and is indeed a good reason to not use "Maintain Connections" for that datasource.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 16, 2016 0
Explorer ,
Mar 03, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 03, 2017 0