Hi Tom, If you're doing SELECT * for a single record then don't worry about it - the overhead is minuscule. The real problems with SELECT * happen when you are running a query that will return multiple (hundreds/thousands of) records and the overhead can become enormous. And if you are doing this in a display that is only showing one field from each record and is only showing 10 or 20 or 30 records (with 'next' and 'prev' buttons) in a site that has many users hitting the same display - then you are likely to run into real problems.
Cheers, Brett B) Tom MacKean wrote: > 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. 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 -~----------~----~----~----~------~----~------~--~---
