>
> But do you really need the quotes around the cfqp inside the len()
> statement?
No you don't, yet another mistake!
> I'd think adding the OR like that would end up with a performance, a
> insanely small one, drop just like a CFIFmight.
>
I'd be interested to know the performance difference between doing that and
using CFIF to build the query. Any other solutions that would perform
faster?
I quite often do something similar with stored procs but usually with
numerical values, i.e. Default them to 0 in the proc and do 'where (@foo = 0
OR myCol = @foo)'. I'm quite sure that is faster than building a dynamic
query and sending it to the server.
Dominic
On 24/10/2007, Aaron Rouse <[EMAIL PROTECTED]> wrote:
>
> 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/
> > > > >
> > > > >
> > > > >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292002
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
