Solved

Coldfusion Query optimization

Posted on 2012-03-22
5
433 Views
Last Modified: 2013-11-19
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.
0
Comment
Question by:quarkmike
  • 3
  • 2
5 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 37752569
What's your db type and what are the data types of "jour" and "heure"?
0
 

Author Comment

by:quarkmike
ID: 37752578
I use SQL SERVER 2008, the data type for jour is a datetime and heure is a varchar.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37752710
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
 

Author Comment

by:quarkmike
ID: 37752980
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
 
LVL 52

Expert Comment

by:_agx_
ID: 37753068
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now