Re: Select * in SQL

>>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

Re: Select * in SQL

>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

Re: Select * in SQL

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, >

Re: Select * in SQL

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 I

Re: Select * in SQL

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=entry&entry=CDA25D0A-50DA-0559-A0104C817FCAD1E8 Will ~

Re: Select * in SQL

--Original Message- From: Leon Oosterwijk <[EMAIL PROTECTED]> To: CF-Talk 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

RE: Select * in SQL

o: CF-Talk 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 diff

RE: Select * in SQL

> 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 se

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 was WAY slower th

Re: Select * in SQL

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 Auth

Re: Select * in SQL

>>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 chec

Re: Select * in SQL

--Original Message- From: Gert Franz <[EMAIL PROTECTED]> To: CF-Talk 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

Re: Select * in SQL

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 dramaticall

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 to retrieve 2. You pull unnecessary data causing more CPU utilization and mainly lot of network traffic. Just imagine the table having a MEMO

Re: Select * in SQL

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. Leatham

Re: Select * in SQL

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 tha

Re: Select * in SQL

il <[EMAIL PROTECTED]> To: CF-Talk 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. >

Re: Select * in SQL

>>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 ;-) -- __

Re: Select * in SQL

>>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 probl

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 to one of the tables in the query that has the > same name as a column in another of the tables in th

RE: Select * in SQL

Yes, but wouldn't that would have screwed up things even if you would have been using explicit column names? -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&#x

Re: Select * in SQL

--Original Message- From: Dawson, Michael <[EMAIL PROTECTED]> To: CF-Talk 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.

RE: Select * in SQL

olumn names. I'm waiting 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 th

Re: Select * in SQL

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 a

RE: Select * in SQL

Thanks. Actually, there's a raft of reasons from this thread. All good ones too. Thanks cf-talk! -Original Message- 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 &l

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 to one of the tables in the query that has the same name as a column in another of the tables in the query.

Re: Select * in SQL

>>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

RE: Select * in SQL

Thanks to all who responded. You have given me a better set of answers now. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] -Original Message- From: Ryan, Terrence [mailto:[EMAIL PROTECTED] Sent: Tuesda

Re: Select * in SQL

>>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 de

Re: Select * in SQL

>>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. -- ___

Re: Select * in SQL

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 w

Re: Select * in SQL

>>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

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 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 tryin

RE: Select * in SQL

What everyone else said... Plus, when you use select * , you are more likely to get sql table structure caching issues if you change the structure of your table. That's where that lame error like : ColdFusion: Error Occurred While Processing Request N >= N When you type out your columns, that ne

RE: Select * in SQL

> 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 usin

Re: Select * in SQL

Typically if you ever have DB developers managing tables or if your data layer is truly separate from your other layers, then in theory, your tables could change (i.e., someone could add more columns that you *don't* need) at any time. If you were using select *, then you will retrieve unneces

RE: Select * in SQL

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? -Original Message- From: Ryan, Terrence [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 11, 2006 10:05 AM To: CF-

Re: Select * in SQL

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. Ryan, Terrence wrote: > I have a dumb quest

Re: Select * in SQL

Hi Ryan, Here are some reasons: 1) You're pulling back every row in the specific table that you're dealing with which DBA's frown upon because you can cripple a database if you're not careful. 2) You're pulling back an unnecessarily large dataset especially if you only need a couple of column

Re: Select * in SQL

like most best practices...it's subjective. for me, i like to look at the SQL and know specifically which columns I'm expecting. SELECT a.foo, a.bar, a.foobar a.somethingesle FROM myTable a goes further towards helping me debug my code than SELECT * FROM myTable it's a