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

Gary Pupurs garyp at speakeasy.net
Thu Dec 14 21:37:14 CST 2000


> > A while back Rudy mentioned something about "the evil of * in select
> > statements" and said something like (reference available) after it.
> >
> > Well - I'm sort of asking Rudy - but I'd like to hear more about this. I
> > know the general principal is "don't pull more from the database than
> > you need, as this impacts performance, etc" - but I'd like to hear it
> > straight from the SQL-Gods's Mouth. :-)

There's another great reason for not using SELECT * that becomes apparent
the more complex a project becomes and the more people who work on it.  For
example, if I'm coding an ASP page (more likely working on one that someone
else wrote) and I need to refer to a field, it's much easier to look in the
SELECT statement for the field name rather than opening up the DB client,
finding the particular database and doing the join manually to find what
fields are being returned.  Think of it as self-documenting code.  When I
take a look at a page someone is having problems with, I want to look at
that SQL statement and immediately see what it's returning.  Don't waste my
time by forcing me to open the database and run the query manually before I
can even begin to help you... It helps solve other problems too, such as
trying to use a field you thought SELECT * was returning but actually isn't.

I don't know too much about the performance side of it, other than the
common sense concepts that you shouldn't make the database do more than you
need it to, and that if you're grabbing more fields than you use, those are
being temporarily stored in memory someplace, taking up space.  Multiply
that by all the simultaneous users on a site and you could eat up memory
quickly that could be used for other things...

-Gary

Quick Tip:  If you're doing really complex SQL queries using ASP and MS
Access as the database and you're finding the pages are dog-slow, be sure to
open that ADO conn object with _OLEDB_, and _not_ a DSN via ODBC!  Just
changed only this on a page today with a query and subquery using the IN
operator on several tables joined together.  Performance improvement?  25
seconds to 0.2 seconds! That's a 184x improvement! Yowzers! (Switching from
Access 97 to Access 2000 added another 40% improvement.)








More information about the thelist mailing list