Coldfusion cfloop Sql queries extremely slow

Question

I have list of quizzes that users take and table that keep tracks of number of questions they got right, quiz category, score and id for the quiz.

In coldfusion, there is a cfloop that goes through each of the quiz and on the fly calculates the average score, max score, low score for each quiz and displays it. This is taking for ever to load, is there any way to optimize the cfloop ?

The original query is like this:

SELECT     Quizname,
    NULLIF(QuizId, '') as  QuizId,
    NULLIF(InstructorId, '') as InstructorId,
    NULLIF(Location, '') as Location,
    cast(replace(quiz_user_quiz_percentage,'%','') as decimal(5,2)) as percentage
FROM         QuizResults
where 0=0
    and year(cast(datecompleted as date))>= 2019

Then Cfloop goes through this query to filter for each quizname, quizid and gets the average, max and min score like this:

<cfloop query="getEachQuiz" >
    <cfquery name="getStats" dbtype="query">
        SELECT 
            count(percentage) as countScore,
            max(percentage) as maxScore,
            min(percentage) as minScore,
            avg(percentage) as avgScore
        FROM data
        where Quizname= <cfqueryparam value="#getEachQuiz.Quizname#" cfsqltype="cf_sql_varchar" >
        and  QuizId= <cfqueryparam value="#getEachQuiz.QuizId#" cfsqltype="cf_sql_varchar" >
        <cfif len(getEachQuiz.InstructorId) gt 0>
            and InstructorId= <cfqueryparam value="#getEachQuiz.InstructorId#" cfsqltype="cf_sql_varchar" >
        </cfif>
        <cfif len(getEachQuiz.Location) gt 0>
            and Location=  <cfqueryparam value="#getEachQuiz.Location#" cfsqltype="cf_sql_varchar" >
        </cfif>
    </cfquery>
    <tr>
        <td>#getEachQuiz.Quizname#</td> 
        <td>#getEachQuiz.QuizId#</td>
        <td>#getStats.countScore#</td>
        <td>#numberformat(getStats.avgScore,'99.99')#%</td>
        <td>#getStats.maxScore#%</td>
        <td>#getStats.minScore#%</td>
    </tr>
</cfloop>

The problem is that you are running a query on each iteration of the loop. As @DanBracuk noted, do a join to hit the database once and use cfoutput with group attribute to display each quiz and the data for each. — Scott Stroz, Jan 09 '20 at 14:41
Could you give me a sample query please? Appreciate the replies — tamilgirl, Jan 09 '20 at 15:56
How many records is your #getEachQuiz# query returning? Have you calculated how long that query actually takes to run? Just wondering if the issue could be the original query and not the CFLoop? — CFMLBread, Jan 09 '20 at 19:39
The geteachquiz is faster and returns the records fast, its the loop thats causing the issue. — tamilgirl, Jan 09 '20 at 19:59
What does the `QuizResults` table look like? What are the data types of the columns? Rather than allowing `QuizID`, `InstructorID` and `Location` to contain an empty string that you have to test for, is it possible to make them `NULL` to start with? How many records are there in `QuizResults`? — Shawn, Jan 16 '20 at 20:45
Also, what is stored in `data` and `QuizResults`? It looks like some of this data may be repeated between the two tables. That may not be be necessary. Or the `data` table itself may not be necessary. Can you provide a simple example of what's in each of these tables? — Shawn, Jan 16 '20 at 20:58

score 3 · Accepted Answer · answered Jan 09 '20 at 21:56

You are running several CF query of queries inside a loop. You should be able to replace this with one.

<cfquery name="getStats" dbtype="query">
select quizname, quizid,instructorId, location
, count(percentage) as countScore
, min(percentage) as minScore
, max(percentage) as maxScore
, avg(percentage) as avgScore
from data
group by quizname, quizid,instructorId, location
</cfquery>

Also, in your main query, replace

where 0=0
    and year(cast(datecompleted as date))>= 2019

with

where datecompleted >= '2019-01-01'

score 0 · Answer 2 · answered Jan 16 '20 at 23:26

For my quick code review:

Review your database schema and make sure that your column data types are what they need to be.
Do your columns allow NULL? If they do, rather than inserting and empty string, use NULL. Then you don't need the NULLIF functions.
cast(replace(quiz_user_quiz_percentage,'%','') as decimal(5,2)) as percentage > It appears that your database allows you to put a "%" character in that column. That's special character in SQL and can make your life difficult. If you have the ability to change it to a simple integer, I'd do it. Also, do any formatting in the final output.
where 0=0 >> You don't need this condition unless you are building some sort of dynamic query, and you want to ensure there's at least one evaluation in the WHERE clause.
and year(cast(datecompleted as date))>= 2019 >> If you can, avoid functions on the left side of your WHERE comparison. It will negate the use of any index on that column, and slow your query down.
Using Query of Query to build up a result set can use a lot of resources. I've found that most things that can be done in QoQ are much better suited for the SQL itself.

I am assuming you are somehow passing in the year you want to filter by. So passing in "2019", you could do something like...

<cfset startDate = createDate(inputYear,1,1)> <!--- 2019-01-01 --->
<cfset endDate = dateAdd("yyyy",1,startDate)> <!--- 2020-01-01 --->

...then use those variables in your query to filter by the date.

NOTE: To make life easier on yourself, I'd fix the percentage column in the database to exclude the "%" character. See my notes below.

<cfquery name="getEachQuiz" ...>
SELECT qr.QuizName, qr.QuizID, qr.InstructorID, qr.Location
    , count(*) as countScore
    , max(qr.percentage) as maxScore
    , min(qr.percentage) as minScore
    , avg(qr.percentage) as avgScore
FROM QuizResults qr
WHERE qr.datecompleted >= <cfqueryparam value="2019-01-01" cfsqltype="cf_sql_date">
    AND qr.datecompleted < <cfqueryparam value="2020-01-01" cfsqltype="cf_sql_date">
GROUP BY QuizName, QuizID, qr.InstructorID, qr.Location
ORDER BY QuizName, QuizID, qr.InstructorID, qr.Location
</cfquery>

Then you can just output your results.

<cfoutput query="getEachQuiz">
    <tr>
        <td>#Quizname#</td> 
        <td>#QuizId#</td>
        <td>#countScore#</td>
        <td>#numberformat(avgScore,'99.99')#%</td>
        <td>#maxScore#%</td>
        <td>#minScore#%</td>
    </tr>
</cfoutput>

However, I'd also recommend that you include a column for both InstructorID and Location. You are grouping and counting by those in your query, and if they aren't identified in your results, you can lose context.

Also note that if you are converting those values to NULL in your original query, your results may not give you what you're expecting. How did you intend to count an instructor who is in both 'TN' and ''?

For a pretty fast alternative to date filtering, use a Calendar Table (https://github.com/shawnoden/SQL_Stuff/blob/master/sql_CreateDateDimension.sql). You'll want to modify the Calendar Table to fit your data and your needs, but they're a great tool.

<cfquery name="getEachQuiz" ...>
SELECT qr.QuizName, qr.QuizID, qr.InstructorID, qr.Location
    , count(*) as countScore
    , max(qr.percentage) as maxScore
    , min(qr.percentage) as minScore
    , avg(qr.percentage) as avgScore
FROM QuizResults qr
INNER JOIN CalendarTable ct ON qr.datecompleted = ct.theDate
    AND ct.theYear = <cfqueryparam value="#inputYear#" cfsqltype="cf_sql_integer">
GROUP BY QuizName, QuizID, qr.InstructorID, qr.Location
ORDER BY QuizName, QuizID, qr.InstructorID, qr.Location
</cfquery>

The set-based JOIN will usually be a lot faster than filtering with a date. Dates can be a ginormous headache.

Note: For a one-off, this isn't likely the best way to go. The main benefit to a Calendar Table is that it gives you easy access to common values that you might have to derive from a date. Not only can functions be slow, but they can also prevent you from properly using a table's index. For the most part, I still firmly believe that almost all databases can benefit from a Calendar Table.

Coldfusion cfloop Sql queries extremely slow

2 Answers2