Benjamin, Since the list doesn't allow attachments, is your cfqueryparam tag available via a URL?
-Dan >-----Original Message----- >From: Porter, Benjamin L. [mailto:[EMAIL PROTECTED] >Sent: Friday, December 29, 2006 8:58 AM >To: CF-Talk >Subject: RE: cfqueryparam DECREASES performance? > >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:265465 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
