[cfaussie] Re: CFCENTRAL Still Show my Email
This may be a good place to point out that there is a free service (mail-archive.com) which serves as a place to archive and provide search/web interface for mailing lists. Now, with cfaussie using googlegroups, one may ask, "why bother", but I'm just answering the question that Steve may be asking: how does one archive messages from *before* the google group was created? That's just one advantage that the mail-archive service has: someone can feed it past years of archival messages. So even with Google Groups running and archiving messages from this group going forward, it may be worth creating the mail archive as well, if anyone wants to be able to search Steve's archive. It also solves the dilemma of "another backup" that Andrew raised, and it also *does* solve the "hiding email" issue that sparked this thread. I wrote a blog entry about the service last year: http://carehart.org/blog/client/index.cfm/2006/11/20/archive_your_groups_mai ling_list and one somewhat related: http://carehart.org/blog/client/index.cfm/2006/11/30/google_groups_as_list_a rchive /Charlie http://www.carehart.org/blog/ _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Steve Onnis Sent: Thursday, March 01, 2007 10:27 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CFCENTRAL Still Show my Email What I have is more of an archive. I think my message archive goes back about 4-5 years. Still deciding if I am going to keep it up to date or not. What do you guys think? --~--~-~--~~~---~--~~ 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/cfaussi
[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 dont 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 --~--~-~--~~~---~--~~ 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)
I have seen 2 notable reasons to stay away from select * if possible: 1. I have come across several issues with the schema changing in a database and the changes where not reflected in a query that had select * (legacy code). This can make the display layer break in strange ways when the template is expecting a row to be there and it isn't. From experience, this only happens with cfqueryparam with it's caching. 2. Supporting legacy code. If you come across a select *, you don't know what columns you are retrieving unless you look into the structure of the table (or tables) in question. This can be annoying if you are working on a large system with a lot of tables, or using a subset of a large number of tables in an Oracle schema (as is sometimes the case due to expensive licenses). The main reason I have found it to not be best practise is the extra steps you need to take to maintain legacy code that uses select *. I don't know if anyone else would agree, but I would prefer to already know what is going to be in a result set just by looking at the query that was used instead of digging around the db or dumping the result. My 2c, Shane 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/knowledgebaseAns
