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 -~----------~----~----~----~------~----~------~--~---
