Coldfusion cfloop查询非常慢

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通过这个查询对每个测试名称、quizid进行筛选，得到平均分数、最大值和最小分数，如下所示：

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

Answer 2

对于我的快速代码审查：

• 检查您的数据库架构并确保您的列数据类型是它们所需要的。
• 您的列是否允许NULL？如果是这样，则使用NULL，而不是插入和空字符串。然后，您不需要functions.
• cast(replace(quiz_user_quiz_percentage,'%','') as decimal(5,2)) as percentage >，您的数据库似乎允许您在该列中放置一个"%“字符。这是SQL中的特殊字符，可能会给您的生活带来困难。如果你有能力把它变成一个简单的整数，我就会这么做。此外，在最终输出中执行任何格式设置。
• where 0=0 clause.
• and year(cast(datecompleted as date))>= 2019 >>您不需要此条件，除非您正在构建某种动态查询，并且您希望确保WHERE clause.
• and year(cast(datecompleted as date))>= 2019>>中至少有一个计算值(如果可以的话)，避免在WHERE比较的左侧使用函数。它将否定对该列的任何索引的使用，并减慢您的查询速度。
• 使用查询的查询来构建结果集可以使用大量的资源。我发现在QoQ中可以做的大多数事情更适合于SQL本身。

我假设你要在你想要过关的一年里度过。所以在“2019年”中，你可以这样做.

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

...then使用查询中的这些变量按日期进行筛选。

注意:为了让自己过得更轻松，我会修复数据库中的percentage列，以排除"%“字符。见下面我的笔记。

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

然后你就可以输出你的结果。

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

但是，我还建议您为InstructorID和Location都包括一个列。您正在按查询中的内容进行分组和计数，如果结果中没有标识它们，则可能会丢失上下文。

还请注意，如果要在原始查询中将这些值转换为NULL，则结果可能不会给出所期望的结果。你打算如何计算一位同时在“TN”和“”中的讲师？

对于日期筛选的快速替代方法，请使用日历表(https://github.com/shawnoden/SQL_Stuff/blob/master/sql_CreateDateDimension.sql)。您可能希望修改日历表以满足您的数据和需要，但它们是一个很好的工具。

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

基于集合的JOIN通常比使用日期过滤快得多。约会可能是个令人头痛的问题。

注意:对于一次性，这不太可能是最好的方式。日历表的主要优点是它使您可以轻松地访问可能必须从日期派生的公共值。函数不仅可以是缓慢的，而且还可以阻止您正确地使用表的索引。在大多数情况下，我仍然坚信几乎所有的数据库都可以从日历表中获益。