Re: struct lookup vs. query of queries
My experience with this is that QoQ are are (really) slow when compared with using structs or arrays. I had a one experience where it brought execution time down from 15-20 seconds using QoQ to 1 or 2 seconds for an array. Generally I create an array of the values I want to look up. EmpArray[1][ID] = tblEmp.Fname EmpArray[2][ID] = tblEmp.Email etc... Even if you use a sub query or a Join to pull these values you are going to hitting the tblEmp table for every record. This way you only hit tblEmp once.. But then again with proper indexes the database route may be faster... so 6 of one or 1/2 dozen of another. G! On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz mdino...@houseoffusion.com wrote: I've got a loop which will need to look up a piece of data on each iteration. The data is standardized so there is really just one call to the database (outside the loop). I can either do a query of queries on each iteration or I can turn the query into a structure and do a structure lookup (structkeyexists, etc) on each iteration. I'm assuming that the struct lookup will be faster/more efficient even after having to turn the query into a structure. Anyone have an opinion on this? Thanks -- Michael Dinowitz Lead Author - Adobe Coldfusion Anthology http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335359 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz mdino...@houseoffusion.com wrote: I've got a loop which will need to look up a piece of data on each iteration. The data is standardized so there is really just one call to the database (outside the loop). I can either do a query of queries on each iteration or I can turn the query into a structure and do a structure lookup (structkeyexists, etc) on each iteration. I'm assuming that the struct lookup will be faster/more efficient even after having to turn the query into a structure. Anyone have an opinion on this? I virtually always choose to use a struct in these cases. There are very few cases where I have seen an argument to use QoQ for virtually anything. QoQ is typically much slower than structs, assuming you are using alot of values from the query. Sometimes it can even be faster to go back to the DB each time rather than QoQ. Seriously. Depending on the use case, I like to convert the Query to a struct and then cache that struct either in a shared scope, or using CF9's EHCache caching mechanisms. You can wrap that whole thing up in a nice little CFC and it become really quite reusable. -Cameron -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: camer...@gmail. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335362 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: struct lookup vs. query of queries
Correct me if I'm wrong (please) but isn't a query already a Struct of Arrays?
I mean the following is totally valid:
Table user_list (user_fname, user_lname) {shown in comma delimited format}
Cameron, Childress
Michael, Dinowitz
Steve, Durette
cfquery name=myUserList (blah blah)
Select user_id, user_name
From user_list;
/cfquery
cfoutput
#Variables.myUserList[user_fname][2] #
/cfoutput
Would output: Michael
So after the query is loaded, you can just use the data. No copying no extra
query, just direct access.
Steve
-Original Message-
From: Cameron Childress [mailto:camer...@gmail.com]
Sent: Wednesday, July 14, 2010 10:27 AM
To: cf-talk
Subject: Re: struct lookup vs. query of queries
On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz
mdino...@houseoffusion.com wrote:
I've got a loop which will need to look up a piece of data on each
iteration. The data is standardized so there is really just one call
to the database (outside the loop). I can either do a query of queries
on each iteration or I can turn the query into a structure and do a
structure lookup (structkeyexists, etc) on each iteration. I'm
assuming that the struct lookup will be faster/more efficient even
after having to turn the query into a structure.
Anyone have an opinion on this?
I virtually always choose to use a struct in these cases. There are
very few cases where I have seen an argument to use QoQ for virtually
anything. QoQ is typically much slower than structs, assuming you are
using alot of values from the query. Sometimes it can even be faster
to go back to the DB each time rather than QoQ. Seriously.
Depending on the use case, I like to convert the Query to a struct and
then cache that struct either in a shared scope, or using CF9's
EHCache caching mechanisms. You can wrap that whole thing up in a
nice little CFC and it become really quite reusable.
-Cameron
--
Cameron Childress
Sumo Consulting Inc
http://www.sumoc.com
---
cell: 678.637.5072
aim: cameroncf
email: camer...@gmail.
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335366
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
Michael,
Any chance you can give us a real example and some more detail so we may try
to give you the best solution?
On Wed, Jul 14, 2010 at 10:54 AM, DURETTE, STEVEN J (ATTASIAIT)
sd1...@att.com wrote:
Correct me if I'm wrong (please) but isn't a query already a Struct of
Arrays?
I mean the following is totally valid:
Table user_list (user_fname, user_lname) {shown in comma delimited format}
Cameron, Childress
Michael, Dinowitz
Steve, Durette
cfquery name=myUserList (blah blah)
Select user_id, user_name
From user_list;
/cfquery
cfoutput
#Variables.myUserList[user_fname][2] #
/cfoutput
Would output: Michael
So after the query is loaded, you can just use the data. No copying no
extra query, just direct access.
Steve
-Original Message-
From: Cameron Childress [mailto:camer...@gmail.com]
Sent: Wednesday, July 14, 2010 10:27 AM
To: cf-talk
Subject: Re: struct lookup vs. query of queries
On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz
mdino...@houseoffusion.com wrote:
I've got a loop which will need to look up a piece of data on each
iteration. The data is standardized so there is really just one call
to the database (outside the loop). I can either do a query of queries
on each iteration or I can turn the query into a structure and do a
structure lookup (structkeyexists, etc) on each iteration. I'm
assuming that the struct lookup will be faster/more efficient even
after having to turn the query into a structure.
Anyone have an opinion on this?
I virtually always choose to use a struct in these cases. There are
very few cases where I have seen an argument to use QoQ for virtually
anything. QoQ is typically much slower than structs, assuming you are
using alot of values from the query. Sometimes it can even be faster
to go back to the DB each time rather than QoQ. Seriously.
Depending on the use case, I like to convert the Query to a struct and
then cache that struct either in a shared scope, or using CF9's
EHCache caching mechanisms. You can wrap that whole thing up in a
nice little CFC and it become really quite reusable.
-Cameron
--
Cameron Childress
Sumo Consulting Inc
http://www.sumoc.com
---
cell: 678.637.5072
aim: cameroncf
email: camer...@gmail.
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335368
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
On Wed, Jul 14, 2010 at 10:54 AM, DURETTE, STEVEN J (ATTASIAIT) sd1...@att.com wrote: Correct me if I'm wrong (please) but isn't a query already a Struct of Arrays? It is, but you can really only use that to select data based on a column name and row number. Typically, if I were to convert a query to a struct and then use that struct, the key of the struct might be something like the PK of the data, such as userID. This means you could look up a value in the struct like this: #myStruct[variables.userID].firstName# If you leave your resultset as a query, you cannot look up an individual row without knowing it's row number. Which makes it's a struct of arrays pretty useless if all you know is the user's ID. -Cameron -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: camer...@gmail. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335372 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
struct lookup vs. query of queries
I've got a loop which will need to look up a piece of data on each iteration. The data is standardized so there is really just one call to the database (outside the loop). I can either do a query of queries on each iteration or I can turn the query into a structure and do a structure lookup (structkeyexists, etc) on each iteration. I'm assuming that the struct lookup will be faster/more efficient even after having to turn the query into a structure. Anyone have an opinion on this? Thanks -- Michael Dinowitz Lead Author - Adobe Coldfusion Anthology http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335294 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
I think the size of the query and structure would be an important factor, as well as how many loop iterations are done. Often I lean towards using structures if I need to look up data frequently because the syntax is cleaner. There is also the array method of accessing queries like query.column[row], which might help with something. Whatever you are trying to do might have other solutions as well, such as keeping track of items as they are encountered using separate variables. QoQ doesn't have the reputation of being fast, although neither does turning a query into another data type. Turning a query into another data type also increases the RAM usage since data is stored twice. Either way, my guess would be that the difference in speed would be unnoticeable to the end user, so it might come down to personal preference and ease of maintenance. -Mike Chabot On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz mdino...@houseoffusion.com wrote: I've got a loop which will need to look up a piece of data on each iteration. The data is standardized so there is really just one call to the database (outside the loop). I can either do a query of queries on each iteration or I can turn the query into a structure and do a structure lookup (structkeyexists, etc) on each iteration. I'm assuming that the struct lookup will be faster/more efficient even after having to turn the query into a structure. Anyone have an opinion on this? Thanks -- Michael Dinowitz Lead Author - Adobe Coldfusion Anthology http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335299 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
Can you accomplish the same thing in the db? I would feel pretty confident saying that unless you've got an unusual table it's going to be faster to use the db for you data comparison. On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz mdino...@houseoffusion.com wrote: I've got a loop which will need to look up a piece of data on each iteration. The data is standardized so there is really just one call to the database (outside the loop). I can either do a query of queries on each iteration or I can turn the query into a structure and do a structure lookup (structkeyexists, etc) on each iteration. I'm assuming that the struct lookup will be faster/more efficient even after having to turn the query into a structure. Anyone have an opinion on this? Thanks -- Michael Dinowitz Lead Author - Adobe Coldfusion Anthology http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335333 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
Depending on the size of the lookup table, I like using a struct to hold a reference to the row number, that way I quickly lookup the data in the query. Lookup = structNew(); Lookup[id] = row; then you can do: query.column[Lookup[id]]; -Dan On Tuesday, July 13, 2010, Michael Dinowitz mdino...@houseoffusion.com wrote: I've got a loop which will need to look up a piece of data on each iteration. The data is standardized so there is really just one call to the database (outside the loop). I can either do a query of queries on each iteration or I can turn the query into a structure and do a structure lookup (structkeyexists, etc) on each iteration. I'm assuming that the struct lookup will be faster/more efficient even after having to turn the query into a structure. Anyone have an opinion on this? Thanks -- Michael Dinowitz Lead Author - Adobe Coldfusion Anthology http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335337 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
