CODEWITHSUNDEEP
sqlcoldfusioncfloop
tamilgirl
tamilgirl

Reputation: 69

Coldfusion cfloop Sql queries extremely slow

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>

Upvotes: 2

Views: 181

Answers (2)

Shawn
Shawn

Reputation: 4786

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.

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

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'

Upvotes: 3

Related Questions