Slow SQL reads after update

[bmyers]

Our web host 'upgraded' our site to CF 6.1, SQL server 2000, and Windows 2003.

We had been running CF5, SQLserver 2000, Windows 2000.

Prior to the upgrade, our site performed flawlessly. Our database querys
were very fast.

But since the upgrade, a sql query to read 100 records will take 20 seconds.
(On the previous server, the same query would take less than .5 seconds).

The web host has no idea what could be causing the SQL database queries to run
so slow, but they blame it on Cold Fusion.

To test the query speed I wrote the following code:

<cfset tstart2 = getTickCount()>
<CFQUERY name="searchcontent" datasource="testdb" maxrows='100' dbtype="ODBC"
blockfactor="100" username="#MGUSER#" password="#MGPASS#">
SELECT page_number
FROM content
</CFQUERY>
<cfset tend=getTickCount()>
<Cfset ttotal = tend - tstart2>
<cfif ttotal GT 0>
<Cfset ttotal = ttotal / 1000>
</cfif>
<cfoutput>Time to read 100 records from Content Table: #ttotal# seconds
<br></cfoutput>

The above shows to read 100 records takes 20 seconds. Same test on a
different server shows the reads to take less than .5 seconds.

Any idea why the new server would be taking 40 times as long to read the same
records?

Thanks for any suggestions you can provide.

Bill

[ksmith]

Ideally, your webhost has upgraded to ColdFusionMX 6.1 updater 1 and not the
original 6.1. Many fixes are in the updater. Also what kind of datasource is
"testdb." Hopefully it is a JDBC connection and not really an ODBC connection
as your cfquery might lead one to believe. CFMX is a java application and JDBC
connections are optimal. If your datasource indicates it is an ODBC socket
CFMX is making a JDBC call to an ODBC client/server, which then makes an ODBC
connection to your SQL server. JDBC will be much faster.

Additionally, your host may want to test the latest JDBC drivers Macromedia
has available at
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=42dcb10a[/url]

If none of this helps, make sure it is not due to a database issue. Test your
query in an odbc query tool like msquery or sqlcon.

[bmyers]

Ken,

Thank you for your reply.

The web host control panel shows the version of Cold Fusion as 6.1.0.0.

Is there any way to tell by the version number if the updater has been
applied? Or any test I can run to check the updater?

Regarding the datasource type - the only option provided by the web host
(intermedia.net) is ODBC. Behind the scenes this may be JDBC, but know one at
intermedia.net seems to know.)

Thanks again for your reply,

Bill

[sdupre]

This might be an old 3.1 JDBC driver DNS lookup bug (trying to find the SQL
Server server).

Ask them what version of the JDBC drivers they have (macromedia_drivers.jar).
Open up the .jar with winzip and look at sqlserver.properties.

They can verify this hang issue with a stack trace. (technote 18339).

The workaround is (if they use an IP in the datasource) to add an entry to the
HOSTS file to identify the SQL Server or to upgrade to the latest drivers
(3.5's are on /support/coldfusion). Ideally, get a stack trace. If you have
a 20 second hang, it should be easy enough to get a stack trace during the
hang. Paste it into here or send it to me.

(2nd possibility doesn't sound like the problem. I'm looking at the example
you have and see no WHERE clause or I would have suspected the "unicode" switch
in the datasource (JDBC/SQL Server) ignoring indexes on varchar columns. That
switch is very important with SQL Server - ie leaving it OFF if your where
clause has VARCHAR columns)

Stephen Dupre
Macromedia QA