3 Replies Latest reply on Mar 7, 2007 12:03 PM by Newsgroup_User

    CFC vs. Query performance

      I created a discussion board. Each entry displays the preferences of the user who posted the entry, such as 'Allow Private Messages', 'Allow Emails' etc. When looping through the entries, I have to query the database to get the preferences for each individual 'poster'. Because I thought it would increase performance, I created a CFC method and call the Object into the session scope. Then I initiate the method when looping through the results and setting a variable in the THIS scope in the instance to the query result value.

      When comparing the two ways of doing it, there's a four-fold increase in load time using the CFC, this doesn't seem right and I suspect I am doing something wrong.

      Below are the two different approaches, maybe someone can help me:

      Without CFC:

      <cfoutput query="results">
      <cfquery name=getPoster datasource="xxx">
      select first_name
      from users
      where user_id = #results.user_id#

      With a CFC:

      CFC Code:

      <cfset THIS.user_data_id = 0>
      <cffunction name="getPosterPrefs" access="public" returntype="query">
      <cfargument name="user_data_id" required="yes" type="numeric">

      <cfquery name="getposter" datasource="xxx">
      SELECT allow_pm, pm_ignore_list, show_email
      FROM forum_user_data
      WHERE user_data_id = #THIS.user_data_id#

      <cfreturn getPMprefs1>



      In the page:

      <cfif (not structKeyExists(session, "replyPrefs"))>
      <cflock name="lock_replyPrefs" timeout="5" type="exclusive">
      <cfobject component="/tvocomponents.forums" name="session.replyPrefs">

      <cfoutput query="results">
      <cfset session.replyPrefs.user_data_id = #getReplyPoster.user_data_id#>
      <cfif #session.replyPrefs.getPosterPrefs(getReplies.thread_reply_user_id).allow_pm# eq 1>Allow Private Messages</cfif>

      Thanks for any help.
        • 1. Re: CFC vs. Query performance
          Level 7
          Well yes, all you did was move your query into a CFC that must be
          initiated for every user in your display. So you are still doing each
          and every query you where doing before, and on top of that you are
          creating an object around it. This is not going to improve performance
          in any way.

          I'm probably not the one to help you improve this with OOD. I'm not
          that good at it myself. I'm still struggling with beans, DAOs,
          gateways, factories, and MVP designs and how you put them all together

          But at the most basic level, what you want to remove is the individual
          queries run sequentially, whether you use procedural or OOP logic.
          Ideally you would run one query that gets all the data for all the users
          you need to know about in one swoop and save this as long as you need
          it, so you don't have to do it again unnecessarily.
          • 2. Re: CFC vs. Query performance
            nonnybonny Level 1
            Thanks for your response. I understand I am doing the same thing, essentially, but what I don't understand is why there is such a significant processing time difference between the two.

            True enough, I should just run one query to get what I want... that would improve performance overall...
            • 3. Re: CFC vs. Query performance
              Level 7
              I don't understand is why there is such a significant processing time
              difference between the two.

              Because CREATING a new instance of an object is very expensive, and you
              are doing this over and over once for each user displayed. The savings
              comes from designs where you can minimize and optimize when and how
              often you do this. Ideally you create an instance of an object once,
              usually at the beginning or some other convenient point in the logic
              flow, then use this instance over and over again, for as long as you can.

              Using initiated CFC objects is very efficient! Creating them - not so much.