Re: Optimize Query of Query
Actually I have dealt with this and did some testing. What I ended up doing was creating an array with the look up vaules like so And then instead of doing a QoQ I just feed it the ID I was trying to tease out like so #ResultsArray[MyID]# The QoQ was expensive in terms of execution time. The execution time for the report went from 30 seconds to around a second. I don't know if this is best practices but it did the trick. On Tue, Sep 16, 2008 at 5:54 PM, Jim McAtee <[EMAIL PROTECTED]>wrote: > 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:312682 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Optimize Query of Query
> ColdFusion is *NOT* a database engine and is not being designed to replace > one. thats a shame that they couldnt somehow make it utilise a db engine more instead of replacing one. > array of arrays - And this is exactly what they suggested for heavy data > lifting inside of ColdFusion. shame i couldnt have been in that meeting, would have saved me days of work!!! but good to know what i am doing in the best option ~| 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:312681 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Optimize Query of Query
Richard White wrote: > ... found that at present coldfusion query of queries just isnt built as a > database engine And probably never will be. That is what ColdFusion engineers told a group of us at a pre-Max conference a couple of years ago. ColdFusion is *NOT* a database engine and is not being designed to replace one. Q-of-Q is only designed for simple tasks. > ... 'what if i changed into an array of arrays and replicated my query but > with manual cf code'. And this is exactly what they suggested for heavy data lifting inside of ColdFusion. As apposed to a database engine designed around SQL and query record sets, ColdFusion is optimized to use arrays and structures. ~| 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:312678 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Optimize Query of Query
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
Optimize Query of Query
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:312635 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
