Solved

Coldfusion Query optimization

Posted on 2012-03-22
5
438 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

617 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