title: ColdFusion Query of Queries 100 x Slower than the Database Call Itself (with relatively low record counts)
body: <p>We have an analytics chart which first queries the database log table to pull all relevant from an optimized query only selecting what is needed along with grabbing relevant start and end ID's since this table has millions of records.  Once that initial query is pulled, we have used ColdFusion's query of queries to work with that data to display different charts.  </p>
  
  <p>You can see the actual external database call in this example grabs 2,240 records in 31ms:</p>
  
  <pre><code>qryGetLogs (Datasource=ourDSN, Time=31ms, Records=2204) 
  </code></pre>
  
  <p>We have a chart that displays views per hour for each day of the week and then builds a jQuery chart to display them.  From the initial design, those queries execution times were almost negligible, most often at 0ms. Because we loop over each hour in the day (24) for 7 days a week, that is 168 queries - one of the main reasons to not make the external DB call so many times.  </p>
  
  <p>It seems now that many, but not all, of those query of queries are taking over 100 times longer to run than the initial database call.  Most of them are using the BETWEEN date range function to select the records for each day and hour part:</p>
  
  <pre><code>qryViewsPerHour (Datasource=, Time=4312ms, Records=5)
  SELECT createdOn, DayOfWeek
  FROM qryGetLogs
  WHERE  (CreatedOn BETWEEN '2012-09-03 0:00:00' AND '2012-09-03 0:59:59')
  AND (DayOfWeek = 2)
  </code></pre>
  
  <p>You can see that query of another query took 4,312ms and was searching through a query with 2,240 records.  Here are the query times for many of the next queries:</p>
  
  <pre><code>qryViewsPerHour (Datasource=, Time=4610ms, Records=5)
  qryViewsPerHour (Datasource=, Time=4187ms, Records=8)
  qryViewsPerHour (Datasource=, Time=5062ms, Records=6)
  qryViewsPerHour (Datasource=, Time=3985ms, Records=0)
  qryViewsPerHour (Datasource=, Time=4828ms, Records=2)
  qryViewsPerHour (Datasource=, Time=5750ms, Records=0)
  qryViewsPerHour (Datasource=, Time=3016ms, Records=4)
  qryViewsPerHour (Datasource=, Time=3625ms, Records=6)
  qryViewsPerHour (Datasource=, Time=6265ms, Records=11)
  </code></pre>
  
  <p>So you can see just by those queries about, it added 40 seconds of loading time!  But notice the next query is just 78ms with more records than any of the previous query, and many after that, the times are better:</p>
  
  <pre><code>qryViewsPerHour (Datasource=, Time=78ms, Records=18)
  qryViewsPerHour (Datasource=, Time=62ms, Records=7)
  qryViewsPerHour (Datasource=, Time=63ms, Records=12)
  qryViewsPerHour (Datasource=, Time=78ms, Records=34)
  qryViewsPerHour (Datasource=, Time=78ms, Records=9)
  </code></pre>
  
  <p>Those go on for a while with good times, then BAM! Back to the 2-6 second queries.</p>
  
  <pre><code>qryViewsPerHour (Datasource=, Time=4891ms, Records=13)
  qryViewsPerHour (Datasource=, Time=1984ms, Records=8)
  qryViewsPerHour (Datasource=, Time=4875ms, Records=4)
  qryViewsPerHour (Datasource=, Time=6203ms, Records=0)
  </code></pre>
  
  <p>All in all, what was taking several seconds to load, is taking between 100-400 seconds loads, and that is just the weekly report!  We also use it for monthly reports.  </p>
  
  <p>I have monitored the server and made sure that I am the only person or process running the request, so it should not be that the CPU's resources are being eaten up by something else, and I also monitor the CPU request and it's solid and steady being used by JRUN.exe.  </p>
  
  <p>Does anyone have any advice on this problem?  It is driving me nuts!</p>
  
  <p>Thanks for any help.</p>