So Frank, sure, I get that you wrote something off the cuff (so did I, though yes I did test it before sending it). But I sensed you were not familiar with the CFOUTPUT GROUP, so I just wanted to show how that could work. No offense intended, and I think none was taken, but want to make sure. :-)
As for mixing bus and display logic, well that of course is an entirely different subject. I was proposing this for those who don't have that concern. (Recall, it was Michael who said he was wondering how to limit the inner loop in a grouping operation, so I assumed he was referring to this sort of CFOUTPUT GROUP operation.) But then you say, "As for performance, I would agree with Dawn's assessment that this is not a great solution if you have many different "dests". I could see this being horribly slow with more than 10 dests or a very large recordset." Well, can you clarify: she was writing in response to your code, not mine. So when you say "this solution", do you mean yours, or mine? It reads like the latter, but I'm sensing maybe you meant you were simply agreeing with her assessment of your own example. But I want to clarify for readers, and myself (because I would not think my code would have any performance hit for "many dests".) As for your 5th paragraph about the debate between doing things in cfml or in the db, you say, "If all I wanted was combined results, I would not use the group attribute." I'm not sure I agree with the conclusion, but to each his own. Still, I do agree if you were communicating that sometimes someone will want to do paging through the results, which may on the surface not seem well suited to this approach, but then if you use query caching of the query to be processed, it can actually work just fine with paging through the results. Finally, you say, "On a slightly different topic, don't always assume that a stored procedure is always the fastest." Here, again, I will assume you are talking to Dawn (in her response to you, though you are replying here to me.) That said, I do agree that SPs are not always the right answer, and as you say testing is always best. And keeping an open mind to alternatives. That's what we're all here to discuss, so I never mind these sort of back and forths as we all learn together and from each other. Thanks for the story there, and the kind regards. /charlie From: [email protected] [mailto:[email protected]] On Behalf Of Frank Moorman Sent: Tuesday, July 24, 2012 7:27 PM To: [email protected] Subject: Re: [ACFUG Discuss] Have a question about group limits Charlie, No, I never used the group clause in <cfoutput> I vaguely remember hearing you talk about it once before, but I never had the need for it since. As for code style, yes this was a quick and dirty solution. Yes, I would have this in a function. If anything I would have the looping extract the necessary information and dump it into appropriate variables, whether that is a single query with all the rows or a simple struct depends on how I was using this information. (I would never just write out the results here, mixing business logic and display logic is rarely good style.) And to justify my lack of an elegant solution, I would just say it is because I was writing code on the fly in email, its not like I was cutting/pasting existing code that I used an IDE and intended to maintain. As for performance, I would agree with Dawn's assessment that this is not a great solution if you have many different "dests". I could see this being horribly slow with more than 10 dests or a very large recordset. Generally, I believe that it is usually best to create a single SQL and have the database do all the work for you returning only relevant results. (Without the need for later filtering and/or concatenation.) In this case, I believe that Oracle or SQL Server's windowing functions can be used to create a single query with the proper resultset. (These are the functions that allow you to use aggregate controls limited within a resultset.) Unfortunately, as much as I like MySQL, I believe that the windowing functions are one of its deficiencies. For MySQL, I would probably write a stored procedure to achieve the same results. As for pulling back all the rows and using the group attribute that Charlie mentioned... If all I wanted was combined results, I would not use the group attribute. I would try to use a single query with all the extra functions as I mentioned above. However, if I was actually utilizing all of the individual details, in which I needed every row, followed by a summary, in that case I would consider using the group function. This goes back to having the database do all the work for you -- if you already have all the information you need, there is no reason to ask the database for more. On a slightly different topic, don't always assume that a stored procedure is always the fastest. Generally that is the way most of us are taught, but it isn't always correct. On one of my applications we look for inspectors across the country based on location. The inspectors work in multiple locations so there is a child table for zip code lookups. We take the zip code of the work and find out who is closest with a secondary sort on a preferred indicator and a tertiary sort on the inspectors' cost. The system only needs to return the top 25 inspectors and of course there is another table to convert zip codes to longitude/latitude coordinates. The results are also filter on things in the parent table including the type of inspection and active/inactive status among other things. I created a stored procedure to take in the location, number of inspectors, and inspection type to return results. The output resultset was inspectors name, cost, distance, full address, and phone number. It worked, and on a good day it would take about 20-25 seconds to execute while running with roughly 4000 inspectors that had an average of roughly 10 zipcodes each. On a bad day it would take triple that time or more. (A nod here to Charlie who has mentioned in a past meeting that coldfusion doesn't like breaking the SQL connection when timing out in the middle of the call.) I rewrote the SQL to return the entire result set without using a stored procedure and now it normally takes about a half a second. I thought for sure that using a stored proc here would win the performance debate hands down, especially since it returns only the 25 relevant rows, not 4000. And when you figure the higher cost of the geometric calculations on all the results I was shocked. To this day I am not positive why, but my best guess is blaming the SQL Connector as a poor driver. (The SP was used by CF7 on windows, I forget which version of MySQL Connector/J) Also, the database and webserver are on the same machine so the 4000 record resultset does not have a network delay. As I said I am still puzzled by this, I have not tested the SP since moving to CF9. But don't always assume you know how something will perform until you try it. (Even though I still make a lot of performance assumptions.) --Frank ------------------------------------------------------------- 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 http://www.fusionlink.com -------------------------------------------------------------
