hi jim i have also had to do alot of work with queries after they come out of the database. i have been through almost every conceivable angle there is to try to optimize it, and have found that at present coldfusion query of queries just isnt built as a database engine and unfortunately is only useful for simple queries and not for what many people are trying to use it for.
i had quite a few query of queries progressivley running various queries on some data and found that was taking 110 seconds to complete, and I tried everything to speed it up. however instead i thought 'what if i changed into an array of arrays and replicated my query but with manual cf code'. i created a function that took a query and changed it into an array of arrays (make use of the querycolumntoarray on cflibs if you are also going to do this), then run manual code manipulating it to do the same as the query would, and then created another function that takes the array of arrays and turn it back into a query. like i said it took 110 seconds in query of query but took only 3 seconds with array of arrays. you and others may also see other alternatives but for me this worked like a dream. i know its more work but it is worth it to see the speed increases - it may work for yours as well but obviously depending on what you are doing but it may be worth a go. good luck, i would also be interested in any result you find, or any alternatives you find. but my advice will be to start thinking of alternatives to query of query until a new release is brought out that can act more like a database engine richard >I have a process that needs to do about 500 record lookups by ID. I >thought I'd do a single query of the table, pulling down the needed data >for every record on file (say 1000 records, just three columns) and then >use QoQ instead of hitting the database so many times. > >Is there any way to index or otherwise optimize the dataset that I >retrieve to optimize the query of query lookups? If I'm doing lookups >using a particular column, would it help to sort the query by that column? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312674 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
