CFQueryParam can indeed decrease performance. If you look at what
happens when you use CFQueryParam versus just passing in variables it
makes sense. CFQueryParam transforms any passed variables into bind
variables. In MSSQL it is the same as declaring a variable, setting it
and then using that in the query. IE
SELECT
Id,
Description
FROM Product
WHERE ProductID = 2
VS (with query param )
DECLARE @P1 int
SET @P1 = 2
SELECT
Id,
Description
FROM Product
WHERE ProductID = @P1
CF then uses a stored procedure spprepexec to try to force the dbms to
precompile the sql statement into an execution plan for faster execution
on subsequent calls. This is where you lose performance often. If your
database does not have enough memory to store all of the prepared
statements you will be essentially running the query 2x every time you
call it. Once to generate the execution plan once to actually run it.
The same thing happens if the query is not called often enough. IE in
the case of reports run infrequently. The database will clear the
execution plan for your query since it is not used often to make room
for other queries. I would recommend writing a custom tag to replace
cfqueryparam when used with infrequent long running reports. A query
that takes 100 ms to run, is no big deal if it has to be recompiled once
and a while. A query that runs for 3 minutes can be a major problem if
it takes 2x as long 50% of the time. I wish Adobe would rewrite this
functionality to take an optional parameter to specify whether to use sp
prep exec.
I've attached a custom tag I wrote to replace cfqueryparam in these
cases. User beware it is not fully tested, and not as secure as
cfqueryparam.
-----Original Message-----
From: Greg Luce [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 28, 2006 1:01 PM
To: CF-Talk
Subject: cfqueryparam DECREASES performance?
OK, I must have something wrong here. I've only heard good things about
cfqueryparam on this list for both security and performance. A client
sent
me an ugly report that times out for them. I spent an hour going through
it
and applying cfqueryparams to each variable in the many queries with
appropriate datatypes. I threw a cfsetting tag in to increase the
request
timeout and the report runs in roughly about 512687 ms, restarted MSSQL
server and CFMX7, then with the cfqueryparams the same query that was
running in 5282ms in the old code, now takes 15094ms.
Any ideas?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265293
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
