ColdFusion slow database connection to SQL2005

The envionment is CF7.0.2 and SQL Server 2005. It appears that queries are taking longer than usual to complete. Even simple queries can take over a second to execute. The test server has no problem executing the same query in 100ms. I supect there is some configuration issue which means that the database connections are dropped after each query, but I am not sure what is going on. The fact that it all works perfectly in test and the same code ran without any problem on CF5 and CF6.1 makes me sure there is somthing off with the configuration but I can't find it and the DBA isn't able to see any problem on the SQL Server either. We are using SQL Authentication rather than NT Authentication. Anyone out there seen somthing like this? Kevin Roche

Run SQL Profiler from a remote computer and see if the queries are taking long to execute on the database server. You want to isolate the problem if possible, and knock out the ColdFusion server or the network connection from the list of possible culprits. It isn't clear from your message where the slowness is occurring. -Mike Chabot On Jan 22, 2008 1:27 PM, Kevin Roche <kevin@objectiveinternet.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Be sure that connection pooling is checked on the datasource in Coldfusion The envionment is CF7.0.2 and SQL Server 2005. It appears that queries are taking longer than usual to complete. Even simple queries can take over a second to execute. The test server has no problem executing the same query in 100ms. I supect there is some configuration issue which means that the database connections are dropped after each query, but I am not sure what is going on. The fact that it all works perfectly in test and the same code ran without any problem on CF5 and CF6.1 makes me sure there is somthing off with the configuration but I can't find it and the DBA isn't able to see any problem on the SQL Server either. We are using SQL Authentication rather than NT Authentication. Anyone out there seen somthing like this? Kevin Roche

Kevin Roche wrote: > The envionment is CF7.0.2 and SQL Server 2005. > > It appears that queries are taking longer than usual to complete. Even simple queries can take over a second to execute. The test server has no problem executing the same query in 100ms. Is there a difference between both servers in the setting for "Send string parameters as Unicode"? Jochem

Thanks guys I will check both those suggestions.

I have checked and the settings are exactly the same on live, and test servers. Maintain connections across client requests. is checked Enable Unicode for data sources configured for non-Latin characters. is unchecked All the other parameters under the advanced tab are the same. In fact they have not been changed from the defaults. Anyone have any other ideas?

> Anyone have any other ideas? Broken or bad router, network etc. etc. ? Does a (flood) ping indicate health there ? -- Tom Chiverton Helping to autoschediastically revolutionize compelling metrics on: http://thefalken.livejournal.com **************************************************** This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB.  A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP.  Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged.  If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents.   If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com.

Well I have just replaced the JDBC Drivers with the latest version 3.5. This has reduced the time taken by a huge amount. There is no mention in the docs of there being anything that would do that. The same change on the test server made no difference. Kevin

Kevin, were you using ODBC socket connections, or ODBC connections?  Or, which driver exactly was it that you selected in the CF admin?  I'm having the same problems that you are, and I'm not sure that the upgrade of the JDBC drivers is going to fix the problem.  Thanks! > Well I have just replaced the JDBC Drivers with the latest version 3.5. > This has reduced the time taken by a huge amount. There is no mention > in the docs of there being anything that would do that. > > The same change on the test server made no difference. > > Kevin

is the data source setup as a native sql driver or ODBC? I've run into speed problems in the past when we had to use the ODBC instead of the native sql one. also, if you go into the mgmt. studio and run the same query in the query tool is the response immediate and much faster? If not, maybe check the tables themselves for proper indexing?