I'd think adding the OR like that would end up with a performance, a
insanely small one, drop just like a CFIFmight.
On 10/24/07, Dominic Watson <[EMAIL PROTECTED]> wrote:
>
> Oh my goodness me, still wrong! Should be:
>
> 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#>)
>
> Phew!
>
> Dom
>
> On 24/10/2007, Dominic Watson <[EMAIL PROTECTED]> wrote:
> >
> > 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/
> > > >
> > > >
> > > >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291995
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
