SQL Problems - can this be right?

[bmyers]

We are trying to a solve a SQL read problem since moving to CF MX and MSSQL
2003.

It now takes 30 seconds to read our databases, while on the prevision CF 5,
MSSQL2000 it was taking 1/10 of a second.

Our web host support team advised us as follows:

"CFMX and CF5 has different CFQUERY statement cache behavior;

in CF5, the query returns only the first 100 rows to the client
(MAXROWS=100), however, in CFMX it returns the entire row (140K records)
and then displays only the first 100.

The advantage of the second method can be that you can continue to work
with the cached results in follow-on queries; however, in this case it
is causing tremendous traffic."

In effect, each time you read a SQL database in CFMX, it reads and returns the
entire database? Even if you are looking for just one record, or a limited
record set?

So on our large database, we can expect 30 seconds each time we read any part
of the database for any reason?

Surely, this can not be the case.

Is there anyway to resolve the problem (other than go back to CF5)?

Desperate for a solution,

Bill

[Kronin555]

> In effect, each time you read a SQL database in CFMX, it reads and returns
the entire database? Even if you are looking for just one record, or a limited
record set?

No. It returns the entire results of the query. If your query is a "select *
from table", then yes, it will return that whole table.

[bmyers]

So basically it means you can no longer rely on Cold Fusion (MX or later) for
sites that have large databases?

As a result of 'upgrading' to MX, our super fast site has now been reduced to
a crawl, with 30 second read times each time the database is accessed.

Same database takes 1000 times longer (literally) to find and read a few
records than it did with CF5.

See anything wrong with the code below?

<CFQUERY name="searchcontent" datasource="#MGDatabase#" maxrows='100'
blockfactor="100" username="*****" password="*****">
SELECT page_number, headline, byline,
department,html,alternate_link,show_rss,active_dat e,image1,access,align1,active_
date
FROM content
WHERE (page_number > 2 And (status = 'a') AND (portal_show = 'y' or
portal_show = '1' or portal_show = '2') AND (purge_date >
#CreateODBCDate(check_today)#))
ORDER BY active_date DESC, content_rank ASC, Page_number DESC
</CFQUERY>

This has worked flawlessly and fast for years. But no more.

If this is the case, what's the point of upgrading (or continuing to use Cold
Fusion)?

Is there any logical reason that CFMX does this?

Bill

[Kronin555]

Have you run this query directly in MSSQL and ensured it's executing in a
timely manner? that is, are you sure it isn't a database issue?

Your code looks fine, although specifying a maxrows of 100 kinda defeats the
purpose of having a blockfactor of 100...

Regarding blockfactor:
[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b19.htm#wp1102316[/url]
"Might not be supported by some database systems."

Are you all updated on CFMX with all the latest hotfixes and patches? Are you
on 6.1 or 7? What database driver are you using?

[bmyers]

The site is on a dedicated server at intermedia.net.

The control panel shows that it is running CF 6.1.0.0. The support team at
intermedia says it is CF 6,1,0,83762.

The server is on a Windows 2003 box, and the database is MSSQL (2003?).

How can I test the sql server with a SQL query? And what could cause a MSSQL
2000 database that ran perfectly on the older server, run slowly on the new one?

Would there be any database design changes required whem moving from CF5 to
CFMX?

Thanks for your help.

Bill

[Dan Bracuk]

If you have direct access to your db, you can run your query in Query Analyser
(if you have it installed)

Your query will probably run a bit faster if you took away some of the
brackets in your where clause and replace this
AND (portal_show = 'y' or portal_show = '1' or portal_show = '2')
with this
AND portal_show in (y', '1', '2')

Something else to check, since you are on a newer version of mssql, is that
your new database has all the indexes the old database had. This could be the
reason for the drastic increase in query response time.



Originally posted by: bmyers
The site is on a dedicated server at intermedia.net.

The control panel shows that it is running CF 6.1.0.0. The support team at
intermedia says it is CF 6,1,0,83762.

The server is on a Windows 2003 box, and the database is MSSQL (2003?).

How can I test the sql server with a SQL query? And what could cause a MSSQL
2000 database that ran perfectly on the older server, run slowly on the new one?

Would there be any database design changes required whem moving from CF5 to
CFMX?

Thanks for your help.

Bill

[sdupre]

BMyers,

I don't see this issue using the 3.5 JDBC SQL Server driver (return all rows
to CF and show only "maxrows" rows) when i run a trace.

Are you or your ISP using the ODBC driver? Or an older JDBC driver with 6.1?

There is a quirk that can cause this behavior or "all rows" (disregarding
maxrows in the fetch) with some drivers but again, I don't see it with SQL
Server JDBC and [url]http://www.macromedia.com/go/42dcb10a[/url].

We're fixing this as we speak so all drivers work but due to the way we
process the CFQUERY today, the DataDirect JDBC SQL Server driver acts as you'd
expect - it fetches only the # rows in "maxrows". Let me know if this is
enough or if I have to talk to someone at Intermedia.net.

At the very least, the provider could have suggested several workarounds in
the CFQUERY maxrows rather than saying "that's the way it is".

Ex: (this worked for me too)

Use SET ROWCOUNT
<CFQUERY>
SET ROWCOUNT 100
Select * from table ...
</cfquery>

Another thought. If they installed CF 6.1 and migrated the CF5 settings, the
migration incorrectly sets "String Format" to 0 in the neo-query.xml (rather
than false). This still shows up in the admin as "unchecked" but it's really
as if it were checked. This can cause a table scan for varchar cols in WHERE
clauses on the backend (the long wait) but the fetch is still doing what you'd
expect (bringing back only 100 rows - this is what I saw in the 3.5 driver).
Have them check neo-query.xml for that datasource and/or CHECK/SUBMIT +
UNCHECK/SUBMIT the datasource for the "String Format" field to the setting gets
correctly written in the URL to "false" (sendStringParametersAsUnicode)

Stephen Dupre
Macromedia QA