[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems) readily say find all results that say select * us bad. Here are a few areas where this has been discussed and/or debated: http://www.parseerror.com/sql/select*isevil.html http://www.sitepoint.com/forums/showthread.php?t=417457 http://www.adopenstatic.com/faq/selectstarisbad.asp http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,si d63_gci978334,00.html http://rip747.wordpress.com/2006/07/10/to-select-or-not-its-a-matter-of-opin ion/ That last one above is from a CF perspective, and does discuss the question from the perspective of what if I *do* want all the columns. I'll leave it to you and others to parse through all the available info to decide best for yourself. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Sent: Thursday, March 01, 2007 10:21 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: @#$!! queryparam Charlie, I didnt think that would be affecting it, I am more than aware that its bad practice, and as it happens the queries in question do have *, column1, column2 etc in them. I know that you are pulling more data than necessary with a * therefore
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom On 3/6/07, Duncan [EMAIL PROTECTED] wrote: Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems) readily say find all results that say select * us bad. Here are a few areas where this has been discussed and/or debated: http://www.parseerror.com/sql/select*isevil.html http://www.sitepoint.com/forums/showthread.php?t=417457 http://www.adopenstatic.com/faq/selectstarisbad.asp http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,si d63_gci978334,00.html http://rip747.wordpress.com/2006/07/10/to-select-or-not-its-a-matter-of-opin ion/ That last one above is from a CF perspective, and does discuss the question from the perspective of what if I *do* want all the columns. I'll leave it to you and
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Hi Tom, If you're doing SELECT * for a single record then don't worry about it - the overhead is minuscule. The real problems with SELECT * happen when you are running a query that will return multiple (hundreds/thousands of) records and the overhead can become enormous. And if you are doing this in a display that is only showing one field from each record and is only showing 10 or 20 or 30 records (with 'next' and 'prev' buttons) in a site that has many users hitting the same display - then you are likely to run into real problems. Cheers, Brett B) Tom MacKean wrote: I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom On 3/6/07, *Duncan* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Well, In my defence I use select * from still too, but I do not use cfqueryparam in my cfcs either so it is not an issue for me. But having said that, I am now using ORM's mainly Reactor, Sorry Mark haven't got the time to look at your Transfer framework just yet (still using reactor in MG:U). So this becomes a non issue for me again. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Tuesday, 6 March 2007 11:48 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom On 3/6/07, Duncan [EMAIL PROTECTED] wrote: Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin. These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was from Tom MacKean.) PPS With all these folks saying, I'm a SELECT * user, it's starting to sound like a rehab meeting. Hi, I'm Tom, and I'm a Select * user. :-) /Charlie http://www.carehart.org/blog/ _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Monday, March 05, 2007 7:48 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~--- attachment: attfc7c2.jpg
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
+1 for ORMs. Or at the very least, wrap your data access in a bean or something, so at least you only write the query once. Andrew Scott wrote: Well, In my defence I use select * from still too, but I do not use cfqueryparam in my cfcs either so it is not an issue for me. But having said that, I am now using ORMs mainly Reactor, Sorry Mark havent got the time to look at your Transfer framework just yet (still using reactor in MG:U). So this becomes a non issue for me again. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au http://www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 *From:* cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] *On Behalf Of *Tom MacKean *Sent:* Tuesday, 6 March 2007 11:48 AM *To:* cfaussie@googlegroups.com *Subject:* [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom On 3/6/07, *Duncan* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Charlie, No pushing of buttons going on, I was asking like a lemming because I was wondering if there were other reasons that I did not know about. I am aware of the larger amounts of data where unneccessary, but thats about it. Sometimes I find asking this way on a list questions like these illicit better responses to learn from. No offense intended. Your comments here are helpful, Thankyou Duncan On 3/3/07, Charlie Arehart [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Thanks Charlie, I'm alternating between Dreamweaver and Eclipse at the moment, depending on the project. Since my original post, I found a button in MySQL Manager ( http://www.mysqlmanager.com/) that copies all field names to clipboard. Reasonably easy. Cheers, Tom 12-step MacKean On 3/6/07, Charlie Arehart [EMAIL PROTECTED] wrote: Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin. These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was from Tom MacKean.) PPS With all these folks saying, I'm a SELECT * user, it's starting to sound like a rehab meeting. Hi, I'm Tom, and I'm a Select * user. :-) /Charlie http://www.carehart.org/blog/ -- *From:* cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] *On Behalf Of *Tom MacKean *Sent:* Monday, March 05, 2007 7:48 PM *To:* cfaussie@googlegroups.com *Subject:* [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~--- attachment: attfc7c2.jpg
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
Tom, If you are using CFEclipse can I suggest you look at a tool called SQL Explorer, that will allow you to create your query and copy and paste that into your code, also very good because it is a JDBC aware so it will connect to any know DB using JDBC drivers. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Tuesday, 6 March 2007 2:23 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) Thanks Charlie, I'm alternating between Dreamweaver and Eclipse at the moment, depending on the project. Since my original post, I found a button in MySQL Manager (http://www.mysqlmanager.com/ ) that copies all field names to clipboard. Reasonably easy. Cheers, Tom 12-step MacKean On 3/6/07, Charlie Arehart [EMAIL PROTECTED] wrote: Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin. These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was from Tom MacKean.) PPS With all these folks saying, I'm a SELECT * user, it's starting to sound like a rehab meeting. Hi, I'm Tom, and I'm a Select * user. :-) /Charlie http://www.carehart.org/blog/ _ From: cfaussie@googlegroups.com [mailto: mailto:cfaussie@googlegroups.com [EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Monday, March 05, 2007 7:48 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating a big long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~--- attachment: image001.jpg
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
If using Eclipse I've found quantumdb to also be good. http://quantum.sourceforge.net Although it does require you to make sure that the Eclipse GEF plugin is installed, and I've been having trouble with the latest version. com.quantum.feature_3.0.1.bin.dist.zip seems to work o.k. Cheers Gareth. Andrew Scott wrote: Tom, If you are using CFEclipse can I suggest you look at a tool called SQL Explorer, that will allow you to create your query and copy and paste that into your code, also very good because it is a JDBC aware so it will connect to any know DB using JDBC drivers. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone:+613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] On Behalf Of Tom MacKean Sent: Tuesday, 6 March 2007 2:23 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) Thanks Charlie, I'm alternating between Dreamweaver and Eclipse at the moment, depending on the project. Since my original post, I found a button in MySQL Manager (http://www.mysqlmanager.com/ ) that copies all field names to clipboard. Reasonably easy. Cheers, Tom "12-step" MacKean On 3/6/07, Charlie Arehart [EMAIL PROTECTED] wrote: Tom, what editor do you use? Nearly all of them (CF Studio, HomeSite+, Dreamweaver, and CFEclipse) offer the RDS feature, which would allow you to view all the tables and their columns for all datasources defined in your CF admin.These offer either a list of column names you can drag and drop (tedious for many) or a visual query builder where you can check the columns desired and then copy/paste from the SQL they build. If you've never used them, you're not alone. Many go years never seeing them. Let us know which you use if you need more help. To those who would jump in saying that you can't use RDS if it's not enabled, such as is likely on a production server, I'll reply that one shouldn't be doing development work against a production server. :-) Do your development locally, or at least just setup the free CF Dev edition locally and define your datasources there, and point to them in your editor to help build SQL. Or skip RDS and just use whatever SQL building tool that might come with the DBMS you're using. Many of them have visual query building tools as well, where again you can build a SQL statement and then copy/paste it into your CFML. Hope that's helpful, Tom. PS Did you know that there's a Tom McKeon in the CF world? He's in upstate New York here in the States. Small world. (For those missing the point, this note was fromTom "MacKean".) PPS With all these folks saying, "I'm a SELECT * user", it's starting to sound like a rehab meeting. "Hi, I'm Tom, and I'm a Select * user." :-) /Charlie http://www.carehart.org/blog/ From: cfaussie@googlegroups.com [mailto: cfaussie@googlegroups.com] On Behalf Of Tom MacKean Sent: Monday, March 05, 2007 7:48 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam) I am a SELECT * user. (In my defence, I'm self-taught and didn't know any better until now) My question... is there a trick or tip or tool that you guys use to save typing in the name of every field that you're after? If you're populating abig long form, it's a real pain to type every field name into your SELECT statement (when you could just use a *). Is there a quick way, or do you guys just bite the bullet and start typing? Cheers, Tom --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Why select * is bad (was RE: [cfaussie] Re: @#$!! Queryparam)
I have seen 2 notable reasons to stay away from select * if possible: 1. I have come across several issues with the schema changing in a database and the changes where not reflected in a query that had select * (legacy code). This can make the display layer break in strange ways when the template is expecting a row to be there and it isn't. From experience, this only happens with cfqueryparam with it's caching. 2. Supporting legacy code. If you come across a select *, you don't know what columns you are retrieving unless you look into the structure of the table (or tables) in question. This can be annoying if you are working on a large system with a lot of tables, or using a subset of a large number of tables in an Oracle schema (as is sometimes the case due to expensive licenses). The main reason I have found it to not be best practise is the extra steps you need to take to maintain legacy code that uses select *. I don't know if anyone else would agree, but I would prefer to already know what is going to be in a result set just by looking at the query that was used instead of digging around the db or dumping the result. My 2c, Shane On 3/3/07, Charlie Arehart [EMAIL PROTECTED] wrote: So, are you saying that removing the select * does or does not solve the problem? I just want to know where this thread is crossing from solving your problem to discussing the broader issue of select *. I'm actually surprised to hear that you would ask for a spelling out of the reasons for why using select * is bad. It's such a universally derided practice. And there's been all that discussion afterward today later on subjects like hosting, findnocase. I have to think folks didn't read this, as I'd expect to see a number of people come out and explain why it's bad. That's the beauty of a list like this: no one person needs to shoulder the burden of answering a question. But I'll kick it off with saying that people usually use it when it's not needed, as a shortcut. The problem is simply that if the number of columns retrieved exceeds the number used, then you've asked for a lot of needless work to be done: the database had to gather the data, then store it in its buffers, then it had to be sent across the network, then it had to be stored in CF's memory as a query resultset. The larger the number of excess column (and the size of data they hold), the more the pain of the problem, and when you multiply that by the number of rows retrieved, and then by the number of requests running that query each day...well, as the joke goes, a million here, a million there, and soon you're talking about real money. And the problem is about more than just you who issued the query. You're request asking the database to do work takes away resources that it could have spent doing more useful work. And when data fills the DB buffers, that flushes data from someone else's query that might have been reused for a subsequent request for the same database pages (very low level, but important, stuff). Now that was just if the number of columns retrieved would be smaller if you did just name them instead. If they're the same, then that's certainly different, though there can still be issues. For instance, depending on the database (and perhaps configuration), the use of select * may cause the DBMS to process its query plan differently. That really depends, though, and so I don't want to state categorically that it's always bad for that reason. I'll leave that to others to hash out (see below). Then there's this issue of its impact when used with CFQUERYPARAM, and some have even argued that it causes problems when used in CFQUERY with views (see the comments in the last thread below), and so on. Now, really, there are all manner of other places where people have decried it (or debated it, as you want to). I can't tell if you're pressing me, Duncan, just to get a rise out of me, or try to make me prove my statement, or just out of sincere curiosity. I just want to clarify that I only proposed you avoid it to solve your very problem with CFQUERYPARAM. Please do let us know if it helped. But if you're still interested in the select * debate, there's plenty out there. I don't need to defend it myself. :-) What's interesting is that if you try to do a google search, you're flummoxed because google uses * as a single word placeholder (no, not a multi-word, just a single word, which is curious), so you can't (it seems) readily say find all results that say select * us bad. Here are a few areas where this has been discussed and/or debated: http://www.parseerror.com/sql/select*isevil.html http://www.sitepoint.com/forums/showthread.php?t=417457 http://www.adopenstatic.com/faq/selectstarisbad.asp http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,si d63_gci978334,00.html
