I read somewhere (I think SQL for Smarties--John Celko) that * in SELECT's is also evil because it forces the db to do the additional overhead of figuring out what the column names are, and hence what to return. -joshua ----- Original Message ----- From: "rudy" <Rudy_Limeback at maritimelife.ca> To: <thelist at lists.evolt.org> Sent: Friday, December 15, 2000 10:28 AM Subject: RE: [thelist] The evils of * in SQL statements... > > I've heard that if you really DO want all the columns back, > > that select * is better then select col1,col2,etc. > > Can someone confirm/deny this? > > hi raymond > > sorry, that's a new one on me > > it would probably depend on the particular database engine, too > > gary's suggestion to list the individual columns makes a lot of sense -- > > > Think of it as self-documenting code.... It helps solve > > other problems too, such as trying to use a field you > > thought SELECT * was returning but actually isn't. > > along the same lines, with SELECT * it is all too easy to write any column > into an ORDER BY or GROUP BY clause, which can lead to inadvertently > running a nonsense query (this is a technical term describing a query that > runs but the output doesn't make sense or is totally useless) > > > I know the general principal is "don't pull more from the > > database than you need, as this impacts performance, etc" > > that's it in a nutshell, joe -- if by "performance" you mean the overall > response time on a web page as perceived by the page visitor > > when a web server calls a database server to get some data, those are two > humungously large blocks of code, of a level of complexity similar to > operating systems (think multi-threading, fully reentrant, memory/buffer > management, et cetera) that have to talk to each other > > this connection is often the slowest part of the process (database engines > being optimized for speed anyway), so yeah, piping more data between them > than you are gonna use is just asking for performance problems... > > there's only one exception to this rule that i can think of, and that's > grabbing everything you can think of out of the database to store in > application and session variables on the web server, with the > understanding that any pages that need data can get it from the variables > instead of querying the database themselves -- but this is really > "stepping outside of the box" and not an indictment of writing queries to > get only what they need > > rudy.ca > > > > --------------------------------------- > For unsubscribe and other options, including > the Tip Harvester and archive of TheList go to: > http://lists.evolt.org Workers of the Web, evolt !