Yes, a prepared statement using bind variables is going to be significantly faster in virtually all cases. By significant, I mean twice as fast, if not more. It depends on the database.
I can't imagine why you'd want to let an invalid value still execute the query, but if you choose to do this you can still use Val() in the cfqueryparam's value. On 8/10/07, Ben Mueller <[EMAIL PROTECTED]> wrote: > > Dave, thanks for the reply. I won't bore you with details, but suffice it > to say that we prefer getting empty recordsets back in some cases because it > makes our overall error handling and application flow better--granted, > perhaps to the detriment of application performance. However, for us the > trade-off has been well worth it to this point. > > But, if the general consensus is that using <cfqueryparam> will really > speed up execution in most cases, then perhaps we'll start using it. > > In the cases where it does increase performance, does it do so because SQL > bind variables are used? > > Thanks again, > Ben > > >> the nice thing about this is that if "URL.userID" isn't an > >> integer, int() returns 0, and the query executes and simply > >> returns no records. For us, this is a far preferable method > >> than what cfqueryparam would do, which is to bomb before > >> executing the query. > > > >You'd rather run a query unnecessarily than avoid running the query? Why > not > >just catch the exception? > > > >> Then today I discovered that cfqueryparam supports bind > >> variables, which theoretically will improve database > >> performance. So now the question is: how much does it > >> improve performance? Am I really going to notice it? > > > >In some cases, you'll see significant performance increases. In other > cases, > >you won't. In still other cases, you may see performance degradation. All > >that said, I'd recommend that you use it in every case, because it will > >generally provide better performance in most cases. > > > >> Should I really switch my queries over to something like this: > >> > >> SELECT username > >> FROM user > >> where userID = <cfqueryparam CFSQLType="CF_SQL_INTEGER" > >> value=#int(URL.user_id)#> > > > >I would do without the Int part, myself, and just catch the appropriate > >exception. That's what exceptions are for! > > > >Dave Watts, CTO, Fig Leaf Software > >http://www.figleaf.com/ > > > >Fig Leaf Software provides the highest caliber vendor-authorized > >instruction at our training centers in Washington DC, Atlanta, > >Chicago, Baltimore, Northern Virginia, or on-site at your location. > >Visit http://training.figleaf.com/ for more information! > > > >This email has been processed by SmoothZap - www.smoothwall.net > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285978 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
