ColdFusion

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsWeb Design & DevelopmentColdFusion

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old August 14th, 2008, 05:08 PM
ipix ipix is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Aug 2008
Posts: 1 ipix User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 25 m 24 sec
Reputation Power: 0
Loop performance issues - Please Help!

CF Code gurus! I need some advice!

I've built an app that has now grown larger than originally expected. It uses nested loops and the performance is really starting to suffer. Pages with the nested loop queries take around 16 seconds to load.

I have attached the code to see if anyone has any ideas on how to make it more efficient. I"ve tried caching the queries, but it makes no diffeence since the results are being formatted after the query is run.

If anyone can help I would totally appreciate! Lots on my plate right now. cheers.

Code:
**************************************

<cfquery name="GetApps" datasource="#DBSrc#" username="#DBUser#" password="#DBPass#">
SELECT *
FROM PRApps 
ORDER BY ID ASC

</cfquery>

<cfquery name="GetReviews" datasource="#DBSrc#" username="#DBUser#" password="#DBPass#">
SELECT *
FROM PRReviews
</cfquery>

<cfquery name="GetAlignments" datasource="#DBSrc#" username="#DBUser#" password="#DBPass#">
SELECT *
FROM PRAlignments
</cfquery>

<cfquery name="GetUsers" datasource="#DBSrc#" username="#DBUser#" password="#DBPass#">
SELECT *
FROM PRUsers
</cfquery>

<p class="PRTitle">APPLICATION LIST</p>

<p>
The application list is where you can view the status on your current applications and their reviews.
</p>

<p>
<b>In Progress -</b> This list provides you with which applications are currently being reviewed.
It also allows you to see each reviewers status on their assessments.
</p>

<p>
<b>Completed -</b> This list shows which applications had their status changed to complete by 
an administrator. Once an application has it status changed to completed, reviewers will not 
be able to submit their reviews if they are not completed.
</p>

<p class="PRTitle">IN PROGRESS</p>

<!--- ADMIN IN PROGRESS LIST --->
<cfif COOKIE.Role EQ 'admin'>
	<cfloop from="1" to="#GetApps.RecordCount#" index="i">
		<cfif #GetApps.Status[i]# EQ 'IN PROGRESS' AND #GetApps.Organization[i]# EQ '#COOKIE.Organization#'>
		<table class="tblAppList" cellpadding="0" cellspacing="0">
		<tr>
			<td class="tdAppId" colspan="2">
			<cfoutput><b>#GetApps.AppName[i]#</b> by #GetApps.Leader[i]#</cfoutput><br>
			<cfoutput><a href="app/#GetApps.ID[i]#.pdf" target="_blank"><img src="img/icon_pdf.gif" alt="View PDF of application #GetApps.ID[i]#"> #GetApps.ID[i]#.pdf</a></cfoutput>
			<cfdirectory action="list" directory="#Directory#" name="GetFileSize" sort="Name" filter="#GetApps.ID[i]#.pdf">
			<cfoutput query="GetFileSize"><span class="PRFileSize">#Ceiling(size/1000000)# Mbs</span></cfoutput>
			
			</td>
		</tr><tr>
			<cfoutput>
			<td class="tdAppForm">Program: #ListFirst(GetApps.FormURL[i],'.')#</td>
			<td class="tdAppBtn"><a class="PRBtn" href="app_edit.cfm?ID=#GetApps.ID[i]#">Edit</a></td>
			</cfoutput>
		</tr><tr>
			
			<td>
			<b>Reviews:</b><br>
			
			<cfoutput>
			
			<cfloop from="1" to="#GetUsers.RecordCount#" index="j">	
			
					<cfif ListFind(#GetApps.Users[i]#,"#GetUsers.ID[j]#")>
					<cfquery name="SearchReviews" datasource="#DBSrc#" username="#DBUser#" password="#DBPass#">
					SELECT *
					FROM PRReviews
					WHERE UserID = '#GetUsers.ID[j]#' AND ApplicationID ='#GetApps.ID[i]#'
					</cfquery>
					
					
					<cfif #SearchReviews.RecordCount# EQ 0 AND #GetUsers.Status[j]# EQ 'ACTIVE'>
					<cfoutput>
						<img src="img/icon_yellow.gif" alt="Not started">&nbsp;Not Started by: #GetUsers.FirstName[j]# #GetUsers.LastName[j]#
						<br>
																						
					</cfoutput>
					</cfif>
					
					<cfif #GetUsers.Status[j]# EQ 'PREASSIGNED'>
							<img src="img/icon_red.gif" alt="Waiting For User Activation">
							<cfoutput>Waiting for activation: #GetUsers.FirstName[j]# #GetUsers.LastName[j]#</cfoutput>
						<br>
						</cfif>
						
						<cfif #GetUsers.Status[j]# EQ 'NOT ACTIVE'>
							<img src="img/icon_gray.gif" alt="User Is Archived">&nbsp;
							User is Archived: 
						</cfif>
						
				    </cfif>
							
			<cfloop from ="1" to="#GetReviews.RecordCount#" index="p">
								
						
					<cfif #GetReviews.UserID[p]# EQ #GetUsers.ID[j]# AND #GetReviews.ApplicationID[p]# EQ #GetApps.ID[i]#>
											
						
						
						<!-- Statements for ACTIVE conditions --->
						
						<!-- ACTIVE and IN PROGRESS --->
											
						<cfif #GetUsers.Status[j]# EQ 'ACTIVE' AND #GetReviews.Status[p]# EQ 'IN PROGRESS'>
							<img src="img/icon_yellow.gif" alt="In Progress">&nbsp;<a href="app_review.cfm?ID=#GetReviews.ID[p]#&ApplicationID=#GetReviews.ApplicationID[p]#"><img src="img/icon_mag.gif" alt="Preview"></a> 
							#GetReviews.ID[p]#&nbsp;In Progress by 
						</cfif>	
						
						<!-- ACTIVE and COMPLETED --->
																
						
						<cfif #GetUsers.Status[j]# EQ 'ACTIVE' AND #GetReviews.Status[p]# EQ 'COMPLETED'>
							<img src="img/icon_green.gif" alt="Review Completed">
							
							<a href="app_review.cfm?ID=#GetReviews.ID[p]#&ApplicationID=#GetReviews.ApplicationID[p]#"><img src="img/icon_mag.gif" alt="Preview"></a>
							
							<a href="review_xls.cfm?ID=#GetReviews.ID[p]#"><img src="img/icon_xls.gif" alt="Download XLS"> #GetReviews.ID[p]#</a> 
							
						</cfif>
						
						
																																									
						#GetUsers.FirstName[j]# #GetUsers.LastName[j]#
						<br>
						
					</cfif>
								
			</cfloop>
		
			</cfloop>
			</cfoutput>
			
			</td>
			<cfoutput>
			<td width="50%">
			<b>Alignment Reviews By:</b><br>
			
			<cfloop from="1" to="#GetUsers.RecordCount#" index="j">
				<cfif ListFind(#GetApps.AlignUsers[i]#,"#GetUsers.ID[j]#")>
					<cfquery name="SearchAReviews" datasource="#DBSrc#" username="#DBUser#" password="#DBPass#">
					SELECT *
					FROM PRAlignments
					WHERE ApplicationID = '#GetApps.ID[i]#'
					AND UserID = '#GetUsers.ID[j]#'
					</cfquery>
					
					<cfif #SearchAReviews.RecordCount# EQ 0>
						&##8226; Not Started by #GetUsers.FirstName[j]# #GetUsers.LastName[j]#
						<br>
					</cfif>
					
					<cfif #SearchAReviews.RecordCount# EQ 1>
						&##8226; <a href="review_align.cfm?ID=#SearchAReviews.ID#">#GetUsers.FirstName[j]# #GetUsers.LastName[j]#</a>
						<br>
					</cfif>
				</cfif>
			</cfloop>
			</td>
			</cfoutput>
		</tr>
		</table><br>
		</cfif>
	</cfloop>
</cfif>
************************************




Any advice is appreciated.

thanks!

Reply With Quote
  #2  
Old February 22nd, 2012, 09:37 AM
jill8beans2 jill8beans2 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2008
Location: TN
Posts: 13 jill8beans2 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 43 m 32 sec
Reputation Power: 0
help!

I am having the same issue! Does anyone know what to do with this?

Reply With Quote
Reply

Viewing: Tutorialized ForumsWeb Design & DevelopmentColdFusion > Loop performance issues - Please Help!


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap