Skip to main content
A
Anonymous
June 25, 2008
Answered

query takes 60 seconds to run on DEV and 1 second on PROD!

  • June 25, 2008
  • 4 replies
  • 899 views
i cannot figure this out. its been going on for weeks now. i have the datasource correctly setup to a Oracle database. a simple "select * from web_adm.coordinates" on my PROD box returns results in 1 second. on my DEV box the same thing takes 60+ seconds. they are both querying the same oracle database table. i've uninstalled Coldfusion and re-installed it on the DEV box... even uninstalled and reinstalled Apache on the DEV box. still no luck. as for my debugging settings, they are all EXACTLY the same. same exact machines as well so no hardware differences. i'm finding that databases with indexes are taking longer. what could possibly be the problem? please help!
This topic has been closed for replies.
Correct answer
i did notice that my JDBC drivers were different. i did an update and for anyone that wants to compare you can check this in the CF admin or use this code:
<cfset jdbcDriver = CreateObject("java", "macromedia.jdbc.sqlserver.SQLServerDriver")>
<cfoutput>
#jdbcDriver.getMajorVersion()#.#jdbcDriver.getMinorVersion()#
</cfoutput>

after updating everything... still have the problem. so i contacted someone here at my office that troubleshoots networks... looks like IT did some funky stuff with the machine name and the port/switches.. he made the changes and everything is great now. thank you all for your help!
-Matt

4 replies

A
Anonymous
Correct answer
June 26, 2008
i did notice that my JDBC drivers were different. i did an update and for anyone that wants to compare you can check this in the CF admin or use this code:
<cfset jdbcDriver = CreateObject("java", "macromedia.jdbc.sqlserver.SQLServerDriver")>
<cfoutput>
#jdbcDriver.getMajorVersion()#.#jdbcDriver.getMinorVersion()#
</cfoutput>

after updating everything... still have the problem. so i contacted someone here at my office that troubleshoots networks... looks like IT did some funky stuff with the machine name and the port/switches.. he made the changes and everything is great now. thank you all for your help!
-Matt
A
Anonymous
June 26, 2008
kronin,
the result set is about 40,000 records. how can i determine the link speed between servers and the database? yes the datasources are configured exactly the same. using jdbc drivers. the other strange thing is the DEV box is in my office... and if i use another computer in the same office location with another coldfusion service running... the query is still as fast as PROD. the only one that doesnt work well is the DEV one.
K
Kronin555
Participating Frequently
June 26, 2008
> the other strange thing is the DEV box is in my office... and if i use another computer in the same office location with another coldfusion service running... the query is still as fast as PROD

Then that rules out the idea that it's a network speed difference.

Are they all using the same version of CF? same version of the Oracle JDBC driver?
K
Kronin555
Participating Frequently
June 25, 2008
BosDog, again I will ask:

How big is the result set? What's the slowest link (network connection) between the production server and the database server? What's the slowest link between the database server and your development box?

If the result set is large, and the network connection between PROD and dev Oracle is significantly faster than the network connection between DEV and dev Oracle, that could be the issue.

Are the datasources configured the same? (native vs. jdbc vs. odbc/jdbc)
K
Kronin555
Participating Frequently
June 25, 2008
How big is the result set? What's the slowest link (network connection) between the production server and the database server? What's the slowest link between the database server and your development box?

Possibly stupid question, I'm assuming both dev and production are talking to the same Oracle database? Are the datasources configured the same? (native vs. jdbc vs. odbc/jdbc)
P
paross1
Participating Frequently
June 25, 2008
I am assuming that you have a different dev verses prod database so you might try running an explain plan with your query directly on each db (using SQL Plus, etc.) and see what your optimizer is doing with each.... could be significantly different. Even with identical indexes, you might not have run DBMS_STATS.GATHER_SCHEMA_STATS() on one database, etc., so you could be running a full table scan because that is what your optimizer thinks it should be doing. In other words, look at Oracle instead of CF if the databases are indeed different.

Phil
A
Anonymous
June 25, 2008
hi i appreciate the responses. as a test i created 2 files called test.cfm (one on DEV and the other on PROD)... the table is created in the Oracle DEV environment (i havent even ported to PROD yet). so on my PROD box i setup a datasource to point to Oracle DEV. this way both test.cfm files are using the exact same sql syntax and pointing to the exact same database table in oracle... but the DEV box is taking 60 seconds while PROD takes under 1 second. ??? any ideas?
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices