Coldfusion Query optimization

Hi everyone,

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>

Open in new window


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.
quarkmikeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

_agx_Commented:
What's your db type and what are the data types of "jour" and "heure"?
0
quarkmikeAuthor Commented:
I use SQL SERVER 2008, the data type for jour is a datetime and heure is a varchar.
0
_agx_Commented:
Do you need to display all times, even if there's no matching record? If no, then it's very simple. Just query the table once for records with todays date and a time between 7AM and 9PM.  Then output the results.  Otherwise, see below

> heure is a varchar.

Truthfully it'd should be some sort of date/time so you can use date functions without worrying about conversion problems.  But it might still work if the values are always in HH:MM format (ie have leading 0's)

I'd pull everything in one query using a CTE like described here.  Something like this

<!--- the CTE generates all the times between 7-9AM on the fly
       and uses an OUTER join so the times are displayed, even if
       there's no matching record found in "info" table
--->
WITH times
AS
(
  SELECT convert(datetime, '07:00:00', 114) AS theTime
  UNION ALL
  SELECT DATEADD(n, 15, theTime)
  FROM times
  WHERE DATEADD(n, 15, theTime) <= convert(datetime,'21:00:00', 114)
)
SELECT t.theTime, i.id, i.name, i.hour, i.day, i.infos
FROM   times t
            LEFT JOIN @infos i ON i.heure = t.theTime AND i.jour = #createODBCDate(now())#
ORDER BY t.theTime
OPTION (MAXRECURSION 0);
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
quarkmikeAuthor Commented:
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.
0
_agx_Commented:
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.
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
Web Development

From novice to tech pro — start learning today.