[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
OK, ok. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Monday, March 05, 2007 9:14 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam *sigh* Well Charlie, when you pushed for time and you reply to something you can't always think of the top of your head. In this case I was trying to picture the settings in the administrator. Now in my defence, until Duncan came out and said it. It was still not very clear on how he was doing the query, and I was just being as specific as possible for him to look at possible solutions to the problem. However when it came to the actual fix for the problem, oh well I got confused in writing my reply :-( Andrew Scott --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Yes, you're right, Duncan. But to Andrew, a very important point need to be made again: you're confusing query caching (caching the results of a SQL SELECT statement) with query plan caching (what Duncan is describing). CFQUERYPARAM affects query plan caching. That caching is entirely in the database. As Scott later wrote, the CACHEDWITHIN and CACHEDAFTER attributes of CFQUERY affect whether a given query's results are cached. That caching is done in CF, and to answer the later questions, yes the Cached Queries feature in the CFAdmin controls how many cached results are allowed to be kept in memory. If you set it to 0, then I would expect you're saying allocate no memory to query caching, in which case the attributes are effectively ignored and no query results are ever cached. Again, all this is indeed about 2 of several other forms of caching I'll discuss in my WebDU presentation. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Sunday, March 04, 2007 6:24 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam does CF cache the query or is the DB that caches it My understanding is: neither of these systems cache the query. What happens is SQL creates an execution plan when it gets a SQL statement to process. Where cfqueryparam is used, this plan is saved, (I would guess this is an instruction passed by Java to have this happen) and on subsequent processing takes this step out because it has already created it. Hence the performance gain. Thats my understanding - I think its close, however I know I am probably missing the nuances of this process. Duncan On 3/5/07, Andrew Scott [EMAIL PROTECTED] wrote: Charlie, Well then I will try to not to pick on you then:-) What I said was I see no reason to use cfqueryparam for security originally, because I was unaware of the performance boost by it caching the query. But having said that the RDBMS has to support bind variables first for it to cache and work this way to begin with. When I mention SP, I was saying that I would prefer to use them and not referring to ou :-) Now all that aside after I think about it more, I can see a huge benefit in using stored procedures not for security but for the caching, but I would like to know does CF cache the query or is the DB that caches it? Just in case someone knows and can post before I get a chance to do some research on it. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone:+613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Saturday, 3 March 2007 3:44 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Andrew, I didn't say anything about SPs, nor did I (or would I) argue against them. In fact, there's nothing in my reply nor in your note I was replying to that discussed them. In my note to you (which I'll assume you are replying to here, since you didn't include it but I did offer two last night), I was just talking about clarifying which caching was being discussed, since you mentioned query caching while Duncan had referred to plan caching. And then we were suggesting not to just view CFQueryParam as a security tool. That said, can you reframe your question below? If it was just referring, like Duncan, to my point about Select *, I hope I've answered that in my last note. And let's not make this pick on Charlie day. Goodness, I'm just here to try to help. I never mean to get into a pissing match with anyone. I'm certainly not trying to smack anyone down or show my stick is bigger. I just like to share info and point out opportunities to correct or clarify. Email's a notoriously bad communication vehicle when it comes to conveying emotion. I just want to be clear that I have don't mean ever in my notes to be picking a fight or trying to one up anyone. I hope that helps put my responses in perspective. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 10:30 PM To: cfaussie@googlegroups.com Subject: [cfaussie] CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Charlie, Can you do me a favour, and explain the typically valuable for performance issue. I don't understand that, because to me better performance would mean to have it as an SP to begin with. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone:+613 8676 4223 Mobile: 0404 998 273 -- Duncan I Loxton [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you
[cfaussie] Re: @#$!! queryparam
Right, then as it seems, this all comes down to the point made some time ago: if you use SELECT * in a statement using CFQUERYPARAM, you can expect trouble if you add columns to the table. Among the places this is discussed is here: http://www.1pixelout.net/2004/06/16/cfqueryparam/ Hope that helps. And I'll certainly look forward to meeting you and anyone else coming to WebDU. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Sunday, March 04, 2007 4:47 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam We are not caching any templates. In CFAdmin there are no cached templates, trusted cache or saving of class files. We are not using a framework, its as simple as putting a cfquery into a .cfm page and running it directly in the browser. It is only on queries with select * in them. On queries that are specifically tailored to pull only certain columns (i.e. no star *) it does not happen. We are running 7,0,1,116466 CFMX 7 Standard edition, using SQL 2000, and the standard Microsoft SQL Server Driver. We havent updated CF to 7.0.2 because none of the items in the issues list for the updater or patches have deemed it to be necessary in our case. Now hopefully you can see why I am asking about this - its a simple simple thing we are doing, but the answer doesnt look to be simple. Charlie, I will introduce myself at WebDU. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Well, I know I just wrote my last note so you can't have seen it yet, but I'll answer more directly this later question of yours, Andrew: there is no connection between CFQUERYPARAM and the cached queries feature. Are you saying you have some MM resource that suggests otherwise? That would be very interesting to see, so I hope you can find and share it. (Hey, I learned about the NULL attribute only today, as I mentioned earlier, so I'm always open to learning new info.) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Sunday, March 04, 2007 7:03 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Acually not sure, but the problem Duncan was having was suggested my Macromedia so that shows you it is a known problem and how long it has been around for, suggests for the cfqueryparam slect * from problem to set the cached queries queue to zero. But it might effect that attribute as well Charlie might be able to fill us in more maybe. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems) readily say find all results that say select * us bad. Here are a few areas where this has been discussed and/or debated: http://www.parseerror.com/sql/select*isevil.html http://www.sitepoint.com/forums/showthread.php?t=417457 http://www.adopenstatic.com/faq/selectstarisbad.asp http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,si d63_gci978334,00.html http://rip747.wordpress.com/2006/07/10/to-select-or-not-its-a-matter-of-opin ion/ That last one above is from a CF perspective, and does discuss the question from the perspective of what if I *do* want all the columns. I'll leave it to you and others to parse through all the available info to decide best for yourself. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Thursday, March 01, 2007 10:21 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Charlie, I didnt think that would be affecting it, I am more than aware that its bad practice, and as it happens the queries in question do have *, column1, column2 etc in them. I know that you are pulling more data than necessary with a * therefore
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Charlie, Cheers for the info, but I wasn't confusing anything. I was asking to begin with. And with select * from when using cfqueryparam, I only am reporting what is reported as a solution on the Adobe livedocs. Gosh:-) Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Charlie, The link is http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm The quote on the page is : I checked with one of our technical people and this is what he said: * Most database (including SQLServer) presume that the database structure does not change between re-uses of parameterized queries. Here are three possible solutions. Try #1 first - it may be enough to fix the problem. With #2 and #3 efficiency is reduced because the SQL statement will be compiled more often. 1. Avoid using wildcard SELECT * expressions. The expansion of the * is the part of the SQL statement most likely to change when the schema is altered, making the compiled SQL statement become invalid. If the specific fields are listed (and presuming they are still valid fields after the schema change) the compiled statement might still be useable. 2. Change [Max Pooled Statements] in Datasource Advanced Settings to zero. 3. Use inline arguments instead of cfqueryparam to cause the SQL statement be re-compiled by SQLServer for every request. * Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Tuesday, 6 March 2007 9:03 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Well, I know I just wrote my last note so you can't have seen it yet, but I'll answer more directly this later question of yours, Andrew: there is no connection between CFQUERYPARAM and the cached queries feature. Are you saying you have some MM resource that suggests otherwise? That would be very interesting to see, so I hope you can find and share it. (Hey, I learned about the NULL attribute only today, as I mentioned earlier, so I'm always open to learning new info.) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Sunday, March 04, 2007 7:03 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Acually not sure, but the problem Duncan was having was suggested my Macromedia so that shows you it is a known problem and how long it has been around for, suggests for the cfqueryparam slect * from problem to set the cached queries queue to zero. But it might effect that attribute as well Charlie might be able to fill us in more maybe. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Most database (including SQLServer) presume that the database structure does not change between re-uses of parameterized queries. What a strange presumption for them to make! Sure, table-schema updates are rare compared to table-queries, but it's not like the DB doesn't *know* when the table gets updated, and accordinly is completely unprepared for the ramifications of such. So it should be sensible enough to go well all those compiled queries that use that table ambiguously (like the ones with select *)... out the window you go: you'll have to be recompiled next time. That is better than just going err... I have no idea what you're talking about any more [face plant]. Does Oracle behave like this? -- Adam --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Oracle does behave like this - actually to the point where you can't add new tables and then run parametrised queries on them within the same session (I believe it is session related). Databases weren't designed to dynamically updated at runtime, and never have been - so you will end up hitting brick walls there. Mark On 3/6/07, Adam Cameron [EMAIL PROTECTED] wrote: Most database (including SQLServer) presume that the database structure does not change between re-uses of parameterized queries. What a strange presumption for them to make! Sure, table-schema updates are rare compared to table-queries, but it's not like the DB doesn't *know* when the table gets updated, and accordinly is completely unprepared for the ramifications of such. So it should be sensible enough to go well all those compiled queries that use that table ambiguously (like the ones with select *)... out the window you go: you'll have to be recompiled next time. That is better than just going err... I have no idea what you're talking about any more [face plant]. Does Oracle behave like this? -- Adam -- E: [EMAIL PROTECTED] W: www.compoundtheory.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Databases weren't designed to dynamically updated at runtime, and never have been - so you will end up hitting brick walls there. Can you explain to me what you mean by runtime, in the context of a DB engine? In such a way that distinguishes between me using CF as a DB client or using Query Analyser / Enterprise Manager / TOAD / SQL Developer as a client, I mean. -- Adam --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
and others to parse through all the available info to decide best for yourself. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Thursday, March 01, 2007 10:21 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Charlie, I didnt think that would be affecting it, I am more than aware that its bad practice, and as it happens the queries in question do have *, column1, column2 etc in them. I know that you are pulling more data than necessary with a * therefore slowing down processing with data processing an transfer. That's good practice for all manner of reasons Would you care to spell out other reasons? Duncan -- Duncan I Loxton [EMAIL PROTECTED] -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems) readily say find all results that say select * us bad. Here are a few areas where this has been discussed and/or debated: http://www.parseerror.com/sql/select*isevil.html http://www.sitepoint.com/forums/showthread.php?t=417457 http://www.adopenstatic.com/faq/selectstarisbad.asp http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,si d63_gci978334,00.html http://rip747.wordpress.com/2006/07/10/to-select-or-not-its-a-matter-of-opin http://rip747.wordpress.com/2006/07/10/to-select-or-not-its-a-matter-of-opin ion/ That last one above is from a CF perspective, and does discuss the question from the perspective of what if I *do* want all the columns. I'll leave it to you and others to parse through all the available info to decide best for yourself. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com mailto:cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com mailto:cfaussie@googlegroups.com] On Behalf Of Duncan Sent: Thursday, March 01, 2007 10:21 PM To: cfaussie@googlegroups.com mailto:cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Charlie, I didnt think that would be affecting it, I am more than aware that its bad practice, and as it happens the queries in question do have *, column1, column2 etc in them. I know that you are pulling more data than necessary with a * therefore slowing down processing with data processing an transfer. That's good practice for all manner of reasons Would you care to spell out other reasons? Duncan -- Duncan I Loxton [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Well, In my defence I use select * from still too, but I do not use cfqueryparam in my cfcs either so it is not an issue for me. But having said that, I am now using ORM's mainly Reactor, Sorry Mark haven't got the time to look at your Transfer framework just yet (still using reactor in MG:U). So this becomes a non issue for me again. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Tuesday, 6 March 2007 11:48 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom On 3/6/07, Duncan [EMAIL PROTECTED] wrote: Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Sigh, and I mean, *BIG sigh*. :-) Andrew, what about this set of comments argues in favor of a connection between CFQUERYPARAM and the cached queries feature, which you referred to in your earlier note? Are you referring to the 2. Change [Max Pooled Statements] in Datasource Advanced Settings to zero.? That's not about the cached queries feature nor its setting, which you referred to twice on Sunday. What that's referring to is, as it says, a setting in the Datasource connection. That specific driver setting is a feature for SQL Server regarding pooling of prepared statements, or what a CFQUERYPARAM causes to be created (among other things). So, as I've been trying to say from the very beginning of this thread, we need to keep the discussion of cached queries (storing the results in memory for later reuse) very separate from the discussion of prepared statements (as affected by CFQUERYPARAM, and any pooling of them in CF via this max pooled statements or their caching in the database. That's the only reason I've pressed for clarity over and over. It's all the subject of frequent misunderstanding, as this thread has shown. We just need to all get on the same page. It happens all the time in such lists. Hope this has been helpful. As I said before, I certainly don't press to be antagonistic or arrogant. I've admitted learning something already today here (and I blogged about it, mentioning the list as well). /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Monday, March 05, 2007 6:15 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Charlie, The link is http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm The quote on the page is : I checked with one of our technical people and this is what he said: * Most database (including SQLServer) presume that the database structure does not change between re-uses of parameterized queries. Here are three possible solutions. Try #1 first - it may be enough to fix the problem. With #2 and #3 efficiency is reduced because the SQL statement will be compiled more often. 1. Avoid using wildcard SELECT * expressions. The expansion of the * is the part of the SQL statement most likely to change when the schema is altered, making the compiled SQL statement become invalid. If the specific fields are listed (and presuming they are still valid fields after the schema change) the compiled statement might still be useable. 2. Change [Max Pooled Statements] in Datasource Advanced Settings to zero. 3. Use inline arguments instead of cfqueryparam to cause the SQL statement be re-compiled by SQLServer for every request. * Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
*sigh* Well Charlie, when you pushed for time and you reply to something you can't always think of the top of your head. In this case I was trying to picture the settings in the administrator. Now in my defence, until Duncan came out and said it. It was still not very clear on how he was doing the query, and I was just being as specific as possible for him to look at possible solutions to the problem. However when it came to the actual fix for the problem, oh well I got confused in writing my reply :-( Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Tuesday, 6 March 2007 1:07 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Sigh, and I mean, *BIG sigh*. :-) Andrew, what about this set of comments argues in favor of a connection between CFQUERYPARAM and the cached queries feature, which you referred to in your earlier note? Are you referring to the 2. Change [Max Pooled Statements] in Datasource Advanced Settings to zero.? That's not about the cached queries feature nor its setting, which you referred to twice on Sunday. What that's referring to is, as it says, a setting in the Datasource connection. That specific driver setting is a feature for SQL Server regarding pooling of prepared statements, or what a CFQUERYPARAM causes to be created (among other things). So, as I've been trying to say from the very beginning of this thread, we need to keep the discussion of cached queries (storing the results in memory for later reuse) very separate from the discussion of prepared statements (as affected by CFQUERYPARAM, and any pooling of them in CF via this max pooled statements or their caching in the database. That's the only reason I've pressed for clarity over and over. It's all the subject of frequent misunderstanding, as this thread has shown. We just need to all get on the same page. It happens all the time in such lists. Hope this has been helpful. As I said before, I certainly don't press to be antagonistic or arrogant. I've admitted learning something already today here (and I blogged about it, mentioning the list as well). /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Monday, March 05, 2007 6:15 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Charlie, The link is http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm The quote on the page is : I checked with one of our technical people and this is what he said: * Most database (including SQLServer) presume that the database structure does not change between re-uses of parameterized queries. Here are three possible solutions. Try #1 first - it may be enough to fix the problem. With #2 and #3 efficiency is reduced because the SQL statement will be compiled more often. 1. Avoid using wildcard SELECT * expressions. The expansion of the * is the part of the SQL statement most likely to change when the schema is altered, making the compiled SQL statement become invalid. If the specific fields are listed (and presuming they are still valid fields after the schema change) the compiled statement might still be useable. 2. Change [Max Pooled Statements] in Datasource Advanced Settings to zero. 3. Use inline arguments instead of cfqueryparam to cause the SQL statement be re-compiled by SQLServer for every request. * Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin. These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was from Tom MacKean.) PPS With all these folks saying, I'm a SELECT * user, it's starting to sound like a rehab meeting. Hi, I'm Tom, and I'm a Select * user. :-) /Charlie http://www.carehart.org/blog/ _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Monday, March 05, 2007 7:48 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~--- attachment: attfc7c2.jpg
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
+1 for ORMs. Or at the very least, wrap your data access in a bean or something, so at least you only write the query once. Andrew Scott wrote: Well, In my defence I use select * from still too, but I do not use cfqueryparam in my cfcs either so it is not an issue for me. But having said that, I am now using ORMs mainly Reactor, Sorry Mark havent got the time to look at your Transfer framework just yet (still using reactor in MG:U). So this becomes a non issue for me again. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au http://www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 *From:* cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] *On Behalf Of *Tom MacKean *Sent:* Tuesday, 6 March 2007 11:48 AM *To:* cfaussie@googlegroups.com *Subject:* [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom On 3/6/07, *Duncan* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Thanks Charlie, I'm alternating between Dreamweaver and Eclipse at the moment, depending on the project. Since my original post, I found a button in MySQL Manager ( http://www.mysqlmanager.com/) that copies all field names to clipboard. Reasonably easy. Cheers, Tom 12-step MacKean On 3/6/07, Charlie Arehart [EMAIL PROTECTED] wrote: Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin. These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was from Tom MacKean.) PPS With all these folks saying, I'm a SELECT * user, it's starting to sound like a rehab meeting. Hi, I'm Tom, and I'm a Select * user. :-) /Charlie http://www.carehart.org/blog/ -- *From:* cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] *On Behalf Of *Tom MacKean *Sent:* Monday, March 05, 2007 7:48 PM *To:* cfaussie@googlegroups.com *Subject:* [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~--- attachment: attfc7c2.jpg
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Tom, If you are using CFEclipse can I suggest you look at a tool called SQL Explorer, that will allow you to create your query and copy and paste that into your code, also very good because it is a JDBC aware so it will connect to any know DB using JDBC drivers. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Tuesday, 6 March 2007 2:23 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) Thanks Charlie, I'm alternating between Dreamweaver and Eclipse at the moment, depending on the project. Since my original post, I found a button in MySQL Manager (http://www.mysqlmanager.com/ ) that copies all field names to clipboard. Reasonably easy. Cheers, Tom 12-step MacKean On 3/6/07, Charlie Arehart [EMAIL PROTECTED] wrote: Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin. These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was from Tom MacKean.) PPS With all these folks saying, I'm a SELECT * user, it's starting to sound like a rehab meeting. Hi, I'm Tom, and I'm a Select * user. :-) /Charlie http://www.carehart.org/blog/ _ From: cfaussie@googlegroups.com [mailto: mailto:cfaussie@googlegroups.com [EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Monday, March 05, 2007 7:48 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~--- attachment: image001.jpg
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
If using Eclipse I've found quantumdb to also be good. http://quantum.sourceforge.net Although it does require you to make sure that the Eclipse GEF plugin is installed, and I've been having trouble with the latest version. com.quantum.feature_3.0.1.bin.dist.zip seems to work o.k. Cheers Gareth. Andrew Scott wrote: Tom, If you are using CFEclipse can I suggest you look at a tool called SQL Explorer, that will allow you to create your query and copy and paste that into your code, also very good because it is a JDBC aware so it will connect to any know DB using JDBC drivers. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone:+613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of Tom MacKean Sent: Tuesday, 6 March 2007 2:23 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) Thanks Charlie, I'm alternating between Dreamweaver and Eclipse at the moment, depending on the project. Since my original post, I found a button in MySQL Manager (http://www.mysqlmanager.com/ ) that copies all field names to clipboard. Reasonably easy. Cheers, Tom "12-step" MacKean On 3/6/07, Charlie Arehart [EMAIL PROTECTED] wrote: Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin.These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was fromTom "MacKean".) PPS With all these folks saying, "I'm a SELECT * user", it's starting to sound like a rehab meeting. "Hi, I'm Tom, and I'm a Select * user." :-) /Charlie http://www.carehart.org/blog/ From: cfaussie@googlegroups.com [mailto: cfaussie@googlegroups.com] On Behalf Of Tom MacKean Sent: Monday, March 05, 2007 7:48 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating abig long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
We are not caching any templates. In CFAdmin there are no cached templates, trusted cache or saving of class files. We are not using a framework, its as simple as putting a cfquery into a .cfm page and running it directly in the browser. It is only on queries with select * in them. On queries that are specifically tailored to pull only certain columns (i.e. no star *) it does not happen. We are running 7,0,1,116466 CFMX 7 Standard edition, using SQL 2000, and the standard Microsoft SQL Server Driver. We havent updated CF to 7.0.2 because none of the items in the issues list for the updater or patches have deemed it to be necessary in our case. Now hopefully you can see why I am asking about this - its a simple simple thing we are doing, but the answer doesnt look to be simple. Charlie, I will introduce myself at WebDU. On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: Again, this is going down the template cache and query cache routes, in trying to solve Duncan's problem with his CFQUERYPARAM error. Please do tell us, Duncan, if it's just the SELECT * or if indeed the problem remains even after that. But the points Andrew makes before are indeed often valuable ones when you have curious problems of errors sticking around when it's not obvious why they should. Good call, too, on implying the potential difference among DB drivers. That's another way to try to resolve curious problems, sometimes. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 10:08 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam The suggestions I had mentioned, I didn't think of Trusted Cache but I never have that ticked for development but that might cause it and the other is Save Class Files but you will also need to delete the class files that are generated there as well. The other option is Maximum number of cache queries, but I think and am not 100% sure that this is for the actual attribute cache for query tags! Now if you have done the above, we need more info about your environment that might cause this. Is the query in a cfc, that might be used in a scope like session or application or using a framework like MG:U, fusebox, Mach-II that might implement its own form of caching of queries and or components. Other than these, I cannot see any other reason without seeing the code that might be causing this. And are you using standard, coldfusion database connection and not a JDBC alternative? HTH in some way. Andrew Scott -- Duncan I Loxton [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
does CF cache the query or is the DB that caches it My understanding is: neither of these systems cache the query. What happens is SQL creates an execution plan when it gets a SQL statement to process. Where cfqueryparam is used, this plan is saved, (I would guess this is an instruction passed by Java to have this happen) and on subsequent processing takes this step out because it has already created it. Hence the performance gain. Thats my understanding - I think its close, however I know I am probably missing the nuances of this process. Duncan On 3/5/07, Andrew Scott [EMAIL PROTECTED] wrote: Charlie, Well then I will try to not to pick on you then:-) What I said was I see no reason to use cfqueryparam for security originally, because I was unaware of the performance boost by it caching the query. But having said that the RDBMS has to support bind variables first for it to cache and work this way to begin with. When I mention SP, I was saying that I would prefer to use them and not referring to ou :-) Now all that aside after I think about it more, I can see a huge benefit in using stored procedures not for security but for the caching, but I would like to know does CF cache the query or is the DB that caches it? Just in case someone knows and can post before I get a chance to do some research on it. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone:+613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Saturday, 3 March 2007 3:44 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Andrew, I didn't say anything about SPs, nor did I (or would I) argue against them. In fact, there's nothing in my reply nor in your note I was replying to that discussed them. In my note to you (which I'll assume you are replying to here, since you didn't include it but I did offer two last night), I was just talking about clarifying which caching was being discussed, since you mentioned query caching while Duncan had referred to plan caching. And then we were suggesting not to just view CFQueryParam as a security tool. That said, can you reframe your question below? If it was just referring, like Duncan, to my point about Select *, I hope I've answered that in my last note. And let's not make this pick on Charlie day. Goodness, I'm just here to try to help. I never mean to get into a pissing match with anyone. I'm certainly not trying to smack anyone down or show my stick is bigger. I just like to share info and point out opportunities to correct or clarify. Email's a notoriously bad communication vehicle when it comes to conveying emotion. I just want to be clear that I have don't mean ever in my notes to be picking a fight or trying to one up anyone. I hope that helps put my responses in perspective. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 10:30 PM To: cfaussie@googlegroups.com Subject: [cfaussie] CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam Charlie, Can you do me a favour, and explain the typically valuable for performance issue. I don't understand that, because to me better performance would mean to have it as an SP to begin with. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone:+613 8676 4223 Mobile: 0404 998 273 -- Duncan I Loxton [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Duncan, But the Admin says Cached Queries so I am going to guess its CF, just wanted to make sure I was right on my assumption. And if you read my post, hey Charlie (yells out) correct me you seem to know.. If you set this setting to zero, there will be no caching and no performance gain. So I guess I just answered my own question, CF caches the queries. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
I think CF would only cache the queries if cachedafter= or cachedwithin= was used. Andrew Scott [EMAIL PROTECTED] 05/03/2007 10:38 am Duncan, But the Admin says Cached Queries so I am going to guess its CF, just wanted to make sure I was right on my assumption. And if you read my post, hey Charlie (yells out) correct me you seem to know.. If you set this setting to zero, there will be no caching and no performance gain. So I guess I just answered my own question, CF caches the queries. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam
Acually not sure, but the problem Duncan was having was suggested my Macromedia so that shows you it is a known problem and how long it has been around for, suggests for the cfqueryparam slect * from problem to set the cached queries queue to zero. But it might effect that attribute as well Charlie might be able to fill us in more maybe. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Scott Thornton Sent: Monday, 5 March 2007 11:01 AM To: cfaussie@googlegroups.com Subject: [cfaussie] CFQUERYPARAM was RE: [cfaussie] Re: @#$!! queryparam I think CF would only cache the queries if cachedafter= or cachedwithin= was used. Andrew Scott [EMAIL PROTECTED] 05/03/2007 10:38 am Duncan, But the Admin says Cached Queries so I am going to guess its CF, just wanted to make sure I was right on my assumption. And if you read my post, hey Charlie (yells out) correct me you seem to know.. If you set this setting to zero, there will be no caching and no performance gain. So I guess I just answered my own question, CF caches the queries. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
But it is, And I did post the answer to you in one of my previous posts to this post. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Monday, 5 March 2007 8:47 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam We are not caching any templates. In CFAdmin there are no cached templates, trusted cache or saving of class files. We are not using a framework, its as simple as putting a cfquery into a .cfm page and running it directly in the browser. It is only on queries with select * in them. On queries that are specifically tailored to pull only certain columns (i.e. no star *) it does not happen. We are running 7,0,1,116466 CFMX 7 Standard edition, using SQL 2000, and the standard Microsoft SQL Server Driver. We havent updated CF to 7.0.2 because none of the items in the issues list for the updater or patches have deemed it to be necessary in our case. Now hopefully you can see why I am asking about this - its a simple simple thing we are doing, but the answer doesnt look to be simple. Charlie, I will introduce myself at WebDU. On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: Again, this is going down the template cache and query cache routes, in trying to solve Duncan's problem with his CFQUERYPARAM error. Please do tell us, Duncan, if it's just the SELECT * or if indeed the problem remains even after that. But the points Andrew makes before are indeed often valuable ones when you have curious problems of errors sticking around when it's not obvious why they should. Good call, too, on implying the potential difference among DB drivers. That's another way to try to resolve curious problems, sometimes. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 10:08 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam The suggestions I had mentioned, I didn't think of Trusted Cache but I never have that ticked for development but that might cause it and the other is Save Class Files but you will also need to delete the class files that are generated there as well. The other option is Maximum number of cache queries, but I think and am not 100% sure that this is for the actual attribute cache for query tags! Now if you have done the above, we need more info about your environment that might cause this. Is the query in a cfc, that might be used in a scope like session or application or using a framework like MG:U, fusebox, Mach-II that might implement its own form of caching of queries and or components. Other than these, I cannot see any other reason without seeing the code that might be causing this. And are you using standard, coldfusion database connection and not a JDBC alternative? HTH in some way. Andrew Scott -- Duncan I Loxton [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems) readily say find all results that say select * us bad. Here are a few areas where this has been discussed and/or debated: http://www.parseerror.com/sql/select*isevil.html http://www.sitepoint.com/forums/showthread.php?t=417457 http://www.adopenstatic.com/faq/selectstarisbad.asp http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,si d63_gci978334,00.html http://rip747.wordpress.com/2006/07/10/to-select-or-not-its-a-matter-of-opin ion/ That last one above is from a CF perspective, and does discuss the question from the perspective of what if I *do* want all the columns. I'll leave it to you and others to parse through all the available info to decide best for yourself. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Thursday, March 01, 2007 10:21 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Charlie, I didnt think that would be affecting it, I am more than aware that its bad practice, and as it happens the queries in question do have *, column1, column2 etc in them. I know that you are pulling more data than necessary with a * therefore slowing down processing with data processing an transfer. That's good practice for all manner of reasons Would you care to spell out other reasons? Duncan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Well, sure, like the point about Select * being bad when you select more than just the columns you need, so too is the benefit of CFQUERYPARAM (from its performance perspective) only valuable for databases that support bind variables (or what SQL Server calls parameterized queries). I'm sorry that I didn't state both points, if anyone felt I should have. I just kind of saw them as a given, but it's totally fair to say that we never know who's reading the list, so we should be as clear as possible. Indeed, along the same lines, I'll say that it was useful to hear about that observation of a newline within a query being an issue. Do you have that reference still? It might be useful to track down if that could have been a problem in either the DB driver or CF itself, which may since then have been resolved in an update or hotfix. That's a frequent challenge with info we find out there. Even on the Adobe site itself, some info may simply no longer apply--or it could depend on what release/hotfix you have. So it goes back to the point above: we *would* be wise to always challenge even old saws and conventional wisdom. The real answer is to test for ourselves, on our platforms. Of course, doing that well isn't all that easy, which is why we end up with the commonly held theories (and occasional old wives tales). That's what's great about this business. It's a constant challenge to learn and grow. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 10:42 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Charlie, I did a bit of research into cfqueryparam to see what you meant by ther performance but it is only for those DBMSs that support bind variables anyway. But I also was reading the comments to this and came across a know unwanted feature, I say that because it is not considered a bug by Adobe, or Macromedia / Allaire at that time. It appears that this error can occur when doing something like this. Select * From Table And not if you have this. Select * From Table But it can, just not as frequent and the solution was to restart the CF Application server until it happened again. But they also said to avoid at all costs using select * from table and to use this Select column1, column2 From table As the change is more likely to be picked up by the caching of the query. Or you could use the setting in the Administrator for Cached queries and set that to zero. Duncan, I hope that helps you out here. Andrew Scott --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Again, this is going down the template cache and query cache routes, in trying to solve Duncan's problem with his CFQUERYPARAM error. Please do tell us, Duncan, if it's just the SELECT * or if indeed the problem remains even after that. But the points Andrew makes before are indeed often valuable ones when you have curious problems of errors sticking around when it's not obvious why they should. Good call, too, on implying the potential difference among DB drivers. That's another way to try to resolve curious problems, sometimes. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 10:08 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam The suggestions I had mentioned, I didn't think of Trusted Cache but I never have that ticked for development but that might cause it and the other is Save Class Files but you will also need to delete the class files that are generated there as well. The other option is Maximum number of cache queries, but I think and am not 100% sure that this is for the actual attribute cache for query tags! Now if you have done the above, we need more info about your environment that might cause this. Is the query in a cfc, that might be used in a scope like session or application or using a framework like MG:U, fusebox, Mach-II that might implement its own form of caching of queries and or components. Other than these, I cannot see any other reason without seeing the code that might be causing this. And are you using standard, coldfusion database connection and not a JDBC alternative? HTH in some way. Andrew Scott --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
-or-not-its-a-matter-of-opin ion/ That last one above is from a CF perspective, and does discuss the question from the perspective of what if I *do* want all the columns. I'll leave it to you and others to parse through all the available info to decide best for yourself. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Thursday, March 01, 2007 10:21 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Charlie, I didnt think that would be affecting it, I am more than aware that its bad practice, and as it happens the queries in question do have *, column1, column2 etc in them. I know that you are pulling more data than necessary with a * therefore slowing down processing with data processing an transfer. That's good practice for all manner of reasons Would you care to spell out other reasons? Duncan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Duncan, The query is only cached when you tell it to be cached. But are you using any other framework like reactor or transfer or even MG:U, or even have this in a cfc that might be stored in a session or application scope. One other thing I would like to point out that a lot of people do not take for granted, but tend to just do it anyway. I chose not to, but that is my choice. If the query is in a cfc, and that function uses arguments that will be used in the query for example cffunction name=getEmployee cfargument name=EmployeeId type=numeric required=true / cfset var Record = '' / cfquery name=Record datasource= Select * from Employees where EmployeeId = #Arguments.EmployeeId# /cfquery /cffunction I will never ever use the cfqueryparam, and the reason being is that the function itself will take care of the validation for me. However, although I did say never a string is a different story and will use it for a string. I know this has nothing to do with your problem, but just wanted to make that statement because I still see people use the cfqueryparam in places I know it is not necessary to use. If you would like to post a more detailed example on how you are using this, whether it is in any of my original methods then we can help you further but one thing to also take into consideration is the caching of the coldfusion class files too, this should never be switched on for development purposes. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, 2 March 2007 11:23 AM To: cfaussie Subject: [cfaussie] @#$!! queryparam I think its to do with the binding or the caching of the query plan but when we change a table in the DB, like remove a column or change the length of a varchar, all the queries that use that table and have a queryparam break. We get messages like [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. on queries as simple as a Select * from table where id = cfqueryparam cfsqltype=cf_sql_integer value=#id# and we didnt even change the id column (obviously) At the moment the only way we can find of making it all work again is to remove all the params or to restart cf or sql. All of which are bad in their own way even though its only on the dev box. Strangely I havent come across this before, previous installations havent had this, but I have just moved to inherit some software and dev boxes and I cant work out why this is going on. All the settings in the cfadmin db connection are the defaults. Anyone else have this problem / have a solution? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Scott, Well although I know what you said, I see no reason to add overhead to my application to provide a stop measure for SQL injection when I have already taken care of it before my code ever reaches there in a cffunction. As far as making sure it is an integer instead of numeric, I couldn't care less it's not an overhead I will put into my applications. And to be honest, I would prefer to write SP's and have it on the DB side if that to be the case. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Scott Thornton Sent: Friday, 2 March 2007 12:25 PM To: cfaussie@googlegroups.com Subject: [cfaussie] @#$!! queryparam Andrew, I disagree. Although cfqueryparam performs vailidation, it is not the reason you should be using it. cfqueryparam makes the database engine use parameter binding on your queries. For example your query below would look different to the db engine every time it is run eg: Select * from Employees where EmployeeId = 1 Select * from Employees where EmployeeId = 2 Select * from Employees where EmployeeId = 3 so your database engine builds different query execution plans for each of these queries (in addition to validating the query, checking\casting the parameter types etc) etc But with parameter binding the databse engine is executing something that looks more like a stored procedure, Select * from Employees where EmployeeId = @var1 Here is an example from a SQL profile trace on my server: declare @P1 int set @P1=30 exec sp_prepexec @P1 output, N'@P1 decimal(38,0)', N'SELECT IsNull(COUNT(ITEM.SB_INVOICE_ITEM_ID),0) AS CNT, IsNull(SUM(ITEM.SB_INVOICE_COST),0) AS TOT_COST, IsNull(SUM(CASE WHEN ITEM.SB_ITEM_STATUS_CODE = ''ER'' THEN 1 ELSE 0 END),0) AS ERR_COUNT FROM SB_INVOICE_ITEM ITEM WHERE ITEM.SB_INV_BATCH_ID = @P1 ', 1387 select @P1 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
I should add to this that the queries are not using any caching, they are not in a cfc, and are not loaded into any scopes like the application. Simple, inline, straightforward queries. On 3/2/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott - thats precisely why we use cfqueryparam - and the performance increase is marked, especially on inserts. There is a performance hit on the first time the query is put together, but after that you save time. Andrew - I thought the cached templates could be it - but its not, we just ran a control test: turn off cache, restart cf, change column, try query. And it still errors. On Mar 2, 1:33 pm, Andrew Scott [EMAIL PROTECTED] wrote: Scott, Well although I know what you said, I see no reason to add overhead to my application to provide a stop measure for SQL injection when I have already taken care of it before my code ever reaches there in a cffunction. As far as making sure it is an integer instead of numeric, I couldn't care less it's not an overhead I will put into my applications. And to be honest, I would prefer to write SP's and have it on the DB side if that to be the case. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Scott Thornton Sent: Friday, 2 March 2007 12:25 PM To: cfaussie@googlegroups.com Subject: [cfaussie] @#$!! queryparam Andrew, I disagree. Although cfqueryparam performs vailidation, it is not the reason you should be using it. cfqueryparam makes the database engine use parameter binding on your queries. For example your query below would look different to the db engine every time it is run eg: Select * from Employees where EmployeeId = 1 Select * from Employees where EmployeeId = 2 Select * from Employees where EmployeeId = 3 so your database engine builds different query execution plans for each of these queries (in addition to validating the query, checking\casting the parameter types etc) etc But with parameter binding the databse engine is executing something that looks more like a stored procedure, Select * from Employees where EmployeeId = @var1 Here is an example from a SQL profile trace on my server: declare @P1 int set @P1=30 exec sp_prepexec @P1 output, N'@P1 decimal(38,0)', N'SELECT IsNull(COUNT(ITEM.SB_INVOICE_ITEM_ID),0) AS CNT, IsNull(SUM(ITEM.SB_INVOICE_COST),0) AS TOT_COST, IsNull(SUM(CASE WHEN ITEM.SB_ITEM_STATUS_CODE = ''ER'' THEN 1 ELSE 0 END),0) AS ERR_COUNT FROM SB_INVOICE_ITEM ITEM WHERE ITEM.SB_INV_BATCH_ID = @P1 ', 1387 select @P1 -- Duncan I Loxton [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Yes, as Scott later said, Andrew, you don't want to confuse caching of the query with what Duncan said, which was caching of the query plan. And as Scott clarified, you definitely don't want to spread the misconception that cfqueryparam is just for security. The value in query plan caching can be greater, for performance sake. Still, you're right that a query inside a CFC or UDF whose variables are all validated with CFARGUMENT just doesn't need the security aspect of CFQueryParam, but it's still typically valuable for performance. (I am giving a talk to the Atlanta SQL Server group on the subject of query plan caching, and some important changes in SQL 2005, as well as how to measure the impact of doing it or not. I've been planning to make a variation of that talk for CF audiences, focusing on MySQL and perhaps Oracle as well.) Anyway, Scott also got to the point of Duncan's problem below. It's your use of Select *. That's a notorious problem when using CFQUERYPARAM. The simple solution is to stop using that. That's good practice for all manner of reasons, not the least of which this. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 7:58 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Duncan, The query is only cached when you tell it to be cached. But are you using any other framework like reactor or transfer or even MG:U, or even have this in a cfc that might be stored in a session or application scope. One other thing I would like to point out that a lot of people do not take for granted, but tend to just do it anyway. I chose not to, but that is my choice. If the query is in a cfc, and that function uses arguments that will be used in the query for example cffunction name=getEmployee cfargument name=EmployeeId type=numeric required=true / cfset var Record = '' / cfquery name=Record datasource= Select * from Employees where EmployeeId = #Arguments.EmployeeId# /cfquery /cffunction I will never ever use the cfqueryparam, and the reason being is that the function itself will take care of the validation for me. However, although I did say never a string is a different story and will use it for a string. I know this has nothing to do with your problem, but just wanted to make that statement because I still see people use the cfqueryparam in places I know it is not necessary to use. If you would like to post a more detailed example on how you are using this, whether it is in any of my original methods then we can help you further but one thing to also take into consideration is the caching of the coldfusion class files too, this should never be switched on for development purposes. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Goodness, too many caches! Now you're talking about cached templates, Duncan. It sure seems to me that Andrew was referring to query caching, since he said, The query is only cached when you tell it to be cached and later mentioned storing data in shared scopes. And then of course Scott was referring to the database's query plan caching. In fact, this is a good place to remind that my talk at WebDU is going to be on 'Caching In on CF Performance', and it will be specifically about trying to bring clarity to this jumble of caching (and there are several more) that often confuse even experienced developers. Of course, we won't have time in an hour to go into depth on any one, but I will certainly strive to help get folks on the same page in understanding these and some other little-known caching aspects in CFML and related things (database, web server, browser). Caching's a very good thing, usually. It's just always enabled by default (and sometimes one can go crazy with it and cost more resources than are saved). /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, March 01, 2007 10:01 PM To: cfaussie Subject: [cfaussie] Re: @#$!! queryparam Scott - thats precisely why we use cfqueryparam - and the performance increase is marked, especially on inserts. There is a performance hit on the first time the query is put together, but after that you save time. Andrew - I thought the cached templates could be it - but its not, we just ran a control test: turn off cache, restart cf, change column, try query. And it still errors. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
The suggestions I had mentioned, I didnt think of Trusted Cache but I never have that ticked for development but that might cause it and the other is Save Class Files but you will also need to delete the class files that are generated there as well. The other option is Maximum number of cache queries, but I think and am not 100% sure that this is for the actual attribute cache for query tags! Now if you have done the above, we need more info about your environment that might cause this. Is the query in a cfc, that might be used in a scope like session or application or using a framework like MG:U, fusebox, Mach-II that might implement its own form of caching of queries and or components. Other than these, I cannot see any other reason without seeing the code that might be causing this. And are you using standard, coldfusion database connection and not a JDBC alternative? HTH in some way. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, 2 March 2007 2:01 PM To: cfaussie Subject: [cfaussie] Re: @#$!! queryparam Scott - thats precisely why we use cfqueryparam - and the performance increase is marked, especially on inserts. There is a performance hit on the first time the query is put together, but after that you save time. Andrew - I thought the cached templates could be it - but its not, we just ran a control test: turn off cache, restart cf, change column, try query. And it still errors. On Mar 2, 1:33 pm, Andrew Scott [EMAIL PROTECTED] wrote: Scott, Well although I know what you said, I see no reason to add overhead to my application to provide a stop measure for SQL injection when I have already taken care of it before my code ever reaches there in a cffunction. As far as making sure it is an integer instead of numeric, I couldn't care less it's not an overhead I will put into my applications. And to be honest, I would prefer to write SP's and have it on the DB side if that to be the case. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Scott Thornton Sent: Friday, 2 March 2007 12:25 PM To: cfaussie@googlegroups.com Subject: [cfaussie] @#$!! queryparam Andrew, I disagree. Although cfqueryparam performs vailidation, it is not the reason you should be using it. cfqueryparam makes the database engine use parameter binding on your queries. For example your query below would look different to the db engine every time it is run eg: Select * from Employees where EmployeeId = 1 Select * from Employees where EmployeeId = 2 Select * from Employees where EmployeeId = 3 so your database engine builds different query execution plans for each of these queries (in addition to validating the query, checking\casting the parameter types etc) etc But with parameter binding the databse engine is executing something that looks more like a stored procedure, Select * from Employees where EmployeeId = @var1 Here is an example from a SQL profile trace on my server: declare @P1 int set @P1=30 exec sp_prepexec @P1 output, N'@P1 decimal(38,0)', N'SELECT IsNull(COUNT(ITEM.SB_INVOICE_ITEM_ID),0) AS CNT, IsNull(SUM(ITEM.SB_INVOICE_COST),0) AS TOT_COST, IsNull(SUM(CASE WHEN ITEM.SB_ITEM_STATUS_CODE = ''ER'' THEN 1 ELSE 0 END),0) AS ERR_COUNT FROM SB_INVOICE_ITEM ITEM WHERE ITEM.SB_INV_BATCH_ID = @P1 ', 1387 select @P1 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Did you delete any cached templates in the CFIDE folder? On 02/03/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott - thats precisely why we use cfqueryparam - and the performance increase is marked, especially on inserts. There is a performance hit on the first time the query is put together, but after that you save time. Andrew - I thought the cached templates could be it - but its not, we just ran a control test: turn off cache, restart cf, change column, try query. And it still errors. On Mar 2, 1:33 pm, Andrew Scott [EMAIL PROTECTED] wrote: Scott, Well although I know what you said, I see no reason to add overhead to my application to provide a stop measure for SQL injection when I have already taken care of it before my code ever reaches there in a cffunction. As far as making sure it is an integer instead of numeric, I couldn't care less it's not an overhead I will put into my applications. And to be honest, I would prefer to write SP's and have it on the DB side if that to be the case. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Scott Thornton Sent: Friday, 2 March 2007 12:25 PM To: cfaussie@googlegroups.com Subject: [cfaussie] @#$!! queryparam Andrew, I disagree. Although cfqueryparam performs vailidation, it is not the reason you should be using it. cfqueryparam makes the database engine use parameter binding on your queries. For example your query below would look different to the db engine every time it is run eg: Select * from Employees where EmployeeId = 1 Select * from Employees where EmployeeId = 2 Select * from Employees where EmployeeId = 3 so your database engine builds different query execution plans for each of these queries (in addition to validating the query, checking\casting the parameter types etc) etc But with parameter binding the databse engine is executing something that looks more like a stored procedure, Select * from Employees where EmployeeId = @var1 Here is an example from a SQL profile trace on my server: declare @P1 int set @P1=30 exec sp_prepexec @P1 output, N'@P1 decimal(38,0)', N'SELECT IsNull(COUNT(ITEM.SB_INVOICE_ITEM_ID),0) AS CNT, IsNull(SUM(ITEM.SB_INVOICE_COST),0) AS TOT_COST, IsNull(SUM(CASE WHEN ITEM.SB_ITEM_STATUS_CODE = ''ER'' THEN 1 ELSE 0 END),0) AS ERR_COUNT FROM SB_INVOICE_ITEM ITEM WHERE ITEM.SB_INV_BATCH_ID = @P1 ', 1387 select @P1 -- Peter Tilbrook ColdGen Internet Solutions President, ACT and Region ColdFusion Users Group PO Box 2247 Queanbeyan, NSW, 2620 AUSTRALIA http://www.coldgen.com/ http://www.actcfug.com/ Tel: +61-2-6284-2727 Mob: +61-0432-897-437 Email: [EMAIL PROTECTED] MSN Messenger Live: Desktop General --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: @#$!! queryparam
Charlie, I didnt think that would be affecting it, I am more than aware that its bad practice, and as it happens the queries in question do have *, column1, column2 etc in them. I know that you are pulling more data than necessary with a * therefore slowing down processing with data processing an transfer. That's good practice for all manner of reasons Would you care to spell out other reasons? Duncan On 3/2/07, Charlie Arehart [EMAIL PROTECTED] wrote: Yes, as Scott later said, Andrew, you don't want to confuse caching of the query with what Duncan said, which was caching of the query plan. And as Scott clarified, you definitely don't want to spread the misconception that cfqueryparam is just for security. The value in query plan caching can be greater, for performance sake. Still, you're right that a query inside a CFC or UDF whose variables are all validated with CFARGUMENT just doesn't need the security aspect of CFQueryParam, but it's still typically valuable for performance. (I am giving a talk to the Atlanta SQL Server group on the subject of query plan caching, and some important changes in SQL 2005, as well as how to measure the impact of doing it or not. I've been planning to make a variation of that talk for CF audiences, focusing on MySQL and perhaps Oracle as well.) Anyway, Scott also got to the point of Duncan's problem below. It's your use of Select *. That's a notorious problem when using CFQUERYPARAM. The simple solution is to stop using that. That's good practice for all manner of reasons, not the least of which this. /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, March 01, 2007 7:58 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Duncan, The query is only cached when you tell it to be cached. But are you using any other framework like reactor or transfer or even MG:U, or even have this in a cfc that might be stored in a session or application scope. One other thing I would like to point out that a lot of people do not take for granted, but tend to just do it anyway. I chose not to, but that is my choice. If the query is in a cfc, and that function uses arguments that will be used in the query for example cffunction name=getEmployee cfargument name=EmployeeId type=numeric required=true / cfset var Record = '' / cfquery name=Record datasource= Select * from Employees where EmployeeId = #Arguments.EmployeeId# /cfquery /cffunction I will never ever use the cfqueryparam, and the reason being is that the function itself will take care of the validation for me. However, although I did say never a string is a different story and will use it for a string. I know this has nothing to do with your problem, but just wanted to make that statement because I still see people use the cfqueryparam in places I know it is not necessary to use. If you would like to post a more detailed example on how you are using this, whether it is in any of my original methods then we can help you further but one thing to also take into consideration is the caching of the coldfusion class files too, this should never be switched on for development purposes. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone:+613 8676 4223 Mobile: 0404 998 273 -- Duncan I Loxton [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
