sql - Coldfusion cfloop Sql查询速度极慢

Question

我有用户参加的测验列表，并列出了正确答案的数量，测验类别，分数和测验ID。

在Coldfusion中，有一个cfloop贯穿每个测验，并即时计算每个测验的平均分数，最高分数，低分数并显示出来。这是永远要加载的，是否有任何方法可以优化cfloop？

原始查询是这样的：

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

然后，Cfloop通过此查询对每个测验名称，测验进行过滤，并获得平均分数，最高分数和最低分数，如下所示：

<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

您正在循环内运行多个CF查询查询。您应该可以将其替换为一个。

<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>

另外，在主查询中，替换

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

与

where datecompleted >= '2019-01-01'