slow queries from CF 5 to CF 7

[dr_bozak]

I am transferring from CF 5 to CF 7.
I moved one site, and I noticed that on CF 5, with an Access DB as the
datasource, the page takes 20 ms total execution time.
On the cf 7 machine, same code, a local copy of the db, the page takes 143 ms.

The machines are on the same network, from the same isp.

what would cause cf 7 to serve pages 7 times as long on a simple query page?

server 1:
win2k
cf 5

server 2:
win2003
cf 7

access dbs on both.

I'm hoping that the rewrite from C to java didn't slow down the db connections.


If anyone has any posts or links, I'd surely appreciate it.

[CF_Oracle]

Check in CF Administrator "Report Execution Times" and you will see details. Maybe it's not query but JDBC or Application.cfc or something else takes more time.

[CF_Oracle]

Check in CF Administrator "Report Execution Times" and you will see details. Maybe it's not query but JDBC or Application.cfc or something else takes more time.

[dr_bozak]

Thanks for the tip. I have that checked.
I had it on classic, but when I changed it to dockable, I could see that the
query, was taking the time.

In this case the db is access.
It is not on a networked drive.
I have installed the most recent update offered from mm.
going to install here in a bit a hot fix from mid october.

I'm just puzzled of why CF is taking 15 times as long to run a query.
same db, same code. SIMPLE query.
today ...
10 ms CF 5
173ms CF 7

How could I determine what is preventing CF from quickly accessing DB's as it
once did?


thank you

[Mountain Lover]

My experience was similar...except on CFMX 6.1...under the same load as
on CF5 (or less) CFMX/JRUN would just stop serving pages.
It was never determined exactly what the problem was, but I wrote up a
page and a half about what I thought it was. My conclusion...don't use
the Microsoft Access driver, use the Microsoft Access w/unicode driver.
The first uses the jdbc:odbc bridge...a type 1 jdbc driver which is only
capable of one connection at a time. The unicode driver uses the izmado
jdbc driver...I think it's type 2 driver... so slightly better than the
jdbc:odbc bridge. (The end result however was to move the database to
MySQL...it's still to be determined if this will be "better".)

The speed thing...we also noticed the Access queries were
slower...although we didn't have the time or resources to test the
differences (the server died and the order was given to physically move
the new server to headquarters). The queries are MUCH faster in the
MySQL database now!

HTH
Tim
--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[Kronin555]

Or better yet, rather than just not using a specific driver for Access, don't
use Access at all.

Access is not designed for this kind of thing. Access is designed as a
single-user local database. If Access was meant for this kind of use (backing a
database-driven web application), Microsoft would have no need for SQL Server.

There are many other alternatives for you. If you need free, I recommend
looking at PostgreSQL (which now has a native Windows port since version 8.0)
or MySQL (in that order). If you want point-and-click and wizards, buy SQL
Server.

[dr_bozak]

Thank you for the help. While I agree that access is slow, and not for heavy
load sites, I have been succesfully using it for years with CF 5 on light load
sites.

some of the sites I will port to MSSQL, but was just concerned that my CF 7
box had been released to me by my isp with wrong settings, or other processes
were interferring with it.. I just had never read in the manual that speed of
queries between 5 and 7 with an access db would be 15 times slower ( which is
what I am expereiencing)

I've tried all the different database types I could ( access, access with
unicode, and ODBC), and all of them have query exectuion times 10 - 15 times as
long as the same query of the same db from cf 5.

So, I guess what I am really trying to figure out ,,, is has CF lost it's
ability to run fast using an access DB?

I mean, Access or not, it shouldn't take CF 7 180ms to query a flatfile db
that has 100 records in it.
It only took cf 5 10 ms to run the same query on a slower machine.

thanks for all the input everyone has provided.

[dr_bozak]

I just tried a query against an SQL db, and it is way faster (even though the
DB is on a different machine).

I guess cf 7 is just slower than dirt when dealing with an Access DB.
Now, I recognize that access is not meant for doing this sort of thing, but
damn, for 5 years now I have used access to prototype and in some cases deploy
apps (which have worked GREAT and FAST).



it's really too bad performance has GREATLY SUFFERED from CF 5 to CF 7 for
this usage (hooking up to an mdb).

looks like Cf is on its way to becoming another piece of crappy bloatware.
Time to learn PHP I guess. thanks marketing dweebs, you are the scum of the
earth. If I ever see you, I will feed you aspartame.

CF 5 worked so much faster than CF 7 for getting to an access DB.
(17 times as fast to be exact)

Classic ASP is faster now than CF 7. Good job MM. Yoo blew it.

Guess I won't be paying those liscencing fees since the CF server is slower
now. maybe its time for blue dragon (Which probably runs circles around CF 7)


Great .. thanks.

[dr_bozak]

okay ...
so now I have CF 7 moving half as fast as cf 5.

CF 5 .. access DB = 50 ms
CF 7 ..;. access DB - 188ms

CF 5 SQL DB - 30 ms
CF 7 SQL DB - 100 ms

so .. with CF 7 AND MSSQL, this is half as fast as CF 5.


so .. someone from MM please tell me I have something confiugured incorrectly,
or I'll just sell my copy of cf 7 and move to some non piece of junk bloatware.

[dr_bozak]

did I mention I hate marketers?

[ksmith]

What kind of datasource is your ms sql DSN - odbc socket or ms sqlserver in
cfadmin description. If it is ODBC socket you are going through coldfusion's
odbc client/server to odbc to mssql. If it is jdbc, you are going over the
wire direct to mssql and it is much faster. Also, your first connection is
always slower due to the actual connection. Follow on queries should be faster

I just tested a few queries on my cfmx7 and cf5. I had exactly the same times
(16ms) returning thru jdbc/cfmx7 and odbc/cf5. I did note queries returning
CLOBs were slower on cfmx7 - 46ms vs 15ms on cf5. Still 46ms seems fast enough
to me.

[dr_bozak]

I had my isp set up a version of my test site on one of their CF boxes, and the
same results.

CF 5 is slower than CF 7 in running queries.
doesn't matter if its MSSQL, or Access ...

If anyone besides a ignorant marketer can show me some metrics that prove
otherwise, I'll leave it at this for this post.

don't waste your money on a cfmx upgrade from 5. it is much slower for DB
access than CF 5.

thanks MM for f***ing up ColdFusion. Im sure jeremy allaire is laughing all
the way to the bank.

bye bye CF. it was nice while it lasted.
maybe someday MM will snap to and rewrite the jdbc drivers so they aren't
almost unusably slow ....

until then

Blue Dragon, PHP, or hell ... .NET. anything has got to be better than a 200
ms run time to get 100 records.


a former CF enthusiast.

[dr_bozak]

Ken,

Thank you for your response. I do appreciate it. I am quite frustrated with
the slowdown I am experiencing between CF 5 and CF 7. Up to this point, I have
totally happy with CF and how snappy it has been. Now I see a bunch more
features, a rewrite from C to Java, and performance suffers.


What kind of datasource is your ms sql DSN - odbc socket or ms sqlserver in
cfadmin description. If it is ODBC socket you are going through coldfusion's
odbc client/server to odbc to mssql. If it is jdbc, you are going over the wire
direct to mssql and it is much faster. Also, your first connection is always
slower due to the actual connection. Follow on queries should be faster

* I tried both connections. they are both equally slow.


I just tested a few queries on my cfmx7 and cf5. I had exactly the same times
(16ms) returning thru jdbc/cfmx7 and odbc/cf5. I did note queries returning
CLOBs were slower on cfmx7 - 46ms vs 15ms on cf5. Still 46ms seems fast enough
to me.

* agreed if the difference was between 15 and 45, I would be curious, but not
enough to post in a support forum.
the difference I noticed was between 30 ms and 183 ms (first query was a
speedy 4825 ms on CF 7)
my concern is if a simple query takes 200ms, what happens when I see any sort
of concurrency or traffic?
100 concurrent hits * 200 ms = 20 seconds for a page to get served ... OUCH.

that ain't exactly high performance.


maybe their are other conditions on this machine that are interferring with CF.
1> slow drive?
2> other services running?

Are there any other services, that are know to slow down CF?

I have also applied the hotfix and the update released on 9/28 from MM.

[dr_bozak]

Ken,

Thank you for your response. I do appreciate it. I am quite frustrated with
the slowdown I am experiencing between CF 5 and CF 7. Up to this point, I have
totally happy with CF and how snappy it has been. Now I see a bunch more
features, a rewrite from C to Java, and performance suffers.


What kind of datasource is your ms sql DSN - odbc socket or ms sqlserver in
cfadmin description. If it is ODBC socket you are going through coldfusion's
odbc client/server to odbc to mssql. If it is jdbc, you are going over the wire
direct to mssql and it is much faster. Also, your first connection is always
slower due to the actual connection. Follow on queries should be faster

* I tried both connections. they are both equally slow.


I just tested a few queries on my cfmx7 and cf5. I had exactly the same times
(16ms) returning thru jdbc/cfmx7 and odbc/cf5. I did note queries returning
CLOBs were slower on cfmx7 - 46ms vs 15ms on cf5. Still 46ms seems fast enough
to me.

* agreed if the difference was between 15 and 45, I would be curious, but not
enough to post in a support forum.
the difference I noticed was between 30 ms and 183 ms (first query was a
speedy 4825 ms on CF 7)
my concern is if a simple query takes 200ms, what happens when I see any sort
of concurrency or traffic?
100 concurrent hits * 200 ms = 20 seconds for a page to get served ... OUCH.

that ain't exactly high performance.


maybe their are other conditions on this machine that are interferring with CF.
1> slow drive?
2> other services running?

Are there any other services, that are know to slow down CF?

I have also applied the hotfix and the update released on 9/28 from MM.

[Kronin555]

dr_bozak,

Did you see this thread?

[url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&thread[/url]
id=1080411&enterthread=y

sdupre points out some things you can check in that thread, especially this
part:

"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 datasource
(rather than false). The driver ignores the 0, and uses its default setting
(true)

This still shows up in the admin as "unchecked" but the driver sees this as if
it were checked (true).

sendStringParametersAsUnicode=true ("String Format") 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 after the LONG wait (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 so the setting gets correctly written in the URL to
"false" (sendStringParametersAsUnicode)

This performance issue (due to the bug in migrating the SQL Server datasources
correctly and setting SendStringParametersAsUnicode=false) is very common with
migrations with SQL Server ODBC->JDBC and is the first thing I suspect."

[sdupre]

Why don't you send us your database and template so we can see what you're
talking about?

Were you using the same MS Access driver on both CF5 and CFMX? Your post
says they were 2 different machines

>> On the cf 7 machine, same code <<

What version is your MS Access database? 97 or 2000? It doesn't matter
which I just want to know what we have to set up here to see this.

Also, send over your neo-query.xml for CFMX and point out the MS Access
database datasource. Send the database over as-is too.

Stephen Dupre
Macromedia QA
[email]sdupre@macromedia.com[/email]

[dr_bozak]

sounds great. I would greatly appreciate it.

I will send the requested data to you in a bit.

as I mentioned, I really have liked working with cf. just a little frustrated.


thank you very mcuh.

[dr_bozak]

I have sent over the db and requested files via email to MM.
The DB in question is an Access2000.

Now, I recognize Access is not for backend web applications, but cf has
elegantly handled using it as such for the past 5 years with GREAT speed. (A
long running page in my view is over 100ms, from what I have experienced with
CF 5)
Maybe I got spoiled with the uber speed of 5 for my very simple tasks
(shopping carts, picture galleries, etc).

Just to set it straight. I love CF. I defend it every chance I get as some PHP
freak, or an ASP dweeb says its slow. (yeah 10ms for 200 records is slow ..
HA!). Now though, CF 7 is just CREEPING.

More features = worse performance?

I surely hope not.

I'll post updates to this issue so others experiencing same issue can see when
it gets resolved.


Many thanks to everyone reading these posts.

[dr_bozak]

Kronin555,

I did not see that thread, but will definitely read it and see if something
mentioned fixes my issue.
Thank you very much for responding to this post, and providing a link.

Many thanks to all who have responded. I really appreciate it.

[dr_bozak]

ahhhh .. .SPEEEED.

Here is what I did (thanks to all the kind people who responded and MM staff)
to help me whip the MDB blues in CFMX 7.

1. Set up datasources as Access with Unicode (as kindly suggested)
2. Set buffer to 0 and page timeout to 600 (as per a MM kb article)
3. Checked "maintain connections across client requests" (I noobliy thought CF
just did this)

My original Select * from MyNoobness which was taking 143-178ms, is now down
to a blazin 14ms.

Now ... that's what I'm talking about.

I'm not disputing any real relational DB as being the correct solution, but I
sure have deployed more than one CF site with access on the backend without
troubles.

Again, special thanks to Mr. Dupre and all those that read, or answered this
post. and for those that didn't ... that was not nice.

:D