[cfaussie] Re: QofQ

Tue, 27 Jun 2006 18:21:11 -0700

Instead of selecting the records between those times, create a new field/column in the query that represents that information. ie
(datepart(hour,your_date) * 2 + iif(datepart(minute,your_date) >= 30,1,0) as half_hour
Then you can order by that field and use grouping if you want.

Blair

On 6/28/06, Rod Higgins < [EMAIL PROTECTED]> wrote:
Im checking 30 minute increments (datetime) on a date column so Im using the QofQ to determine whether a record exists within a certain start and end time. I will more then likely create an array from the date column and then test against this. I had thought QofQ would be the more elegant approach. I was more wondering if anyone had a better simpler way. It's a pity QofQ seems so inefficient.
 
Thanks
----- Original Message -----
From: Joel Cass
Sent: Wednesday, June 28, 2006 10:57 AM
Subject: [cfaussie] Re: QofQ

Hi Rod,
 
If it's just one field and you are matching dates (ie. date1 = date2), a referential struct may help, ie
 
<cfset struct[date]=arrayNew(1)>
...
<cfset arrayAppend(struct[date],structOfcolumns)>
 
Or you may be able to use looping or cfoutput grouping, ie.
 
<cfoutput query="myquery" group="datefield">
    #datefield#<hr />
    <cfoutput>
        #recordDetails#<br>
    </cfoutput>
    <hr />
</cfoutput>
 
or
 
<cfloop query="myquery">
    <cfif datefield IS thisDate>
        ...
    </cfif>
    <cfif dateField IS thatDate>
        ...
    </cfif>
</cfloop>
 
Not really sure about the purpose you need but there would be a solution without QoQ, just not sure how difficult it would be to implement.
 
QoQ is pretty inefficient - I'm not saying you shouldn't use it, I just don't like QoQ all that much. It's a personal thing, really. Perhaps someone with more experience in QoQ may be able to help..
 
Joel
-----Original Message-----
From: [email protected] [mailto:[email protected]]On Behalf Of Rod Higgins
Sent: Wednesday, 28 June 2006 10:39 AM
To: [email protected]
Subject: [cfaussie] Re: QofQ

Hi Joel,
 
The reason Im using QofQ firstly is because Im lazy and secondly because Im using a lot of date/time matching from the source recordset. The source recordset has about 160 odd records so it's not that large. What I am finding though is QofQ is not really that efficient. I was wondering what other ppl's thoughts are on this approach. I was under the impression this approach would be faster then hitting the db 50 odd times pulling out recordsets containing one or two records each. Maybe I will have to convert the orginial recordset into arrays, and then pull out the data that way instead.
 
Any thoughts?
 
----- Original Message -----
From: Joel Cass
Sent: Wednesday, June 28, 2006 10:27 AM
Subject: [cfaussie] Re: QofQ

As someone who does not use QoQ that often, here are some other ways you can do this:
 
- If you want to look up a certain value, create a referential struct based on the column you wish to lookup, containing the vital information
- If you want to create little queries, you could try query functions (querynew, queryAddColumn etc)
- You could try converting the query into an array, CFC, function or other structure that retrieves data in a more relevant way
- Use query caching and the startrow / endrow features of cfloop
- Do lots of little queries with the database instead of continually messing around with one big query stored in memory
 
CPU and memory are different things. Someone with a CS degree should clear that up for you. Storing data in memory doesn't mean that you avoid processing the data when you start using it in QoQ.
 
I don't know why people use QoQ anyway. It seems limited compared to other ways of processing data. That's just a personal opinion. I know it's probably extremely useful in very specific cases.
 
Joel
 
-----Original Message-----
From: [email protected] [mailto: [email protected]]On Behalf Of Rod Higgins
Sent: Wednesday, 28 June 2006 9:50 AM
To: [email protected]
Subject: [cfaussie] QofQ

When running a page that uses excessive QofQ from a single query the CPU goes bezerk. I was under the impression that QofQ was simply accessing a recordset from memory. I have a couple of questions if anyone has some ideas, workarounds for this. Why does the cfstat utility record QofQ's as DB hits/sec? Also what methods have ppl used to improve performance on pages that use a lot of QofQ - where talking about 50 odd QofQ calls to a single recordset.
 
TIA









--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie
-~----------~----~----~----~------~----~------~--~---

Reply via email to