Panos
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:
Your opinion? What do you suggest? Any better idea?
Thank you in advance
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>
Your opinion? What do you suggest? Any better idea?
Thank you in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
ASKER
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"
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