Message-
From: Leon Oosterwijk [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tue Jul 11 21:58:13 2006
Subject: RE: Select * in SQL
All,
Let me be the devil's advocate and say that I prefer * despite all the
arguments made against it. To me, hand-coding the column names before
It should be in her download here:
CFUnited 2006
Writing Efficient SQL
http://www.webtricks.com/pres.cfm
Then some notes on the preso here:
http://www.talkingtree.com/blog/index.cfm?mode=entryentry=CDA25D0A-50DA-0559-A0104C817FCAD1E8
Will
I think this was one of her tests... it was on a downloadable USDA database I
think.
DECLARE @Start DATETIME,
@End DATETIME,
@Total1 BIGINT,
@Total2 BIGINT,
@Total3 BIGINT,
@LoopCount INT,
@LoopTotal
Any idea why she wrote her own efficiency monitor stuff instead of using
query analyzer?
Will Tomlinson wrote:
I think this was one of her tests... it was on a downloadable USDA database I
think.
DECLARE @Start DATETIME,
@End DATETIME,
@Total1 BIGINT,
Any idea why she wrote her own efficiency monitor stuff instead of using
query analyzer?
Heck I dunno.
Will
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by
Just wondering what DB
According to the examples given in the preso for joins, it looks like it
is an Oracle DB.
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send any spam to this address: [EMAIL
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
I've found this : http://www.adopenstatic.com/faq/selectstarisbad.asp
but I don't thing it effectively counters the argument But I need all
of the columns in the table.
Mind you, that I'm
a few seconds more to type out...but I'm typing it out once. I'm
referring back to it frequently :)
On 7/11/06, Ryan, Terrence [EMAIL PROTECTED] wrote:
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
I've found this : http
...
Ryan, Terrence wrote:
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
I've found this : http://www.adopenstatic.com/faq/selectstarisbad.asp
but I don't thing it effectively counters the argument But I need all
of the columns
question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
I've found this : http://www.adopenstatic.com/faq/selectstarisbad.asp
but I don't thing it effectively counters the argument But I need all
of the columns in the table.
Mind you, that I'm not looking
PROTECTED]
615.370.1530 x737
--//-
-Original Message-
From: Ryan, Terrence [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 11, 2006 10:05 AM
To: CF-Talk
Subject: Select * in SQL
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL
have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
I've found this : http://www.adopenstatic.com/faq/selectstarisbad.asp
but I don't thing it effectively counters the argument But I need all
of the columns in the table.
Mind you, that I'm
I have a dumb question. Can someone point me to a definitive
explanation of why select * in SQL is bad?
There are various performance-related reasons, none of which is that strong
in my opinion. However, it's generally bad to introduce unnecessary
ambiguity, which is what * does. Are you using
, that never happens.
...
Ben Nadel
www.bennadel.com
-Original Message-
From: Ryan, Terrence [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 11, 2006 11:05 AM
To: CF-Talk
Subject: Select * in SQL
I have a dumb question. Can someone point me to a definitive explanation
of why
On 7/11/06, Andy Matthews [EMAIL PROTECTED] wrote:
If you actually need every one of the columns in a single (or multiple)
row(s) then select * saves time. Isn't that we all try to do, save ourselves
time? Why not in this case?
because it doesn't save time in the long run when you're trying to
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
SELECT * is not bad if you need ALL columns.
If you only need a subset of columns in a table, it is better to list
them explicitely, otherwise the query
will unefficiently return unused data
The best reason that I've found in working with CF is that CF or the
driver sometimes does a weird caching thing and if you make a change
in the underlying table, they won't pick it up. When that happens, it
doesn't save you any time, as you'll spend extra time trying to figure
out why what once
1) You're pulling back every row in the specific table
Read Every column ;-/
3) You're not leveraging any of the indexing capabilities of the table
??? How come?
Any available index can still be used depending on the WHERE and ORDER
BY clauses.
--
___
When you use *, the database has to do a lookup to figure out what the
actual column names are in the table, then do the actual select
statement. If you explicitly tell it what column names (even if you
want all of them), you eliminate that overhead.
I don't agree with this argument:
1) any
: Tuesday, July 11, 2006 11:05 AM
To: CF-Talk
Subject: Select * in SQL
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
I've found this : http://www.adopenstatic.com/faq/selectstarisbad.asp
but I don't thing it effectively counters the argument
because it doesn't save time in the long run when you're trying to
maintain or debug your code down the road and you find yourself
wondering which columns specifically are coming back from the query.
Well, this is only when you are wondering which columns specifically are
coming back from the
of why select * in SQL is bad?
I've found this : http://www.adopenstatic.com/faq/selectstarisbad.asp
but I don't thing it effectively counters the argument But I need all
of the columns in the table.
Mind you, that I'm not looking for convincing for myself, rather I'm
doing a code review
-
From: Charlie Griefer [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 11, 2006 10:24 AM
To: CF-Talk
Subject: Re: Select * in SQL
On 7/11/06, Andy Matthews [EMAIL PROTECTED] wrote:
If you actually need every one of the columns in a single (or multiple)
row(s) then select * saves time. Isn't that we
On 7/11/06, Claude Schneegans [EMAIL PROTECTED] wrote:
because it doesn't save time in the long run when you're trying to
maintain or debug your code down the road and you find yourself
wondering which columns specifically are coming back from the query.
Well, this is only when you are
for Jochem to give the final answer on this...
M!ke
-Original Message-
From: Jim [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 11, 2006 10:39 AM
To: CF-Talk
Subject: Re: Select * in SQL
Heres a real world scenario I've come across:
Code throughout the app is using data returned from
-
From: Dawson, Michael [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tue Jul 11 17:03:20 2006
Subject: RE: Select * in SQL
A well-written driver would then throw an ambiguous column name error,
if that happens.
First, you would need to create a table alias:
SELECT t1.*
FROM
Message-
From: Jim [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 11, 2006 10:39 AM
To: CF-Talk
Subject: Re: Select * in SQL
Heres a real world scenario I've come across:
Code throughout the app is using data returned from a query that has
select *, and pulls data from more than one table
Heres a real world scenario I've come across:
Code throughout the app is using data returned from a query that has
select *, and pulls data from more than one table.
Someone adds a column to one of the tables in the query that has the
same name as a column in another of the tables in the
Someone adds a column to one of the tables in the query that has the
same name as a column in another of the tables in the query.
If you're working in an environment in which any one can add columns or
modify a table
without othe programers being aware, then you will have have many more
Seems to me that it would save time to not have to look to another
development tool to get the information.
Depends which tool.
Mine will generate a complete CFQUERY tag with all table and column
names just by clicking on them.
But I had to develop it myself ;-)
--
PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tue Jul 11 17:14:21 2006
Subject: Re: Select * in SQL
Heres a real world scenario I've come across:
Code throughout the app is using data returned from a query that has
select *, and pulls data from more than one table.
Someone adds a column
Oh yes, when you go out to troubleshoot other companies apps you
certainly do come across many more problems.
Not a good example of where explicit alias + naming would have been handy?
OK, sorry bout that.
Claude Schneegans wrote:
Someone adds a column to one of the tables in the query that
Select * is for whimps, where they write a couple basic sql statements.
select with column names is for the cool kids in town where we'd rather
write it in SQL vs writing all those *Arrays, Structures, Multidimensional
Arrays Arrays of Structures***.
That's a slight crack on Kurtis D
our Mailing List / Treten Sie unserer Mailingliste bei:
deutsch: http://de.groups.yahoo.com/group/railo/
english: http://groups.yahoo.com/group/railo_talk/
Ryan, Terrence schrieb:
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
I've
Ryan, Terrence wrote:
I have a dumb question. Can someone point me to a definitive explanation
of why select * in SQL is bad?
#1 - if you provide column names, Coldfusion doesn't have to figure them
out.
#2 - If you don't need all the columns, don't retrieve them all. It can
dramatically
Message-
From: Gert Franz [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tue Jul 11 17:59:25 2006
Subject: Re: Select * in SQL
Hi Ryan,
there are many reasons why you shouldn't pull data with Select *
1. You have some overhead for the database to create the List of the
columns
1. You have some overhead for the database to create the List of the
columns to retrieve
IMHO this is a wrong argument: it is definitely easier for the driver to
take the list of columns as it
is defined in the datasource, than to read yours and compare with the
list in the datasource
to check
Selene Bainum did a SQL preso at CFUNITED and demo'd the asterisk VS. listing
each column. Then she showed the performance differences.
The asterisk was WAY slower than listing each column.
Will
~|
Introducing the Fusion
Heh - I was referencing that same preso. Even looked it up to make sure
I had the right example.
Will Tomlinson wrote:
Selene Bainum did a SQL preso at CFUNITED and demo'd the asterisk VS. listing
each column. Then she showed the performance differences.
The asterisk was WAY slower than
IMHO this is a wrong argument: it is definitely easier for
the driver to take the list of columns as it is defined in
the datasource, than to read yours and compare with the list
in the datasource to check if they are legal column names.
Well, if you take a look at what your database
Subject: Re: Select * in SQL
Heh - I was referencing that same preso. Even looked it up to make sure
I had the right example.
Will Tomlinson wrote:
Selene Bainum did a SQL preso at CFUNITED and demo'd the asterisk VS.
listing each column. Then she showed the performance differences.
The asterisk
41 matches
Mail list logo
