Coldfusion Query optimization
Hi everyone,
I try to find a solution to optimize a web page, Here is the code :
As you can see i loop over time to find an entry in the infos table then output the informations.
It works but when i do that over a week there is too many query.
If you have a solution to optimize that... thanks in advance.
Mike.
I try to find a solution to optimize a web page, Here is the code :
<cfset dtHour = CreateTimeSpan(0, 0,15, 0 ) />
<cfloop index="dtTime" from="07:00" to="21:00" step="#dtHour#">
Hour : #TimeFormat( dtTime, "HH:MM" )# (
<cfquery name="gInfos">
SELECT id,name,hour,day,infos
FROM Infos
Where jour = '#dateformat(now(), "dd/mm/yyyy")#' and heure = '#TimeFormat( dtTime, "HH:MM" )#'
</cfloop>
<cfoutput>#gInfos.name#<br />#gInfos.infos#</cfoutput>)<br>
</cfloop>As you can see i loop over time to find an entry in the infos table then output the informations.
It works but when i do that over a week there is too many query.
If you have a solution to optimize that... thanks in advance.
Mike.
_agx_
What's your db type and what are the data types of "jour" and "heure"?
ASKER
I use SQL SERVER 2008, the data type for jour is a datetime and heure is a varchar.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Omg just perfect ;) If you have a solution to do the same for a week ...
thanks in advance and thanks for the very quick solution.
thanks in advance and thanks for the very quick solution.
I'm not sure about same week. It's trickier because you'd need a cross join between the dates and times to display them all. I'd almost say use a calendar table. But I think you should open a separate question for that one. You might want to include the sql server zone too, because it's most likely going to be solved in sql, not CF.