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

[Joshua OIson]

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 !