Solved

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

Posted on 2004-09-01
9
138 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
yeah - dont just blindly convert all ur queries to Q of Q ... just analyse the need & use it sensibly ...
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Assisted Solution

by:Plucka
Plucka earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
(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
Comment Utility
Thanks everyone, excellent guidelines and points.
0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
well, glad to help you,
Regards,
---Pinal
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

10 Experts available now in Live!

Get 1:1 Help Now