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