[thelist] The evils of * in SQL statements...

[rudy]

> 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