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