Query Slowing

Report · Mar 17, 2010

Hello,

I have a query that has begun to slow overtime and would like to see if there is anything that can be done as far as cleaner code that could speed things back up. This query populates an html crgrid. This is a MySQL DB. TIA

QUERY:

<cfquery name="getRecordcount" datasource="#dsn#">
select count(editbcastreq.bcast_id) as editcount
from bcastreq
left join editbcastreq
on bcastreq.bcast_id = editbcastreq.bcast_id
where bcastreq.bcast_id = #url.cfgridkey#
group by bcastreq.bcast_id
</cfquery>
<cfset editcounts = '#getRecordcount.editcount#'>

<cfif #editcounts# is 0>
<cfquery name="getLastedit" datasource="#dsn#">
select *
from bcastreq
inner join affiliate
on bcastreq.affiliate_id = affiliate.affiliate_id
inner join affiliatetype
on affiliatetype.affiliatetype_id = affiliate.affiliatetype_id
where bcastreq.bcast_id = #url.cfgridkey#
</cfquery>
<cfelseif #editcounts# gte 1>
<cfquery name="getLastedit" datasource="#dsn#">
select *
from editbcastreq
inner join affiliate
on editbcastreq.affiliate_id = affiliate.affiliate_id
inner join affiliatetype
on affiliatetype.affiliatetype_id = affiliate.affiliatetype_id
where editbcastreq.bcast_id = #url.cfgridkey#
order by edit_id desc

</cfquery>
</cfif>

CFGRID:

<cfoutput>
<cfform>
<table border="1" width="50%">
<tr>
<td>
Filter By: <cfselect id="filtercolumn" name="filtercolumn" bind="cfc:alumforms.broadcast.cfc.Users.getUserColumns()"
display="ColumnName" value="ColumnName" bindOnLoad="true" />
Filter Text: <cfinput type="text" id="filter" name="filter" >
<cfinput type="button" name="filterbutton" value="Filter" id="filterbutton" onclick="ColdFusion.Grid.refresh('usersgrid',false)">
</td>
</tr>
<tr>
<td id="gridtd" style="padding-top:10px;">
<div style="min-height:200px;">
<cfgrid name="usersgrid" pagesize="20" format="html" width="625" height="450"
bind="cfc:alumforms.broadcast.cfc.Users.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filtercolumn},{filter})" href="editreq.cfm" hrefkey="bcast_id" gridlines="yes" striperows="yes" >

<cfgridcolumn name="bcast_id" header="Broadcast ID" display="no" >
<cfgridcolumn name="affiliatetype_id" header="Affiliate Type ID" display="no">
<cfgridcolumn name="affiliate_id" header="Affiliate ID" display="no">
<cfgridcolumn name="affiliatename" header="Affiliate Name" width="150">
<cfgridcolumn name="subject" header="Subject" width="350">
<cfgridcolumn name="editcount" header="No. Edits" width="50" >
<cfgridcolumn name="reqtimestamp" header="Submit Date" width="75" >

</cfgrid>
</div>
</td>
</tr>
<tr>
</tr>
</table>
</cfform>
</cfoutput>

Report · Mar 17, 2010

Your first query does not need a group by clause. In fact, I'm not even sure you need the first query at all. With that left join, you are simply selecting the number of records in the bcastreq table that match the url.gridkey. Depending on your intent, either changing the left join to an inner join, or simply selecting from one table will get you started.