Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

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

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
mdbbound
Asked:
mdbbound
6 Solutions
 
pinaldaveCommented:
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
 
pinaldaveCommented:
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
 
pinaldaveCommented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
anandkpCommented:
yeah - dont just blindly convert all ur queries to Q of Q ... just analyse the need & use it sensibly ...
0
 
PluckaCommented:
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
 
mrichmonCommented:
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
 
mrichmonCommented:
(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
 
mdbboundAuthor Commented:
Thanks everyone, excellent guidelines and points.
0
 
pinaldaveCommented:
well, glad to help you,
Regards,
---Pinal
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now