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 but perhaps this is wrong (it is certainly blind assumption). Regardless of that, I like to contain all my data logic inside the database itself - feels clear in my head that way. Anyways, this is drifting off topic - just wanted to give an alternative to the OPs solution to his problem ;) Dom On 24/10/2007, Mark A Kruger [EMAIL PROTECTED] wrote: Dom, To know this you have to test. SPs are marginally faster in most cases - and just like cfqueries they have to be well written. There is not enough of a boost in performance (when comparing SPs to well written queries using cfqueryparam to bind the data) to make a hard and fast rule that SPs are best practice in all cases - that's my .02. Having said that, in a team enviornment there is some division of labor benefits. -Mark -- Blog it up: http://fusion.dominicwatson.co.uk ~| 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:292044 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam and dynamically-created SQL
Yep, I think like with queryparam, you have to look outside of the performance benefits when working with stored procedures. The other benefits come when you have very complex queries that are accessed from several locations within an application, or multiple applications, this means not having to duplicate the query SQL code, it also helps with maintenance of the query down the line if needs be. There are probably performance benefits, but I'd imagine that if performance is a REAL concern to you, there are plenty of other places within your application where your time would be better spent optimizing, you'll likely get better performance benefits by considering things like caching of queries and objects and so on. Like the other guys have picked up on, the biggest performance on queries will come from good SQL code, you're best off spending some time in your database studio 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 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 contain all my data logic inside the database itself - feels clear in my head that way. Anyways, this is drifting off topic - just wanted to give an alternative to the OPs solution to his problem ;) Dom On 24/10/2007, Mark A Kruger [EMAIL PROTECTED] wrote: Dom, To know this you have to test. SPs are marginally faster in most cases - and just like cfqueries they have to be well written. There is not enough of a boost in performance (when comparing SPs to well written queries using cfqueryparam to bind the data) to make a hard and fast rule that SPs are best practice in all cases - that's my .02. Having said that, in a team enviornment there is some division of labor benefits. -Mark -- Blog it up: http://fusion.dominicwatson.co.uk ~| 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:292046 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and dynamically-created SQL
I totally agree. I like to put code in stored procs for organisational reasons more than performance ones in most cases. However, I was recently updating a site that used dynamic SQL to generate some reports and the performance of this was becoming a huge issue. The logic was so complicated that approximately 1MB of SQL code was being sent to the SQL server (cfqueries within cfloops of other queries) - eek! In this instance I chose to rewrite the SQL from scratch (bad code was the biggest flaw) and also put each report into a stored procedure. The performance was an issue and the boost was huge. Dominic On 25/10/2007, Robert Rawlins - Think Blue [EMAIL PROTECTED] wrote: Yep, I think like with queryparam, you have to look outside of the performance benefits when working with stored procedures. The other benefits come when you have very complex queries that are accessed from several locations within an application, or multiple applications, this means not having to duplicate the query SQL code, it also helps with maintenance of the query down the line if needs be. There are probably performance benefits, but I'd imagine that if performance is a REAL concern to you, there are plenty of other places within your application where your time would be better spent optimizing, you'll likely get better performance benefits by considering things like caching of queries and objects and so on. Like the other guys have picked up on, the biggest performance on queries will come from good SQL code, you're best off spending some time in your database studio 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 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 contain all my data logic inside the database itself - feels clear in my head that way. Anyways, this is drifting off topic - just wanted to give an alternative to the OPs solution to his problem ;) Dom On 24/10/2007, Mark A Kruger [EMAIL PROTECTED] wrote: Dom, To know this you have to test. SPs are marginally faster in most cases - and just like cfqueries they have to be well written. There is not enough of a boost in performance (when comparing SPs to well written queries using cfqueryparam to bind the data) to make a hard and fast rule that SPs are best practice in all cases - that's my .02. Having said that, in a team enviornment there is some division of labor benefits. -Mark -- Blog it up: http://fusion.dominicwatson.co.uk ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292049 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam and dynamically-created SQL
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 reusability of an execution plan. I'd rather have multiple queries, each with its own optimal execution plan, than one stored procedure with a potentially suboptimal execution plan. 2. Less data is being sent to the SQL server In most cases, this is trivial. 3. Less CF application process before the code is run on the SQL server weighed against more SQL application process after the code is run on your CF server. Would you think otherwise? I think it's very dangerous to attempt predictions of performance based on what feels right to us, as we often do. Very often, in my experience, load testing tends to overturn those predictions. 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! ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292076 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 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 too. Any tiny, tiny, performance hit is unlikely to out weigh that. If so, I would guess this is a common issue people run into. Is there a best practice for handling this kind of situation? Use cfqp. Always use cfqp. -- Tom Chiverton. Are you a great ColdFusion programmer, who knows Reactor and ColdSpring, and has done some Flex work ? Would you like to work for a top 30 law firm in Manchester, UK ? Are you not an agency ? If yes, send email ! This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291946 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam and dynamically-created SQL
Yep, I agree with Tom, _always_ use it. If you haven't done so already, do some research on 'SQL Injection Attacks' which you are vulnerable too when not using cfqueryparam ... You'll be having nightmares and panic attacks for days, until you wrap all your dynamic SQL in cfqueryparam. Rob -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 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 too. Any tiny, tiny, performance hit is unlikely to out weigh that. If so, I would guess this is a common issue people run into. Is there a best practice for handling this kind of situation? Use cfqp. Always use cfqp. -- Tom Chiverton. Are you a great ColdFusion programmer, who knows Reactor and ColdSpring, and has done some Flex work ? Would you like to work for a top 30 law firm in Manchester, UK ? Are you not an agency ? If yes, send email ! This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291947 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and dynamically-created SQL
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: Posted this yesterday, but it didn't seem to go through... I became a convert to cfqueryparam, thanks to this list, about 2 months ago. Since then, I've wondered about this kind of statement (shortened here): cfquery SELECT username FROM user WHERE lastname = cfqueryparam type=varchar value=#mylastname# cfif len(myfirstname) AND cfqueryparam type=varchar value=#myfirstname# /cfif /cfquery Does the presence of the cfif block inside the SQL negate the speed benefits I would get from using cfqueryparam? I'm kind of hoping not, but guessing yes. Thanks in advance for the advice, Ben Mueller ~| 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-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291975 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and dynamically-created SQL
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:291972 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam and dynamically-created SQL
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 site, steal important information from you, names, address, credit card information and so on. queryparam really is important... _really_ important ... I can't stress that enough. Why is it that you find it annoying? Because of the extra few moments it takes to code? It really is worth that extra few 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, 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/ ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291981 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and dynamically-created SQL
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-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291983 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and dynamically-created SQL
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/
~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax features - download now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291985
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and dynamically-created SQL
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 list, about 2 months ago. Since then, I've wondered about this kind of statement (shortened here): cfquery SELECT username FROM user WHERE lastname = cfqueryparam type=varchar value=#mylastname# cfif len(myfirstname) AND cfqueryparam type=varchar value=#myfirstname# /cfif /cfquery Does the presence of the cfif block inside the SQL negate the speed benefits I would get from using cfqueryparam? I'm kind of hoping not, but guessing yes. Thanks in advance for the advice, Ben Mueller ~| 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:291980 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and dynamically-created SQL
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
Re: cfqueryparam and dynamically-created SQL
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 site, steal important information from you, names, address, credit card information and so on. queryparam really is important... _really_ important ... I can't stress that enough. Why is it that you find it annoying? Because of the extra few moments it takes to code? It really is worth that extra few seconds to wrap up all your dynamic parameters, once you get in to the habbit you won't even think twice about it. 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, while I have started using cfqp everywhere, I probably wouldn't use it for security alone. Hate me if you must. (-; I am also interested in the performance benefit, and what would annoy me is if the presence of a cfif statement inside a cfquery block negated any performance benefit gained from cfqp. There are a few cases on our site where adding cfqp seems to have made a non-trivial difference in performance, and I would hate to think that in all other other places I use it, I may be doing more harm than good in terms of performance. (I'm only slightly annoyed by how verbose the tag is, but not enough to stop using it). Anyway, thanks all for the replies. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291992 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam and dynamically-created SQL
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. Execution plans are cached by your database server, not by your application server, so you'll just end up with more execution plans. CF just builds the statement and sends it to the database server. 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! ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291988 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and dynamically-created SQL
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
RE: cfqueryparam and dynamically-created SQL
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, while I have started using cfqp everywhere, I probably wouldn't use it for security alone. Hate me if you must. (-; It's one thing to check incoming data (not to mention tend to check said data) and another thing to separate data from SQL code, which is what CFQUERYPARAM does. The former is no substitute for the latter. 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! ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291996 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and dynamically-created SQL
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-72catid=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
Re: cfqueryparam and dynamically-created SQL
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
Interesting idea, Dom. Thanks. But do you really need the quotes around the
cfqp inside the len() statement? I would think not.
Ben
~|
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:291993
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam and dynamically-created SQL
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 done any load testing? Very often, things don't work out the way we might expect them to. 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! ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292012 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam 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 process before the code is run on the SQL server 4. WHERE @foo = 0 is a very simple instruction to process and I assume the server does a good job of optimising it ( I realise to assume makes an ass out of u and me) Would you think otherwise? Dom On 24/10/2007, Dave Watts [EMAIL PROTECTED] wrote: 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 done any load testing? Very often, things don't work out the way we might expect them to. 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! ~| 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-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292020 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam and dynamically-created SQL
Dom, To know this you have to test. SPs are marginally faster in most cases - and just like cfqueries they have to be well written. There is not enough of a boost in performance (when comparing SPs to well written queries using cfqueryparam to bind the data) to make a hard and fast rule that SPs are best practice in all cases - that's my .02. Having said that, in a team enviornment there is some division of labor benefits. -Mark -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 5:06 PM To: CF-Talk Subject: Re: cfqueryparam 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 process before the code is run on the SQL server 4. WHERE @foo = 0 is a very simple instruction to process and I assume the server does a good job of optimising it ( I realise to assume makes an ass out of u and me) Would you think otherwise? Dom On 24/10/2007, Dave Watts [EMAIL PROTECTED] wrote: 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 done any load testing? Very often, things don't work out the way we might expect them to. 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! ~| 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-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292024 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and dynamically-created SQL
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 to this group several months back. Since then, I've started wondering about the benefits of it if there are cfif statements in a cfquery block. For example: cfquery name=myname datasource=myDB SELECT email FROM user u WHERE country = cfqueryparam cfsqltype=cf_sql_varchar value=#somecountry# cfif len(somestate) AND state = cfqueryparam cfsqltype=cf_sql_varchar value=#somestate# /cfif /cfquery 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? Thanks in advance, Ben Mueller -- Barney Boisvert [EMAIL PROTECTED] http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| 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:291890 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and dynamically-created SQL
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 wondering about the benefits of it if there are cfif statements in a cfquery block. For example: cfquery name=myname datasource=myDB SELECT email FROM user u WHERE country = cfqueryparam cfsqltype=cf_sql_varchar value=#somecountry# cfif len(somestate) AND state = cfqueryparam cfsqltype=cf_sql_varchar value=#somestate# /cfif /cfquery 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? Thanks in advance, Ben Mueller ~| 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-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291888 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam and dynamically-created SQL
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 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/ 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! ~| 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-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291889 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and dynamically-created SQL
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 23/10/2007, 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 wondering about the benefits of it if there are cfif statements in a cfquery block. For example: cfquery name=myname datasource=myDB SELECT email FROM user u WHERE country = cfqueryparam cfsqltype=cf_sql_varchar value=#somecountry# cfif len(somestate) AND state = cfqueryparam cfsqltype=cf_sql_varchar value=#somestate# /cfif /cfquery 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? Thanks in advance, Ben Mueller ~| 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:291891 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
