Everything but your guess is correct ;)

Dominic

2009/4/16 David McGuigan <davidmcgui...@gmail.com>:
>
> Well from what I read today it seems like the performance is improved for
> reused prepared statements, which may not be how cfqueryparam is
> implemented.
> Prepared statements are stored and reused by name, passing in the values for
> the parameters.
>
> So if cfqueryparam is using prepared statements (which my guess would be
> that it's not), cfqueryparamming all of your variable values would improve
> performance because the statement itself isn't being re-sent to the RDMS on
> each subsequent query, only the values. But if it's not, it could actually
> slightly DIMINISH performance ( because the SQL generated is more verbose
> because of the variable declarations and binding syntax ).
>
> Any experts out there that can enlighten us?
>
>
> On Thu, Apr 16, 2009 at 12:33 PM, Dominic Watson <
> watson.domi...@googlemail.com> wrote:
>
>>
>> Just a little thing to add here is that I believe you should
>> parametize all values in your query, whether user generated or
>> constant values or whatever. This is not for security but performance.
>> So:
>>
>> SELECT fu
>> FROM bar
>> WHERE barId = <cfqueryparam cf_sql_type="cf_sql_integer" value="#id#" />
>> AND live = 1
>>
>> Should be:
>>
>> SELECT fu
>> FROM bar
>> WHERE barId = <cfqueryparam cf_sql_type="cf_sql_integer" value="#id#" />
>> AND live = <cfqueryparam cf_sql_type="cf_sql_bit" value="1" />
>>
>> The reason (and someone please correct me if I am wrong) is that
>> prepared statements can perform better because the db engine caches
>> their execution plan much in the same way as for stored procedures. By
>> parametizing and supplying the type of each value, you help this
>> process. Indeed I suspect that I have read somewhere that it will not
>> cache at all if it finds values that are not parametized.
>>
>> This is my rough understanding of it; please somebody who knows more
>> clarify or correct the point (I do know that db performance always
>> goes by the rule: "it depends" though).
>>
>> Dominic
>>
>>
>
> 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321690
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Reply via email to