I whole-heartedly agree and when I get some time I will do some testing.
What I imagine to be the case is that the more complex the SQL required, the
more likely it is that a stored procedure is beneficial but perhaps this is
wrong (it is certainly blind assumption).
Regardless of that, I like to
checking query performance times and looking at execution
plans, playing with the SQL and table Indexing.
Rob
-Original Message-
From: Dominic Watson [mailto:[EMAIL PROTECTED]
Sent: 25 October 2007 09:50
To: CF-Talk
Subject: Re: cfqueryparam and dynamically-created SQL
I whole-heartedly agree
October 2007 09:50
To: CF-Talk
Subject: Re: cfqueryparam and dynamically-created SQL
I whole-heartedly agree and when I get some time I will do some testing.
What I imagine to be the case is that the more complex the SQL required,
the
more likely it is that a stored procedure is beneficial
1. I believe stored procs are generally faster than sending the
same query to SQL Server due to query optimisation(?)
The only query optimization that goes on is the building of an execution
plan. Using conditional logic in a stored procedure can have very harmful
effects on the
On Tuesday 23 Oct 2007, [EMAIL PROTECTED] wrote:
Does the presence of the cfif statement inside the cfquery block negate
all the performance benefits I would have otherwise gained from using
cfqueryparam?
You have to remember cfqp doesn't just buy you performance, but security and
robustness
-Original Message-
From: Tom Chiverton [mailto:[EMAIL PROTECTED]
Sent: 24 October 2007 10:39
To: CF-Talk
Subject: Re: cfqueryparam and dynamically-created SQL
On Tuesday 23 Oct 2007, [EMAIL PROTECTED] wrote:
Does the presence of the cfif statement inside the cfquery block negate
all
What I believe happens is that two different statements will be compiled in
the database, one that has two bind variables and one that has one. So you
should get the same speed benefit, the database just treats them as two
different statements.
On 10/24/07, 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,
seconds to wrap up all your
dynamic parameters, once you get in to the habbit you won't even think twice
about it.
Rob
-Original Message-
From: Ben Mueller [mailto:[EMAIL PROTECTED]
Sent: 24 October 2007 17:33
To: CF-Talk
Subject: Re: cfqueryparam and dynamically-created SQL
Thanks, all
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
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!
It did go through. Check the website, there have been several responses.
http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:54002
Thanks,
Eric
Ben Mueller wrote:
Posted this yesterday, but it didn't seem to go through...
I became a convert to cfqueryparam, thanks to this
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
Try not to think about queryparam as a performance benefit so much, The
performance increase isn't really anything that massive, think about it from
a security perspective.
Without cfqueryparam people will add, edit and remove content from your
database, gain access to secure sections of your
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
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
I get the fact that it's important to prevent SQL injection
attacks, etc etc. While I certainly see the value of that, I
must admit that I'm not sold on cfqueryparam for that reason
alone. Our web application tends to check incoming data
before it even gets to a SQL statement. So,
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
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!
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.
Why are you quite sure? Have you
Why I believe it to be true (though quite open to it not being the case):
1. I believe stored procs are generally faster than sending the
same query to SQL Server due to query optimisation(?)
2. Less data is being sent to the SQL server
3. Less CF application process before the code
and dynamically-created SQL
Why I believe it to be true (though quite open to it not being the case):
1. I believe stored procs are generally faster than sending the
same query to SQL Server due to query optimisation(?)
2. Less data is being sent to the SQL server
3. Less CF application
The CFIF will result in two different SQL statements to be prepared,
instead of one, so there is a slight ramification. Pretty irrelevant
though.
cheers,
barneyb
On 10/23/07, Ben Mueller [EMAIL PROTECTED] wrote:
Hi all,
I've become a convert to cfqueryparam after posting a question about it
I'd be willing to bet that if the CFIF statement hurts performance it is by
a very small amount.
On 10/23/07, Ben Mueller [EMAIL PROTECTED] wrote:
Hi all,
I've become a convert to cfqueryparam after posting a question about it to
this group several months back. Since then, I've started
Does the presence of the cfif statement inside the cfquery
block negate all the performance benefits I would have
otherwise gained from using cfqueryparam? If so, I would
guess this is a common issue people run into. Is there a
best practice for handling this kind of situation?
Yes, to
If there is a performance benefit using cfqueryparam, it will still be of
benefit in this case. Also, regardless of the performance, security is still
an issue (SQL injections) and so I would say that continuing to use
cfqueryparam is your best option and practice.
Regards,
Dominic
On
26 matches
Mail list logo
