Solved

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

Posted on 2004-09-01
9
143 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.htaccess rewrite rule to 301 redirect canonical versions of homepage to root 4 111
wordpress email form 23 82
AWS New EC2 Instance and EBS Storage 2 85
http to https 3 70
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 …
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…

828 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