Charlie, I did a bit of research into cfqueryparam to see what you meant by ther performance but it is only for those DBMSs that support bind variables anyway.
But I also was reading the comments to this and came across a know unwanted feature, I say that because it is not considered a bug by Adobe, or Macromedia / Allaire at that time. It appears that this error can occur when doing something like this. Select * From Table And not if you have this. Select * From Table But it can, just not as frequent and the solution was to restart the CF Application server until it happened again. But they also said to avoid at all costs using select * from table and to use this Select column1, column2 From table As the change is more likely to be picked up by the caching of the query. Or you could use the setting in the Administrator for Cached queries and set that to zero. Duncan, I hope that helps you out here. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Friday, 2 March 2007 2:06 PM To: [email protected] Subject: [cfaussie] Re: @#$!! queryparam Yes, as Scott later said, Andrew, you don't want to confuse caching of the query with what Duncan said, which was "caching of the query plan". And as Scott clarified, you definitely don't want to spread the misconception that cfqueryparam is "just" for security. The value in query plan caching can be greater, for performance sake. Still, you're right that a query inside a CFC or UDF whose variables are all validated with CFARGUMENT just doesn't need the security aspect of CFQueryParam, but it's still typically valuable for performance. (I am giving a talk to the Atlanta SQL Server group on the subject of query plan caching, and some important changes in SQL 2005, as well as how to measure the impact of doing it or not. I've been planning to make a variation of that talk for CF audiences, focusing on MySQL and perhaps Oracle as well.) Anyway, Scott also got to the point of Duncan's problem below. It's your use of Select *. That's a notorious problem when using CFQUERYPARAM. The simple solution is to stop using that. That's good practice for all manner of reasons, not the least of which this. /Charlie http://www.carehart.org/blog/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 7:58 PM To: [email protected] Subject: [cfaussie] Re: @#$!! queryparam Duncan, The query is only cached when you tell it to be cached. But are you using any other framework like reactor or transfer or even MG:U, or even have this in a cfc that might be stored in a session or application scope. One other thing I would like to point out that a lot of people do not take for granted, but tend to just do it anyway. I chose not to, but that is my choice. If the query is in a cfc, and that function uses arguments that will be used in the query for example <cffunction name="getEmployee"> <cfargument name="EmployeeId" type="numeric" required="true" /> <cfset var Record = '' /> <cfquery name="Record" datasource=""> Select * from Employees where EmployeeId = #Arguments.EmployeeId# </cfquery> </cffunction> I will never ever use the cfqueryparam, and the reason being is that the function itself will take care of the validation for me. However, although I did say never a string is a different story and will use it for a string. I know this has nothing to do with your problem, but just wanted to make that statement because I still see people use the cfqueryparam in places I know it is not necessary to use. If you would like to post a more detailed example on how you are using this, whether it is in any of my original methods then we can help you further but one thing to also take into consideration is the caching of the coldfusion class files too, this should never be switched on for development purposes. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
