Woops, I made a mistake in that SQL, should have been: <cfquery name="myname" datasource="myDB">
SELECT email FROM user u WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somecountry#> AND ( Len(<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) = 0 OR state = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) </cfif> </cfquery> On 24/10/2007, Dominic Watson <[EMAIL PROTECTED]> wrote: > > If you are talking about whether, in the case of your multiple IF > statement DSQL, to use or not use CFQP then I think there is only one > answer; use CFQP! As I said before, if there is a performance boost in using > CFQP over not using it, then that performance boost should still be valid. > Also, all the arguments of security probably outweigh any performance issues > there may be. > > If you are talking about finding an alternative to the multiple IF DSQL > then I would say that if it is at all possible, then do it! I.e., in the > example you gave, you could replace the CFIF with this: > > <cfquery name="myname" datasource="myDB"> > > SELECT email > FROM user u > WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar" > value=#somecountry#> > AND ( Len(<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) > > 0 > OR state = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) > </cfif> > </cfquery> > > Dom > > On 24/10/2007, Ben Mueller <[EMAIL PROTECTED]> wrote: > > > > Thanks, all, for your replies. So, a follow-up question: will SQL > > create multiple prepared statements for each condition? If so, that's > > great. My fear is that it will only create 1 prepared statement for each > > cfquery block, so if the current statement doesn't match the previous > > statement, it would discard the previous one and create a new one. > > > > I have a bunch of queries that have 8 or more <cfif> conditions, almost > > all in the WHERE clause. Obviously, the more <cfif> conditions there are, > > the greater the likelihood that any one individual call won't match the > > previous one. > > > > I know cfqp is really good and all that, but this would be pretty > > annoying if I got no real performance benefit. > > > > > > > > > > > >Yes, to the extent that you are less likely to have another query with > > the > > >same prepared statement (and the same execution plan, of course) that > > can be > > >run again. Frankly, though, I wouldn't worry too much about that, since > > >coding around that causes all kinds of problems. > > > > > >Dave Watts, CTO, Fig Leaf Software > > >http://www.figleaf.com/ > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291984 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
