Coldfusion speed up

Hello experts.
I need your opinion what is the best way to get data from database for the best functionality.
I have a main options table with all the stored option_value and and option_text for my select boxes and lists
like:
attr_name   option_value option_text language

I'm using now for each select box a separate function but i thought if i could get all the data with one call and than use dbtype="query" to output the values.

My test code with option 1 and 2:
1. Option:
<cfset q_miles = objgetSelects.f_MILES(attr_name = 'MILES',ascdesc = 'ASC') />
<cfset q_weight = objgetSelects.f_WEIGHT(attr_name = 'WEIGHT',ascdesc = 'ASC') />

<cffunction name="f_MILES" access="public" returntype="query">
 <cfargument name="ascdesc" required="no" default="desc">
 <cfargument name="attr_name" required="yes">
 <cfset var q_MILES = "">
 <cfquery name="q_MILES" datasource="#variables.dsn#" cachedWithin = "#CreateTimeSpan(365, 0, 0, 0)#">
   SELECT option_value, option_text
   FROM attributes_options
   WHERE attr_name = '#arguments.attr_name#'
   <cfif isdefined('arguments.language') and len('arguments.language')>
    AND option_language = '#arguments.language#'
   </cfif> 
   ORDER BY option_sort #arguments.ascdesc# 
 </cfquery>
 <cfreturn q_MILES>
</cffunction>

<cffunction name="f_WEIGHT" access="public" returntype="query">
 <cfargument name="ascdesc" required="no" default="desc">
 <cfargument name="attr_name" required="yes">
 <cfset var q_WEIGHT = "">
 <cfquery name="q_WEIGHT" datasource="#variables.dsn#" cachedWithin = "#CreateTimeSpan(365, 0, 0, 0)#">
   SELECT option_value, option_text
   FROM attributes_options
   WHERE attr_name = '#arguments.attr_name#'
   <cfif isdefined('arguments.language') and len('arguments.language')>
    AND option_language = '#arguments.language#'
   </cfif> 
   ORDER BY option_sort #arguments.ascdesc# 
 </cfquery>
 <cfreturn q_WEIGHT>
</cffunction>

<cfoutput>
<select>
 <cfloop query="q_miles">
  <option value="#option_value#">#option_text#</option>
 </cfloop>
</select>
</cfoutput>
......
......

2.Option:
<cfset q_attributes = objgetSelects.f_OPTIONS(ascdesc = 'ASC') />

<cffunction name="f_OPTIONS" access="public" returntype="query">
 <cfargument name="ascdesc" required="no" default="desc">
 <cfargument name="attr_name" required="yes">
 <cfset var q_OPTIONS = "">
 <cfquery name="q_OPTIONS" datasource="#variables.dsn#" cachedWithin = "#CreateTimeSpan(365, 0, 0, 0)#">
   SELECT *
   FROM attributes_options
 </cfquery>
 <cfreturn q_OPTIONS>
</cffunction>

<cfquery dbtype="query" name="q_miles">  
    SELECT option_value, option_text
    FROM q_OPTIONS
    WHERE attr_name = 'q_miles'
</cfquery>

<cfoutput>
<select>
 <cfloop query="q_miles">
  <option value="#option_value#">#option_text#</option>
 </cfloop>
</select>
</cfoutput>

Open in new window


Your opinion? What do you suggest? Any better idea?
Thank you in advance
LVL 2
PanosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
You definitely don't want option 2 - query-of-query is much slower than hitting the database again.
In option 1, it looks like your two functions are exactly the same.   Why have two functions?   I propose option 3 which is option 1 with just one function.   I have added cfqueryparam so you can bind your variables, that will speed things up because the database will not have to reinterpret the statement each time.

I am curious about the year long cache.    Do the table values never, ever change?   Even if the never change, I would think a 24 hour cache would be fine, just in case.

How big is this table (how many records?)    The best way improve performance is to ensure you have the right indexes on your table.    In your debug, how many mseconds does it take to run this query each time?

3. Option:
<cfset q_miles  = objgetSelects.f_getOptions(attr_name = 'MILES',ascdesc = 'ASC') />
<cfset q_weight = objgetSelects.f_getOptions(attr_name = 'WEIGHT',ascdesc = 'ASC') />

<cffunction name="f_getOptions" access="public" returntype="query">
 <cfargument name="ascdesc" required="no" default="desc">
 <cfargument name="attr_name" required="yes">
 <cfset var q_Option = "">
 <cfquery name="q_Option" datasource="#variables.dsn#" cachedWithin = "#CreateTimeSpan(365, 0, 0, 0)#">
    SELECT option_value, option_text
    FROM attributes_options
    WHERE attr_name = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.attr_name#">
   <cfif isdefined('arguments.language') and len('arguments.language')>
    AND option_language = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.language#"> 
   </cfif> 
   ORDER BY option_sort <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.ascdesc#"> 
 </cfquery>
 <cfreturn q_Option>
</cffunction>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PanosAuthor Commented:
Hi gdemaria.
Thank you for your post. Before your post i made some tests with the second option and it is really slower.
About your questions.
1. I have 2000 records in the table.
2. i'm using index for the attr_name and language.
3. I thougth i have to use separate functions for caching.

I did changed it now

Using :<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.ascdesc#">
i get error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ASC'' at line 4
0
gdemariaCommented:
You may not be able to use cfqueryparam in the order clause, just remove that.

2,000 records is not big, I would think that the index would be enough and your query time would be really low - did you check the time on the query?   Scroll down the page to see the query in the debug information, it will say the number of records fetched and the time it took
0
PanosAuthor Commented:
OK i removed that.
For all the queries i need 15 - 30 ms.

I was trying in this question to find out if it is better to hit more times the database or to handle it with a coldfusion query-of-query way.

Thank you gdemaria for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.