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>

Answer 1

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'