Solved

What is it that i have to watch out in using Q of Qs?

Posted on 2004-09-01
9
139 Views
Last Modified: 2013-12-24
Hello

EVerything i read about Q of Q is great and so to make my app more optimized i am starting to convert to Q of Q and have been successful so far.   My app is like a statistical style of presenting data so i thought Q of Q will be useful.  

Is there any reason or anything I should watch out for when it comes to Q of Q.

Thanks
0
Comment
Question by:mdbbound
9 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 11959347
Hi mdbbound,
1) you can not use joins there....

and there is great article of query of query on sitepoint... I have once read... give me sec and I will find that out from my archives...


Regards,
---Pinal
0
 
LVL 21

Accepted Solution

by:
pinaldave earned 175 total points
ID: 11959353
Hi mdbbound,
Applying Query of Queries

When should you use Query of Queries? In my humble opinion, Query of Queries should be used when you can limit the number of connections to a database server without hampering the coding and design of your application. I don't recommend you rush out and run 10 select * from Table statements, using Queries of Queries throughout your application. Keep in mind that the result sets you create have to be stored somewhere. Most of the time, they're stored in memory (be it RAM or virtual) and, if you don't plan properly, your server could slow to a crawl or even stop because of low memory resources.

As with any project, it's best to plan out the entire system first, then go back and refine it multiple times, as you seek ways to increase performance, security, and overall stability. It's during this refinement phase that you can pinpoint possible areas in which Query of Queries will be beneficial for the applications and the system resources. I hope this article has given you the drive to look over your code and see how you can implement the query of queries functionality into your projects.


http://www.sitepoint.com/article/query-queries
Regards,
---Pinal
0
 
LVL 21

Assisted Solution

by:pinaldave
pinaldave earned 175 total points
ID: 11959359
Hi mdbbound,
well, my coworker also suggested if you want to optime the quries then you should take care for caching them too....
http://www.macromedia.com/devnet/server_archive/articles/query_caching_cf.html great analsys.

Regards,
---Pinal
0
 
LVL 17

Assisted Solution

by:anandkp
anandkp earned 100 total points
ID: 11960460
yeah - dont just blindly convert all ur queries to Q of Q ... just analyse the need & use it sensibly ...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Assisted Solution

by:Plucka
Plucka earned 100 total points
ID: 11962628
I think they are great for some things but you loose a lot, here's what I mostly miss.

1. Joins
2. Calcs ie select a+b as c
3. Any DB specific functions like IsNULL in SQL Server
4. Proper handling of dates, seem to always have to use <cfqueryparam

I try to avoid them as much as possible, but have a really good data grid and can pass it either a table, view, stored procedure or q of q, sometimes I just can do what I want and q of q is the answer.

But as the above people mentioned they should only be used if there is a need or a performance benefit.
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 125 total points
ID: 11964761
You can do joings, just not all of the kinds of joins.

In general you should do as much as you can in the actual query, but there are uses for QoQs.

For example.

I have a manually generated query that I ad cells to, but in the end I only want distinct values.  Perfect place for a QoQ

Or I have sesssion data that I want to join with database data - another good place.

But if I can do what I want in the DB alone - bad place for QoQs.


Here is some sample code :

<!--- loop over each item in being shipped --->
<cfloop index="i" from="0" to="#maxItem#">
<cfset currentItem = Form['Item_' &i]>
<cfset currentClassCode = Form['ClassCode_' &i]>
<cfset currentPrice = Form['Price_' &i]>
<cfset currentShipQuantity = Form['ShipQty_' &i]>


<!--- Add Tax and shipping to Accounting Info query --->
<cfif Form.ShippedTax GT 0>
      <cfset QueryAddRow(AccountingInfo)>
      <cfset QuerySetCell(AccountingInfo, "ClassCode", "TAX")>
      <cfset QuerySetCell(AccountingInfo, "Amount", Form.ShippedTax)>
</cfif>
<cfif Form.ShippingCharge GT 0>
      <cfset QueryAddRow(AccountingInfo)>
      <cfset QuerySetCell(AccountingInfo, "ClassCode", "SHP")>
      <cfset QuerySetCell(AccountingInfo, "Amount", Form.ShippingCharge)>
</cfif>
<!--- Summarize Accounting Info (group by class code) --->
<cfquery dbtype="query" name="AccountingSummary">
      SELECT ClassCode, SUM(Amount) AS Amount FROM AccountingInfo GROUP BY ClassCode
</cfquery>
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 125 total points
ID: 11964797
(whoops missing some code above)

Here it is again:

<!--- loop over each item in being shipped --->
<cfloop index="i" from="0" to="#maxItem#">
<cfset currentItem = Form['Item_' &i]>
<cfset currentClassCode = Form['ClassCode_' &i]>
<cfset currentPrice = Form['Price_' &i]>
<cfset currentShipQuantity = Form['ShipQty_' &i]>

<!--- Add info about current item to Accounting Query --->
<cfset QueryAddRow(AccountingInfo)>
<cfset QuerySetCell(AccountingInfo, "ClassCode", currentClassCode)>
<cfset QuerySetCell(AccountingInfo, "Amount", currentPrice * currentShipQuantity)>
</cfloop>

<!--- Add Tax and shipping to Accounting Info query --->
<cfif Form.ShippedTax GT 0>
     <cfset QueryAddRow(AccountingInfo)>
     <cfset QuerySetCell(AccountingInfo, "ClassCode", "TAX")>
     <cfset QuerySetCell(AccountingInfo, "Amount", Form.ShippedTax)>
</cfif>
<cfif Form.ShippingCharge GT 0>
     <cfset QueryAddRow(AccountingInfo)>
     <cfset QuerySetCell(AccountingInfo, "ClassCode", "SHP")>
     <cfset QuerySetCell(AccountingInfo, "Amount", Form.ShippingCharge)>
</cfif>
<!--- Summarize Accounting Info (group by class code) --->
<cfquery dbtype="query" name="AccountingSummary">
     SELECT ClassCode, SUM(Amount) AS Amount FROM AccountingInfo GROUP BY ClassCode
</cfquery>
0
 

Author Comment

by:mdbbound
ID: 11981123
Thanks everyone, excellent guidelines and points.
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11981125
well, glad to help you,
Regards,
---Pinal
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now