Re: Select * in SQL
Any links for that preso? Just wondering what DB, hardware etc. What SQL was used etc.. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original 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 becomes evident that this is needed for performance reasons amounts to premature optimization. Most of the time, especially early on in development, using the * prevents annoying code errors where a data model change requires you to go back through a bunch of cfquery's and add a new column that is now needed. Leon -Original Message- From: Crow T. Robot [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 11, 2006 3:40 PM To: 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 differences. The asterisk was WAY slower than listing each column. Will ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246228 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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=entryentry=CDA25D0A-50DA-0559-A0104C817FCAD1E8 Will ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246243 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 INT, @Efficiency1 FLOAT, @Efficiency2 FLOAT SET @LoopCount = 1 SET @Total1 = 0 SET @Total2 = 0 SET @Total3 = 0 SET @LoopTotal = 3 WHILE @LoopCount = @LoopTotal BEGIN -- Select * SET @Start = GetDate() SELECT * FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd SET @End = GetDate() SET @Total1 = @Total1 + DateDiff(ms, @Start, @End) -- Select all columns: SET @Start = GetDate() SELECT FD.NDB_No, FD.FdGrp_Cd, FD.[Desc], FD.Shrt_Desc, FD.ComName, FD.ManufacName, FD.Survey, FD.Ref_Desc, FD.Refuse, FD.SciName, FD.N_Factor, FD.Pro_Factor, FD.Fat_Factor, FD.CHO_Factor, FG.FdGrp_Desc FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd SET @End = GetDate() SET @Total2 = @Total2 + DateDiff(ms, @Start, @End) -- Select needed columns SET @Start = GetDate() SELECT FD.NDB_No, FD.FdGrp_Cd, FD.[Desc], FD.Shrt_Desc, FG.FdGrp_Desc FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd SET @End = GetDate() SET @Total3 = @Total3 + DateDiff(ms, @Start, @End) SET @LoopCount = @LoopCount + 1 END SET @Efficiency1 = (CAST(@Total2/@LoopTotal AS FLOAT)) / (CAST(@Total1/@LoopTotal AS FLOAT)) SET @Efficiency2 = (CAST(@Total3/@LoopTotal AS FLOAT)) / (CAST(@Total1/@LoopTotal AS FLOAT)) PRINT 'Select Star: ' + CAST(@Total1/@LoopTotal AS VARCHAR) PRINT 'Select All Columns: ' + CAST(@Total2/@LoopTotal AS VARCHAR) PRINT 'Efficiency: ' + CAST(100 - (@Efficiency1 * 100) AS VARCHAR) + '%' PRINT '' PRINT 'Select Needed Columns: ' + CAST(@Total3/@LoopTotal AS VARCHAR) PRINT 'Efficiency: ' + CAST(100 - (@Efficiency2 * 100) AS VARCHAR) + '%' ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246244 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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, @Total2 BIGINT, @Total3 BIGINT, @LoopCount INT, @LoopTotal INT, @Efficiency1 FLOAT, @Efficiency2 FLOAT SET @LoopCount = 1 SET @Total1 = 0 SET @Total2 = 0 SET @Total3 = 0 SET @LoopTotal = 3 WHILE @LoopCount = @LoopTotal BEGIN -- Select * SET @Start = GetDate() SELECT * FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd SET @End = GetDate() SET @Total1 = @Total1 + DateDiff(ms, @Start, @End) -- Select all columns: SET @Start = GetDate() SELECT FD.NDB_No, FD.FdGrp_Cd, FD.[Desc], FD.Shrt_Desc, FD.ComName, FD.ManufacName, FD.Survey, FD.Ref_Desc, FD.Refuse, FD.SciName, FD.N_Factor, FD.Pro_Factor, FD.Fat_Factor, FD.CHO_Factor, FG.FdGrp_Desc FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd SET @End = GetDate() SET @Total2 = @Total2 + DateDiff(ms, @Start, @End) -- Select needed columns SET @Start = GetDate() SELECT FD.NDB_No, FD.FdGrp_Cd, FD.[Desc], FD.Shrt_Desc, FG.FdGrp_Desc FROM Food_Des FD INNER JOIN Fd_Group FG ON FD.FdGrp_Cd = FG.FdGrp_Cd SET @End = GetDate() SET @Total3 = @Total3 + DateDiff(ms, @Start, @End) SET @LoopCount = @LoopCount + 1 END SET @Efficiency1 = (CAST(@Total2/@LoopTotal AS FLOAT)) / (CAST(@Total1/@LoopTotal AS FLOAT)) SET @Efficiency2 = (CAST(@Total3/@LoopTotal AS FLOAT)) / (CAST(@Total1/@LoopTotal AS FLOAT)) PRINT 'Select Star: ' + CAST(@Total1/@LoopTotal AS VARCHAR) PRINT 'Select All Columns: ' + CAST(@Total2/@LoopTotal AS VARCHAR) PRINT 'Efficiency: ' + CAST(100 - (@Efficiency1 * 100) AS VARCHAR) + '%' PRINT '' PRINT 'Select Needed Columns: ' + CAST(@Total3/@LoopTotal AS VARCHAR) PRINT 'Efficiency: ' + CAST(100 - (@Efficiency2 * 100) AS VARCHAR) + '%' ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246246 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246247 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246271 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 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, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246095 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 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://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, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246096 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 columns which in terms requires more memory for processing by both the DBMS and CF. 3) You're not leveraging any of the indexing capabilities of the table (that is if its actually indexed) so you're performance will be somewhat degraded. I'm sure there's more and others will offer them up. Rey... 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 in the table. Mind you, that I'm not looking for convincing for myself, rather I'm doing a code review, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246098 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 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 for convincing for myself, rather I'm doing a code review, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246099 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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? !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL 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 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, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246100 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 unnecessary columns without knowing it. Additionally, I believe it's faster for the SQL engines to parse explicit column names instead of *. It's also more readable for someone else to explicitly know what is being retrieved in a query when debugging or examining code. 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 in the table. Mind you, that I'm not looking for convincing for myself, rather I'm doing a code review, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246101 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 using it because you're too lazy to explicitly list the columns? Are you actually using all of the columns? What exactly are the columns? None of these questions can be answered by someone reading your code, and that's bad. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246103 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 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 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, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246105 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 trying to maintain or debug your code down the road and you find yourself wondering which columns specifically are coming back from the query. I'd rather pay up front and save time down the road. -- Charlie Griefer ...All the world shall be your enemy, Prince with a Thousand Enemies, and whenever they catch you, they will kill you. But first they must catch you, digger, listener, runner, prince with a swift warning. Be cunning and full of tricks and your people shall never be destroyed. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246108 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246107 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 worked no longer does. 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? ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246110 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246111 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 decent driver will check the list of columns you supply any way, to make sure all columns exist. 2) with * the driver can get columns in their natural order in the database, and this may imply less manipulation while reading data. If you supply the list of all columns, the driver will probabily try to return the columns in the same order in the dataset. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246112 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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: 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 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, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246113 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 query, but you can always get this information from some othe development tool, and SELECT * ALWAYS saves you time when you write a query (provided you DO need all columns, of course) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246115 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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. CF now returns a query with 2 columns named the same and your data is funked up all over the site because using query.columnname is referencing the wrong column. Rey Bango wrote: 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 columns which in terms requires more memory for processing by both the DBMS and CF. 3) You're not leveraging any of the indexing capabilities of the table (that is if its actually indexed) so you're performance will be somewhat degraded. I'm sure there's more and others will offer them up. Rey... 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 in the table. Mind you, that I'm not looking for convincing for myself, rather I'm doing a code review, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246116 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Select * in SQL
Thanks. Actually, there's a raft of reasons from this thread. All good ones too. Thanks cf-talk! !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -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 [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 maintain or debug your code down the road and you find yourself wondering which columns specifically are coming back from the query. I'd rather pay up front and save time down the road. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246118 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 are wondering which columns specifically are coming back from the query, but you can always get this information from some othe development tool, and SELECT * ALWAYS saves you time when you write a query (provided you DO need all columns, of course) Seems to me that it would save time to not have to look to another development tool to get the information. Of course it always saves time when you write. But it could potentially cost your (or others) time in maintaining the code. I'd rather pay up front. -- Charlie Griefer ...All the world shall be your enemy, Prince with a Thousand Enemies, and whenever they catch you, they will kill you. But first they must catch you, digger, listener, runner, prince with a swift warning. Be cunning and full of tricks and your people shall never be destroyed. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246123 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 table1 t1 Then, if you add a column, as you mention, that query would not return the newly-added column. You would need to do: SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.fk I don't usually specify table aliases, but this is a good case of why you should. When I join tables, however, I do always use a table alias. It makes it much easier to see where the columns originate. I also think a SELECT * puts a somewhat-larget hit on the DB than specifying all column names. I think it is because the DB must look up all of the column names before it can validate the column names. If you specify the column names, then the DB can skip the column name lookup step before validating the column 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 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. CF now returns a query with 2 columns named the same and your data is funked up all over the site because using query.columnname is referencing the wrong column. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246124 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select * in SQL
Correct, with a join you have to alias or use FQN or you get ambiguous column errors. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- 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 table1 t1 Then, if you add a column, as you mention, that query would not return the newly-added column. You would need to do: SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.fk I don't usually specify table aliases, but this is a good case of why you should. When I join tables, however, I do always use a table alias. It makes it much easier to see where the columns originate. I also think a SELECT * puts a somewhat-larget hit on the DB than specifying all column names. I think it is because the DB must look up all of the column names before it can validate the column names. If you specify the column names, then the DB can skip the column name lookup step before validating the column 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 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. CF now returns a query with 2 columns named the same and your data is funked up all over the site because using query.columnname is referencing the wrong column. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246128 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Select * in SQL
Yes, but wouldn't that would have screwed up things even if you would have been using explicit column names? !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -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 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. CF now returns a query with 2 columns named the same and your data is funked up all over the site because using query.columnname is referencing the wrong column. Rey Bango wrote: 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 columns which in terms requires more memory for processing by both the DBMS and CF. 3) You're not leveraging any of the indexing capabilities of the table (that is if its actually indexed) so you're performance will be somewhat degraded. I'm sure there's more and others will offer them up. Rey... 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 in the table. Mind you, that I'm not looking for convincing for myself, rather I'm doing a code review, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246129 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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. CF now returns a query with 2 columns named the same and your data is funked up all over the site because using query.columnname is referencing the wrong column. Some people disagree with this, but I recommend using a naming convention that makes this impossible. In other words, one where the table name can be inferred from the column name. Nick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246130 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 problems ;-) And what if some one just change the name of a column? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246132 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246133 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select * in SQL
Uh oh, a DB schema design argument brewing... :-) This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Nick de Voil [EMAIL 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 to one of the tables in the query that has the same name as a column in another of the tables in the query. CF now returns a query with 2 columns named the same and your data is funked up all over the site because using query.columnname is referencing the wrong column. Some people disagree with this, but I recommend using a naming convention that makes this impossible. In other words, one where the table name can be inferred from the column name. Nick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246134 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 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 problems ;-) And what if some one just change the name of a column? ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246135 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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. Leathamhttp://www.cfunited.com/speakers.cfm#249. His session was awesome at cfunited, but those things can be eliminate to an extent with good sql. Writing basic SQL, who cares. use *, Writing hefty statements, with lots of joins and stuff, use table names. Casey On 7/11/06, Jim [EMAIL PROTECTED] wrote: 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 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 problems ;-) And what if some one just change the name of a column? ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246136 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 field in it 3. Since the query analyzers of the database server generates an execution best suited for performance using * will almost allways generate bookmark lookups which hurt performance 4. In the source code you do not know which columns you get in the resultset 5. The code in fact is much easier to read 6. Using INNER JOINS can cause ambiguous references if two columns are named the same but I must admit i do it the same way sometime just out of lazyness. But only if I know what results are expected. Greetings / Grüsse Gert Franz Customer Care [EMAIL PROTECTED] www.railo.ch Join 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 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, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246138 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 dramatically increase the speed of the query because less data has to be returned - especially if you're returning clobs and such that you don't necessarily need. #3 - you'll avoid crazy errors like 7 = 7. WTF does that mean? It means you're doing a select * and then you added/removed columns from the table and then did select * again. You don't want to go there! Avoid meaningless errors by specifying column names :) Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246145 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Select * in SQL
I think we answered the ambig problem - most decent servers would flunk at this anyhow. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original 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 to retrieve 2. You pull unnecessary data causing more CPU utilization and mainly lot of network traffic. Just imagine the table having a MEMO field in it 3. Since the query analyzers of the database server generates an execution best suited for performance using * will almost allways generate bookmark lookups which hurt performance 4. In the source code you do not know which columns you get in the resultset 5. The code in fact is much easier to read 6. Using INNER JOINS can cause ambiguous references if two columns are named the same but I must admit i do it the same way sometime just out of lazyness. But only if I know what results are expected. Greetings / Grüsse Gert Franz Customer Care [EMAIL PROTECTED] www.railo.ch Join 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 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, and want to be have a stronger footing for saying: Get rid of the select *'s. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246146 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 check if they are legal column names. 2. You pull unnecessary data causing more CPU utilization and mainly lot of network traffic. Right,... unless what you need is all columns, like it was supposed by the original poster. 4. In the source code you do not know which columns you get in the resultset If you don't know the columns you get, how come you can list them in the query? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246178 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246182 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 than listing each column. Will ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246183 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 server is doing, you don't need to rely on your opinion. If you don't know the columns you get, how come you can list them in the query? Being able to view the columns in the query may not help the original programmer, but it will help the maintainer of that code. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246187 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 becomes evident that this is needed for performance reasons amounts to premature optimization. Most of the time, especially early on in development, using the * prevents annoying code errors where a data model change requires you to go back through a bunch of cfquery's and add a new column that is now needed. Leon -Original Message- From: Crow T. Robot [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 11, 2006 3:40 PM To: 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 differences. The asterisk was WAY slower than listing each column. Will ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246190 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
