Typically, processing in DB will be faster - even if you need to write a stored proc to do processing. You can write inefficient stuff in a DB pretty easily as well, so it's not ALWAYS a given.
I'm more familiar with Oracle & SQL Server so I'm not sure which method will be better using MySQL (outer joins with subqueries or unions of subqueries). One thing is for sure though, indexes or hints may be necessary to produce the fastest results. Sometimes creating views (for instance) will be faster than doing sub-queries as you've outlined below. Sometimes you just have to play with several methods to get the data the way you want and see which is more efficient. On Mon, Apr 9, 2012 at 12:53 PM, Tepfer, Seth <[email protected]> wrote: > Nathan reminded me I didn’t give full info:**** > > **· **Small data set <1K records**** > > **· **Using MySQL 5.0**** > > ** ** > > Sure, I have a working answer with query1 (students with Temp or Perm), > query2 (all students except those returned in Q1), Union Q1 + Q2.**** > > ** ** > > But There has to be a more efficient method. I know with our servers and > the recordset, the need for efficiency is minimal, but I don’t want to be > lazy or miss a chance to learn new techniques. **** > > ** ** > > Isn’t MySQL faster than CF at doing the select? If I can do it all in one > call, isn’t it better to make MySQL do the heavy lifting?**** > > ** ** > > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Charlie > Arehart > *Sent:* Monday, April 09, 2012 11:01 AM > *To:* [email protected] > *Subject:* RE: [ACFUG Discuss] Tricky SQL help?**** > > ** ** > > Just a guess, with the limited info, but it seems to me that you’d just > want then for the query to get “all the students”, and make the decision as > to what address to “show” in CFML while looping over the results. Or do you > have some reason to want to do this all in a query?**** > > ** ** > > /charlie**** > > ** ** > > *From:* [email protected] [mailto:[email protected] <[email protected]>] *On > Behalf Of *Tepfer, Seth > *Sent:* Monday, April 09, 2012 10:45 AM > *To:* [email protected] > *Subject:* [ACFUG Discuss] Tricky SQL help?**** > > ** ** > > I know this is not strictly a CF issue, but I’m trying to find the most > efficient way of doing this, and a single query seems faster than 2 queries > plus a query of queries.**** > > **** > > **** > > I have two tables, one of student names, one of addresses. ** ** > > **** > > *Students*: id, name, emplID**** > > *Addresses*: emplID, addressType, addressline1, city, state, **** > > **** > > *Student table data:***** > > 1, john dooley, 1234567**** > > 2, jane dooley, 2345678**** > > 3, tom dooley, 33456780**** > > **** > > *Addresses table data:***** > > 1234567, onCampus, 101 Dowman, null, null**** > > 1234567, home address, 100 main street, springfield, IL**** > > 1234567, permanent forward, 120 main street, Atlanta, GA**** > > 2345678, home address, 200 main street, springfield, MO**** > > 2345678, temporary forward, 130 main street, Atlanta, GA**** > > 3456789, home address, 300 main street, springfield, GA**** > > **** > > Every student has a home address. Some have permanent or temporary > forwards. The query results I want to return are **** > > · All the student names**** > > · If they have permanent or temporary forward, that address**** > > · If they do not, a null for the address (NOT the home address or > onCampus address)**** > > **** > > I’ve tried an outer join with a ‘where addressType = ‘permanent or > temporary’, but that doesn’t give me all the names, just the ones with a > forward. **** > > Thanks for any time you can give.**** > > **** > > ** ** > ------------------------------ > > > This e-mail message (including any attachments) is for the sole use of > the intended recipient(s) and may contain confidential and privileged > information. If the reader of this message is not the intended > recipient, you are hereby notified that any dissemination, distribution > or copying of this message (including any attachments) is strictly > prohibited. > > If you have received this message in error, please contact > the sender by reply e-mail message and destroy all copies of the > original message (including attachments).**** > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- **** > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- **** > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > -- Dawn
