Re: [ACFUG Discuss] Have a question about group limits

[Frank Moorman]

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 On 07/24/2012 04:33 PM, Charlie Arehart wrote: Michael, I suppose if that works for you, great. :-) But I see a few things I would question, which might improve the look of the code (not the result: again, I realize that what you have may provide the same result which you want). First, I’d just note that you really ought to define the function either at the top or bottom of the page, rather than inside the loop. :-) And that could be about more than just style: I’d think CF is really executing that code and recreating the function each iteration. Since there’s no “state” in it, it doesn’t cause a problem, but it just is better to move it down out of the loop. Second, and more important of the discussion of CFOUTPUT GROUP, it’s not really needed here, at least how you’ve coded things.  It’s really not doing any GROUPing at all. The reason things “work” (despite that) is that you are doing the select distinct in the first query, which gives you the list of distinct dest values. Since you’re doing that, you don’t even really get any benefit from the GROUP in your cfoutput loop on that. There is never more than one value of dest. Instead, the purpose is when the result has lots of records, many of which have the same value you want to “break” on (your dest), and you have CF do the dirty work. The key is that you not only get all the records in one query, but then you do an ORDER BY in that query to get them in order by that value, and thean have CF “break” on that value. So for your code, I’d think this would do it: <cfquery name="top10" datasource="xxxxx"  >         SELECT dest, property, url_e, price, description           FROM tableb          WHERE top10 = "1" AND description > ' '          ORDER BY dest </cfquery> <cfoutput query="top10" group="dest">   <b>#top10.dest#</b><br>   <table>   <cfoutput>      <tr><td >#CapFirst(str)#</td><td>#UCASE(top10.Description)#</td> <td>$#top10.price#.00</td><td><a href="" target="_blank">More</a></td></tr>   </cfoutput>   </table>  </cfoutput> </cfloop> (then include the function definition after this). Note that I removed the first query entirely, and in the second I removed the WHERE and added an ORDER BY. Now, you brought up the3 desire to have only 5 records at most in the resulting list (per dest value), and that’s where I proposed adding the code (in my earlier email). So you’d add 3 lines to the above, within the table, as in:   <table>   <cfset ct=0>   <cfoutput>      <cfif ct++ lt 3>      <tr><td >#CapFirst(str)#</td><td>#UCASE(top10.Description)#</td> <td>$#top10.price#.00</td><td><a href="" target="_blank">More</a></td></tr>     </cfif>   </cfoutput>   </table>  Let me know if that does it for you, and if it then makes better sense. Finally, why was all that within a <head> tag in your code below? :-) Hope that helps.   /charlie   From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Michael Brown Sent: Tuesday, July 24, 2012 11:38 AM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Have a question about group limits   Charlie, Tried the code on cflib.org.  It did the case that I desired, but it also caused the content in the displayed field (property) to duplicate, showing same value for all records.  See code and link below. link: http://hotdeals.com/datatesting2.cfm <head> <cfquery name="groups" datasource="xxxx"  > SELECT DISTINCT dest   FROM tableb  WHERE top10 = "1" AND description = ' '; </cfquery> <cfloop query="groups">     <cfquery name="top10" datasource="xxxxx"  >         SELECT dest, property, url_e, price, description           FROM tableb          WHERE top10 = "1" AND description > ' '            AND dest = <cfqueryparam maxlength="20" value="#groups.dest#">          LIMIT 5     </cfquery> <!--- Either print here, or combine in one large query, using QueryNew() outside the loop and QueryAddRow() for each result ---> <cffunction name="CapFirst" returntype="string" output="false">     <cfargument name="str" type="string" required="true" />         <cfset var newstr = "" />     <cfset var word = "" />     <cfset var separator = "" />         <cfloop index="word" list="#arguments.str#" delimiters=" ">         <cfset newstr = newstr & separator & UCase(left(word,1)) />         <cfif len(word) gt 1>             <cfset newstr = newstr & right(word,len(word)-1) />         </cfif>         <cfset separator = " " />     </cfloop>     <cfreturn newstr /> </cffunction> <CFSET str="#top10.property#"> <cfoutput query="top10" group="dest">   <b>#top10.dest#</b><br> <table>   <cfoutput><tr> <td >#CapFirst(str)#</td><td>#UCASE(top10.Description)#</td> <td>$#top10.price#.00</td><td><a href="" target="_blank">More</a></td>   </tr></cfoutput> </table>  </cfoutput> </cfloop> </head> ------------------------------------------------------------- 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 ------------------------------------------------------------- ------------------------------------------------------------- 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 -------------------------------------------------------------