[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

QoQ Optimizes?

I have currently set up two pages in coldfusion to see how much using Query of Queries really makes a difference in server usage. What I have found is it's contradictory to what it should be. On the page that uses differing queries pulling from the same table it is actually faster than the page that is using QoQ's.

I'm using Coldfusion's local debuging environment under the execution times to get my information

What I am doing with the queries is analysing data through recordsets, variable arithmatic etc. and inserting into fusion chart graphs on the page.

Can anyone explain why this might be so?
0
jasch2244
Asked:
jasch2244
  • 7
  • 5
  • 2
  • +2
1 Solution
 
Jones911Commented:
Show some code.  QoQ is ussually faster.
0
 
jasch2244Author Commented:
Jones911: I was hoping you were not going to ask that it's a long one so bear with me. I have included the QoQ file (just queries). If you want me to post the other file I'll do so. I wish I could include more points due to the size of the code. Sorry
<cfset lcdate=now()>
<cfset currentYear= year(now())>
<cfset currentMonth= month(now())>
 
<!--- YTD STUFF --->
<cfquery name="ytdDollars" datasource="#application.datasource#">
SELECT
closings.ClosingDate, year(closings.ClosingDate) as cYear,
closings.Commission, sum(closings.Commission) as  Comish,
closings.Bonus, sum(closings.Bonus) as totBonus,
closings.ReferralPaid, sum(closings.ReferralPaid) as Referral
FROM
closings
WHERE
year(closings.ClosingDate) = #currentYear#
GROUP BY
cYear
</cfquery>
 
<cfset ytdNet = (#ytdDollars.Comish#-#ytdDollars.Referral#)+#ytdDollars.totBonus#>
 
 
<!-------------------------------------  USE FOR PREVIOUS MONTH ------------------->
<!--- previous date - 1month --->
<cfset prevMdate = dateadd("m",-1,lcdate)> 
 
<!--- previous month --->
<cfset prevMmonth= month(prevMdate)>
 
<!--- previous year --->
<cfset prevMyear = year(prevMdate)>
 
<!--- change previous last day depending on month --->
<cfset prevMlastday = daysInMonth(prevMdate) />
 
<cfset fromMdate=createdate(prevMyear,prevMmonth,1)>
<cfset lastMdate=createdate(prevMyear,prevMmonth,prevMlastday)>
 
<!--------------------------------------  USE FOR PREVIOUS YEAR ------------------->
<!--- previous date - 1year --->
<cfset prevYdate = dateadd("yyyy",-1,lcdate)> 
 
<!--- previous month --->
<cfset prevYmonth= month(prevYdate)>
 
<!--- previous year --->
<cfset prevYyear = year(prevYdate)>
 
<!--- change previous last day depending on month --->
<cfset prevYlastday = daysInMonth(prevYdate) />
 
<cfset fromYdate=createdate(prevYyear,prevYmonth,1)>
<cfset lastYdate=createdate(prevYyear,prevYmonth,prevYlastday)>
 
 
<!--------------------------------------  MASTER CLOSING QUERYs ------------------->
<cfquery name="qCurrYear" datasource="#application.datasource#" cachedwithin="#CreateTimeSpan(0,0,5,0)#">
SELECT
ClosingDate, YEAR(ClosingDate) as theY, MONTH(ClosingDate) as theM,
ClosingID,
ListingId,
Commission,
Bonus,
ReferralPaid
FROM
closings
WHERE
year(ClosingDate) = #currentYear# <!--- CURRENT YEAR --->
</cfquery>
 
<cfquery name="qPrevYear" datasource="#application.datasource#" cachedwithin="#CreateTimeSpan(0,0,5,0)#">
SELECT
ClosingDate, YEAR(ClosingDate) as theY, MONTH(ClosingDate) as theM,
ClosingID,
ListingId,
Commission,
Bonus,
ReferralPaid
FROM
closings
WHERE
year(ClosingDate) = #prevYyear#<!--- PREVIOUS YEAR --->
</cfquery>
 
 
<!--------------------------------------  LISTINGS TAKEN DATA ------------------->
<cfloop from="1" to="12" index="m">
<cfquery name="lTaken#Left(MonthAsString(m), 3)#" datasource="#application.datasource#">
SELECT
listings.ListDate
FROM
listings
WHERE 
month(listings.ListDate) = #m# AND year(listings.ListDate) = #currentYear#
</cfquery>
</cfloop>
<!--- GET TOTAL --->
<cfquery name="lTakenTotal" datasource="#application.datasource#">
SELECT
listings.ListDate
FROM
listings
WHERE 
year(listings.ListDate) = #currentYear#
</cfquery>
 
<!--------------------------------------  LISTINGS SOLD DATA ------------------->
<cfloop from="1" to="12" index="m">
<cfquery name="LS#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
ClosingDate,
ClosingID,
ListingId
FROM
qCurrYear
WHERE
theM = #m# AND theY = #currentYear# AND ListingId != 0
</cfquery>
</cfloop>
 
<!--- GET TOTAL --->
<cfquery name="LSTotal" dbtype="query">
SELECT
ClosingDate,
ClosingID,
ListingId
FROM
qCurrYear
WHERE
theY = #currentYear# AND ListingId != 0
</cfquery>
 
 
<!------------------------------------------- BUYERS SALES DATA --------------->
<cfloop from="1" to="12" index="m">
<cfquery name="BS#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
ClosingDate,
ClosingID,
ListingId
FROM
qCurrYear
WHERE
theM = #m# AND theY = #currentYear# AND ListingId = 0
</cfquery>
</cfloop>
 
<!--- GET TOTAL --->
<cfquery name="BSTotal" dbtype="query">
SELECT
ClosingDate,
ClosingID,
ListingId
FROM
qCurrYear
WHERE
theY = #currentYear# AND ListingId = 0
</cfquery>
 
<!--------------- BEGIN TOTAL SALES TALLEY CURRENT YEAR----------------------->
<cfloop from="1" to="12" index="m">
<cfquery name="TSales#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
ClosingDate
FROM
qCurrYear
WHERE
theM = #m# AND theY = #currentYear# 
</cfquery>
</cfloop>
<!--- END BY MONTH / CURRENT YEAR --->
 
<!--------------- TOTAL SALES CURRENT YEAR / NO MONTHS---------------------->
<cfquery name="TSalesTotal" dbtype="query">
SELECT
ClosingDate
FROM
qCurrYear
WHERE
theY = #currentYear# 
</cfquery>
 
 
<!--------------- BEGIN TOTAL SALES TALLEY PREVIOUS YEAR RECORDCOUNT----------------------->
 
<cfloop from="1" to="12" index="m">
<cfquery name="TSalesPrev#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
ClosingDate
FROM
qPrevYear
WHERE
theM = #m# AND theY = #prevYyear# 
</cfquery>
</cfloop>
 
<!--------------- GET TOTAL COMISH (PER MONTH) CURRENT YEAR----------------------->
 
<cfloop from="1" to="12" index="m">
<cfquery name="MoComCur#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
theM,
sum(Commission) as TotComish,
sum(Bonus) as TotBonus,
sum(ReferralPaid) as TotReferral
FROM
qCurrYear
WHERE theM = #m#
GROUP BY theM
</cfquery>
</cfloop>
 
 
<!--------------- GET TOTAL COMISH (PER MONTH) PREVIOUS YEAR----------------------->
 
<cfloop from="1" to="12" index="m">
<cfquery name="MoComPrev#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
theM,
sum(Commission) as TotComish,
sum(Bonus) as TotBonus,
sum(ReferralPaid) as TotReferral
FROM
qPrevYear
WHERE theM = #m#
GROUP BY theM
</cfquery>
</cfloop>
 
<!--- GOAL QUERIES --->
<cfquery name="gListingGoals" datasource="#application.datasource#">
SELECT
goalsetting.trans_type,
goalsetting.year,
goalsetting.income_goal,
goalsetting.percent_of_income,
goalsetting.avg_salesprice,
goalsetting.agent_comish_split,
goalsetting.conversion_per,
goalsetting.avg_precent_comish
FROM
goalsetting
WHERE
goalsetting.trans_type = "Listings"
</cfquery>
 
<cfquery name="gSellingGoals" datasource="#application.datasource#">
SELECT
goalsetting.trans_type,
goalsetting.year,
goalsetting.income_goal,
goalsetting.percent_of_income,
goalsetting.avg_salesprice,
goalsetting.agent_comish_split,
goalsetting.conversion_per,
goalsetting.avg_precent_comish
FROM
goalsetting
WHERE
goalsetting.trans_type = "Selling"
</cfquery>
 
<!--- LISTINGS GOALS SUMMARY VARS--->
<cfset lIncGoal = #gListingGoals.income_goal#>
<cfset lPercTotInc = #gListingGoals.percent_of_income#/100>
<cfset lAnnuDollInc = #lIncGoal#*#lPercTotInc#>
<cfset lAvgSalesPrice = #gListingGoals.avg_salesprice#>
<cfset lAvgComishPer = #gListingGoals.avg_precent_comish#/100>
<cfset lAgentSplit = #gListingGoals.agent_comish_split#/100>
<cfset lComishPUnit = #lAvgSalesPrice#*#lAvgComishPer#>
<cfset lAgentComishPUnit = #lComishPUnit#*#lAgentSplit#>
<cfset lIfAllSold = Int(#lAnnuDollInc#/#lAgentComishPUnit#)>
<cfset lConversion = #gListingGoals.conversion_per#/100>
<cfset lAnnGoal = Int(#lIfAllSold#/#lConversion#)>
<cfset lMoGoal = Int(#lAnnGoal#/12)>
 
<!--- SELLING GOALS SUMMARY VARS--->
<cfset sIncGoal = #gSellingGoals.income_goal#>
<cfset sPercTotInc = #gSellingGoals.percent_of_income#/100>
<cfset sAnnuDollInc = #sIncGoal#*#sPercTotInc#>
<cfset sAvgSalesPrice = #gSellingGoals.avg_salesprice#>
<cfset sAvgComishPer = #gSellingGoals.avg_precent_comish#/100>
<cfset sAgentSplit = #gSellingGoals.agent_comish_split#/100>
<cfset sComishPUnit = #sAvgSalesPrice#*#sAvgComishPer#>
<cfset sAgentComishPUnit = #sComishPUnit#*#sAgentSplit#>
<cfset sIfAllSold = Int(#sAnnuDollInc#/#sAgentComishPUnit#)>
<cfset sConversion = #gSellingGoals.conversion_per#/100>
<cfset sAnnGoal = Int(#sIfAllSold#/#sConversion#)>
<cfset sMoGoal = Int(#sAnnGoal#/12)>
 
<!--- TOTAL SALES UNITS VIA GOALS --->
<cfset TotalSalesGoal = #sIfAllSold#+#lIfAllSold# >
<cfset TotalMoSalesGoal = #lMoGoal#+#sMoGoal#>
<cfset ComishMoGoal = #sIncGoal#/12>
 
<!--- VARS FOR PIE GRAPH --->
<cfset TotalSales = #TSalesTotal.recordcount#>
<cfset ListSalesPer = #LSTotal.recordcount#/#TotalSales#>
<cfset BuySalesPer = #BSTotal.recordcount#/#TotalSales#>

Open in new window

0
 
Jones911Commented:
Can u just post say 1 query thats qoq compared to the one that's q direct query.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
jasch2244Author Commented:
Sure no problem... It does not make any sense unless the local debugging in Coldfusion is bunk
MASTER QUERY//////
<cfquery name="qCurrYear" datasource="#application.datasource#" cachedwithin="#CreateTimeSpan(0,0,5,0)#">
SELECT
ClosingDate, YEAR(ClosingDate) as theY, MONTH(ClosingDate) as theM,
ClosingID,
ListingId,
Commission,
Bonus,
ReferralPaid
FROM
closings
WHERE
year(ClosingDate) = #currentYear# <!--- CURRENT YEAR --->
</cfquery>
 
QoQ////////////
<cfloop from="1" to="12" index="m">
<cfquery name="LS#Left(MonthAsString(m), 3)#" dbtype="query">
SELECT
ClosingDate,
ClosingID,
ListingId
FROM
qCurrYear
WHERE
theM = #m# AND theY = #currentYear# AND ListingId != 0
</cfquery>
</cfloop>
 
NO QoQ:
<cfloop from="1" to="12" index="m">
<cfquery name="LS#Left(MonthAsString(m), 3)#" datasource="#application.datasource#">
SELECT
closings.ClosingDate,
closings.ClosingID,
closings.ListingId
FROM
closings
WHERE
month(closings.ClosingDate) = #m# AND year(closings.ClosingDate) = #currentYear# AND closings.ListingId != 0
</cfquery>
</cfloop>

Open in new window

0
 
Jones911Commented:
The 2 queries are different though.
0
 
jasch2244Author Commented:
Yes they are but only in the as clauses and columns I'm referencing out of the table. I figured since it's a master query I'll get everything I need as opposed to the smaller query that is just getting what it needs for the output.
0
 
Jones911Commented:
Yep I mean the QoQ and the Non QoQ so its hard to compare times.
0
 
jasch2244Author Commented:
Let me ask you this.. is the debugging execution time accurate? Because as it stands right now I'm consistantly getting better times without using QoQ which is bugging me. Should I sweat it?
0
 
Jones911Commented:
The timers are accurate.  Its odd that the times are slower with QoQ unless they are takign up load of memory and your serve is low on memory and its swaping to disk.
0
 
duncancummingCommented:
How much slower are we talking about here?
0
 
jasch2244Author Commented:
Ranging any where from 150 to 200 in difference. I'm going over the 250 execution time when the page first loads, after that if I refresh it's right around the 250 mark. On the page that does not have the QoQ it is below 150.
0
 
eszaqCommented:
If iyt is matter of solving the problem, not just being bugged by this puzzle, try to keep this only in your Master query:
   WHERE  year(closings.ClosingDate) = #currentYear# AND closings.ListingId != 0

This way you'll have less records to go through in QoQ (why do you check for current year again in QoQ anyway? your master has in in WHERE clause)
0
 
eszaqCommented:
So, your QoQ will only have
  WHERE theM = #m#
0
 
gdemariaCommented:
Query of query is not faster than hitting the database.
The database is designed to fetch data from tables and does it very fast; usually in less than 50 mseconds.

The query of queries is a process of looping through array/structures type values and filtering it using database type syntax which it has to interpret as well.    It does not work as quickly as the database can process.

Query of Queries should be used for convenience, not as a way to optimize your code, because it usually doesn't make things faster, but slower.

I think the results you found are accurate/typical.

0
 
jasch2244Author Commented:
Thank you that is what I was needing... a little reasurance.
0
 
gdemariaCommented:
Just a tip on optimizing your code.   I glanced through it (not a thorough analysis)

It looks like you're doing a lot of queries, compiling totals with the intent of generating a report perhaps.

Your best bet may be to move this into a database level procedure.

You could build a temporary table, populate it from all your different queries, perform the sums and such on that table and then select * from temp to pass the results back to Coldfusion to display in the right format.   That would cut your time waaaaay down.
(again, I didn't really analyze the data to see how it all fits together though)
0
 
jasch2244Author Commented:
Thank you for your time. Everything seems to work fine as (as far as performance) for now. Being I'm a newb. just wanted to know which was the best or most optimized way to do it. I would be more concerned if the site I'm working on was high traffic.
thank you so much.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now