Query Performance

[mfoster]

CF8 and sql/server 2000

We are experiencing unexpectedly long query times using CFQuery and CFStoredProc. A query or procedure invokation that returns in 1 or 2 seconds in query analyzer is taking 30-60 seconds when being invoked from a CF template; this is the "time" value shown in the debugging output. The queries being executed are identical. Any help would be greatly appreciated.

Mike

[Dan_Bracuk]

Might be driver related.

[Fetch]

Is the SQL server on the same LAN or VLAN as the CF server? If you're connecting across the web for example I would expect worse execution times. I.e. it may also be a connectivity issue.

[Newsgroup_User]

are you using <cfqueryparam> tag in your query? make sure that cfsqltype
is correct - there has been a similar problem discussed recently and the
culprit was cfsqltype="cf_sql_date" (can't remember what it had to be
changed to to make the query execute as fast as from within SSMC)

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Guest]

I would check these things....

Indexing on your database tables. Performance can dramatically decrease as rows increase in size in a table if indexes aren't optimized. Do a google search on sql server indexing and you'll find plenty of articles with good information.

Switch your worst offending queries to stored procedures. I tend to believe the execution plan for SPs is better than prepared statements using sql parameters (ie cfqueryparam). Centralized code using SPs is an extra plus.

Azadi makes a good point, there is some strageness around date parameters in general with SQL Server. I've found that sql datetime variables used in a SP, perform much better sometimes when they are declared as input parameters, instead of being declare in the TSQL inside of an SP. IE.....

create proc myProc @myDate datetime as ...... more TSQL

instead of.....

create proc myProc as
declare @myDate datetime.... more TSQL

I remember reading an article about this but can't remember what the issue was. Something about the caching of the execution plan I think in SQL server. But I remember it made a lot of sense when I read it. I probably have it bookmarked at work....

Another thing to check, has anything else change with these queries, like a new column or data type change. Sometimes these changes mess with CF's JDBC caching and/or sql server's execution plan cache.

I also have one SP that freaks once and a while, and I just need to run a alter proc .... to reset the execution plan and it starts working wonderfully again, haven't figured that one out yet.

I also find sql profiler to be pretty invaluable in terms of narrowing down the real problem. You may have a situation where a new INSERT or UPDATE statement to the same table is locking things for a bit, before you old SELECT can run, thus making everything run slow on the table. Profiler can give you a record of the order of operations and narrow down the bottle neck. Play around with that a bit. There's a pretty small performance hit, so don't be too worried about that.