Hi Kevin, I'm trying to decide if it would be best to loop through the query to do the comparison, or just do a bunch of queries.
To refresh, I am doing a query to find all hits on a site that are less than an hour old. Once I have that data set, I want to do additional queries on that data to find hits that are from smaller segments, such as "only 1 minute old". Let's say that once I do the main query and have a record set of hits on a site that are less than an hour old, I then want to do 60 smaller queries, each query grabbing the number of hits for each minute in the hour. I've already determined that "query within a query won't work here in how qnq's handle datetimes. SO I can either do 60 individual queries OR loop through the main query 60 times to harvest the data I need. 60 individual queries would be easy on OpenBD and would present more load on the database server, but not much since the original query would have been cached so the 60 subsequenty queries would run pretty quickly. Looping through the main query would alleviate the load on the database, since only the original query was needed, but would require more resources from OpenBD since it would do all the calculations in the loop. I've decided I'd rather err on the side of OpenBD doing the work since it is easier, and cheaper, to add OpenBD servers than it is database servers. Scenario 1 Query 1 - select all records from DB where (site_id matches provided ID) and (time stamps are less than an hour old) Query 2 - select all records from DB where (site_id matches provided ID) and (time stamps are less than 1 minutes old.) Query 3 - select all records from DB where (site_id matches provided ID) and (time stamps are less than 2 minutes old and but older than 1.) etc... Or Scenario 2 Query 1 - select all records from DB where (site_id matches provided ID) and (time stamps are less than an hour old) For each metric needed, Loop through query 1 and do regular expresson <CFLOOP index="X" from="1" to="60"> <CFLOOP query="mainquery"> <CFSET hitcount[#index#] = 0> <CFIF query.timestamp less than 10 minutes old and but older than 5> <CFSET hitcount[index] = hitcount[index]+1> </CFIF> <CFLOOP> </CFLOOP> This would leave me with an array called 'hitcount' and each element in the array would be the hit count for the minute segment it represented. hitcount[5] would contain the amount of hits that occured during the 5th minute. My question is, does the loop seem like a better idea? ANd if so, is my logic sound? -- Open BlueDragon Public Mailing List http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon mailing list - http://groups.google.com/group/openbd?hl=en !! save a network - please trim replies before posting !!
