Improve company productivity with a Business Account.Sign Up

x
?
Solved

Coldfusion Query optimization

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

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 53

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 53

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Over time, the online landscape has altered considerably, but that’s nothing compared to the up-and-coming trends that will shape the web design industry in the coming year. Keep reading to find out which trends will shape B2B web design in 2018.
Although a new technology, ReactJs offers multiple benefits to the website owners when it comes to creating interactive websites. Know what aspects make React Js one of the most popular frameworks for building websites.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. 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.: (CODE)
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

606 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