Solved

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

Posted on 2004-09-01
9
144 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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