CFC vs. Query performance

[nonnybonny]

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#
</cfquery>
</cfoutput>


With a CFC:

CFC Code:
<cfcomponent>

<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#
</cfquery>

<cfreturn getPMprefs1>

</cffunction>


</cfcomponent>

In the page:

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

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

Thanks for any help.

[Newsgroup_User]

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

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.

[nonnybonny]

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

[Newsgroup_User]

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.