asked on

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

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Panos

ASKER

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

gdemaria

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

Panos

ASKER

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.