Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Coldfusion Query optimization

Posted on 2012-03-22
5
Medium Priority
?
439 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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
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…

704 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