I've got a main <cfloop> looping through a set of records - say 15 at a time - and within that loop I've got 3 SQL queries performing a count on how many associated records there are for that particular record in the main loop. Kind of like a forum index page to give how many threads, posts etc there are for each forum.
i.e. (rough code to give you an idea of what I mean):
<cfquery name="mainquery">
select * from tablemain
</cfquery>
<cfloop query="mainquery" startrow="1" endrow="15">
<td>#SectionName#</td>
<cfquery name="countquery1">
select count(case_id) As ThisCount1 from table1 where x = '#mainquery.SectionName#'
</cfquery>
<td>#countquery1.ThisCount1#</td>
<cfquery name="countquery2">
select count(case_id) As ThisCount2 from table2 where x = '#mainquery.SectionName#'
</cfquery>
<td>#countquery2.ThisCount2#</td>
<cfquery name="countquery3">
select count(case_id) As ThisCount3 from table3 where x = '#mainquery.SectionName#'
</cfquery>
<td>#countquery3.ThisCount3#</td>
</cfloop>
My question is, is there a more efficient method for doing this kind of thing? As the tables I am accessing have 1,000s of rows - possibly millions - the page takes a significant amount of time finish processing. I could remove the record counts altogether, but it's preferable for it to remain.
Cheers,
Rob
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
