CFC's and performence

Posted At : May 8, 2007 7:47 PM | Posted By : Admin
Related Categories: OO,ColdFusion

I am parsing out data I had collected with a survey engine I wrote. The whole thing is relational so the results only have ID's and the returned value of the answer (which is either another ID or a string). So I query of all the results and then I use a query of a query to tease out the answers for each question for each user. Like so:

<cfquery name="get_Results_ForUser" dbtype="query" >
                        SELECT    Survey_Results_ID,
                              NUM_Survey_Results_Value,
                              MEM_Survey_Results_Value,
                              LKP_Survey_Main_ID,
                              LKP_Survey_Question_Type_ID,
                              LKP_Survey_Question_ID
                        FROM    retget_ALL_Survey_Results
                        WHERE    LKP_Survey_Question_ID = #Get_Survey_Questions.Survey_Question_ID#
                        AND      LKP_empid = #Ralph#
                     </cfquery>

But when I took it and stuck it in a component to tighten up the code like so:

<cffunction name="get_Results_ForUser" displayName="Gets Results For a User " hint="Takes a query of all the questions for a survey and returns jsut the Questions For that Category" access="public" returnType="query" output="true">
      <cfargument name="retget_ALL_Survey_Results" displayName="Get_Survey_Questions" hint="The Query that has ALL the Survey_Results" type="query" required="true" default="NULL">
      <cfargument name="Survey_Question_ID" displayName="Survey_Question_ID" hint="The Questions ID" type="numeric" required="true" default="0">
      <cfargument name="LKP_empid" displayName="LKP_empid" hint="The emp id" type="numeric" required="true" default="0">

          <CFSET var Q_get_Results_ForUser = "">

         <cfquery name="Q_get_Results_ForUser" dbtype="query" >
               SELECT    Survey_Results_ID,
                     NUM_Survey_Results_Value,
                     MEM_Survey_Results_Value,
                     LKP_Survey_Main_ID,
                     LKP_Survey_Question_Type_ID,
                     LKP_Survey_Question_ID
               FROM    retget_ALL_Survey_Results
               WHERE    LKP_Survey_Question_ID = #Survey_Question_ID#
               AND      LKP_empid = #LKP_empid#
            </cfquery>

      <cfreturn Q_get_Results_ForUser >
   </cffunction>

It took a HUGE performance hit on the scale of ten fold. The render time for the page that called it went from 400-500 milliseconds to 7000 milliseconds. I read that there was overhead issues when using scripts, UDF's and components. Now I know.....