Because you're basically taking that constant and forcing the SQL engine to treat it as a dynamic value, which as you'd imagine, incurrs some extra overhead. I'm surprised that the performance difference is noticable, but degradation is to be expected.
Having raw variables in SQL is the slowest, because the SQL engine needs to parse a custom query string for every query. Using CFQUERYPARAM (or more properly bind variables) is faster, because it lets the SQL engine cache the query string for multiple invocations, and just substitute in the different values as needed. Using a constant is fastest, because the value is part of the cached query string, so not even the substitution needs to happen at execution time. cheers, barneyb On 10/23/06, Charlie Hanlon <[EMAIL PROTECTED]> wrote: > When I place a constant within a cfqueryparam, I am noticing performace > degradation. > > SELECT myField1, myField2, myField3 > FROM myDBtable > WHERE myRecID = <cfqueryparam value=#val(url.id)# > cfsqltype="CF_SQL_INTEGER"> > AND myTypeID = <cfqueryparam value=22 cfsqltype="CF_SQL_INTEGER"> > > The query above runs slower than the query shown below: > > SELECT myField1, myField2, myField3 > FROM myDBtable > WHERE myRecID = <cfqueryparam value=#val(url.id)# > cfsqltype="CF_SQL_INTEGER"> > AND myTypeID = 22 > > Can anyone tell me why this is, why placing the hard-coded value of 22 into > a cfqueryparam causes a performance hit? > > > thanks in advance for insight > > Charles Hanlon -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257786 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
