Hi Scott,
If I may, your code could be improved performance-wise. It is a classic
example of Rbar programming (pronounced reebar = Row by agonizing row) as
the process is not written in a set-based way, which is exactly what DB
servers are really good (and really fast) at.
If you think about it, the code loops over each record in the first query
(the cfif is useless by the way) , and then does an update for each one. if
you have a thousand records, that's a thousand loops, a thousand queries
sent through the wire, a thousand query the optimizer has to parse, and so
on. What you really want to do is have a single update statement instead.
The problem in your case is that using aggregation forces you to use a group
by statement. On top of that you need the aggregated data to perform the set
operation which complicates the matter. While writing one single statement
is possible it's probably easier to read if you use a temp table to store
the maxed date and then write a single update. like so:
<cfquery>
SELECT sb_invoice.sb_invoice_id,
MAX(sb_claim_date) AS sb_claim_date
INTO ##tempTable
FROM sb_invoice
INNER JOIN sb_invoice_item ON sb_invoice.sb_invoice_id =
sb_invoice_item.sb_invoice_id
INNER JOIN sb_item_claim ON sb_invoice_item.sb_invoice_item_id =
sb_item_claim.sb_invoice_item_id
WHERE sb_invoice.sb_bill_mech_code IN ( 'M', 'F', 'AG', 'SC', 'MB', 'MO' )
AND sb_invoice_item.sb_item_status_code IN ( '21', '24', '31' )
GROUP BY sb_invoice.sb_invoice_id,
sb_invoice.sb_reminder_date
HAVING sb_invoice.sb_reminder_date < DATEADD(d, 30, MAX(sb_claim_date))
UPDATE i
SET sb_reminder_date = DATEADD(d, 31, t.sb_claim_date)
FROM sb_invoice i
INNER JOIN #tempTable t ON i.sb_invoice_id = t.sb_invoice_id
</cfquery>
The more records the initial select returns, the more resources you save. I
just tried a simple example on my local machine where I tried to update 10k
rows with both methods. the rbar one took about 10 seconds while the
set-based one took less than half a second. trying with 100k rows, I got 70
seconds against 2.5.
Let's keep in mind that lots of updates wouldn't need the temp table in the
first place. It's only there because because you were updating with an
aggregated value. Normally you'd just do:
UPDATE t
set blah = 6
FROM myTable t
JOIN otherTable o on o.id = t.fk
...
...
Anyway, speaking of agonizing stuff, I'm off to see the dentist.
Cheers,
Tof
On Fri, Oct 1, 2010 at 11:30 AM, Scott Thornton <
scott.thorn...@hnehealth.nsw.gov.au> wrote:
> Hello,
>
>
>
> Thanks for all replies.
>
>
>
> An update of my local dev server to 9,0,1,274733 appears to have solved
> the problem.
>
>
>
>
>
>
>
> *From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] *On
> Behalf Of *Scott Thornton
> *Sent:* Friday, 1 October 2010 10:29 AM
> *To:* cfaussie@googlegroups.com
> *Subject:* RE: [cfaussie] Strange problem - Element RECORDCOUNT is
> undefined in query
>
>
>
> HI All,
>
>
>
> 1. What happens if you try CFDUMP instead right after the query?
>
>
>
> using
>
> *<cfdump var="#fix_this#"><cfabort>*
>
> * *
>
> *returns *
>
>
>
> Variable FIX_THIS is undefined.
>
>
>
> 2. re recordcount gt 0.
>
>
>
> I didn't know that, thanks.
>
>
>
> 3. Why the cfoutput?
>
>
>
> The original code used cfloop, I tried using cfoutput as well. Typically
> cfloop would be used.
>
>
>
> 4. Update CF?
>
>
>
> Will try that now.
>
>
>
> 5. Does the query run (as is)?
>
>
>
> Yes it does, however it does not return any rows ( in SQl Query Analyzer
> 2000). The database is MS SQL 2005
>
>
>
> 5. Much simplier query?
>
>
>
> <cfquery datasource="#DSN#" name="fix_this">
>
> select top 1 * from sb_invoice
>
> </cfquery>
>
>
>
> <cfdump var="#fix_this#"><cfabort>
>
>
>
> works fine.
>
>
>
> Sooo, I will update my local server first, then put the orginal query back
> in and see how it goes.
>
>
>
> *From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] *On
> Behalf Of *...@ampersand.net.au
> *Sent:* Friday, 1 October 2010 9:41 AM
> *To:* cfaussie@googlegroups.com
> *Subject:* Re: [cfaussie] Strange problem - Element RECORDCOUNT is
> undefined in query
>
>
>
> What happens if you try CFDUMP instead right after the query?
>
> On 1/10/2010 9:35 AM, Scott Thornton wrote:
>
> Hi,
>
>
>
> Running across a odd problem in CF 9,0,0,251028. development server.
>
>
>
> I was just running a process that usually works and has not been modified
> for quite a long time. part of that process runs a query and then loops over
> the result set. Simple enough… however the query does not seem to exist
> after it has run.
>
>
> eg: The following code returns Element RECORDCOUNT is undefined in
> FIX_THIS.
>
>
>
>
>
> <cfquery datasource="#DSN#" name="fix_this">
>
> select sb_invoice.sb_invoice_id,
> sb_invoice.sb_reminder_date, max(sb_claim_date) as sb_claim_date
>
> from sb_invoice
>
> inner join sb_invoice_item
>
> on sb_invoice.sb_invoice_id = sb_invoice_item.sb_invoice_id
>
> inner join sb_item_claim
>
> on sb_invoice_item.sb_invoice_item_id =
> sb_item_claim.sb_invoice_item_id
>
> where sb_invoice.sb_bill_mech_code in
> ('M','F','AG','SC','MB','MO')
>
> and sb_invoice_item.sb_item_status_code in ('21','24','31')
>
> group by sb_invoice.sb_invoice_id,
> sb_invoice.sb_reminder_date
>
> having sb_invoice.sb_reminder_date <
> DateAdd(d,30,max(sb_claim_date))
>
> </cfquery>
>
>
>
> <cfif fix_this.recordcount gt 0>
>
>
>
> <cfoutput query="fix_this">
>
>
>
> <cfquery name="upd_fund_reminder"
> datasource="#DSN#">
>
> update sb_invoice
>
> set sb_reminder_date =
> <cfqueryparam cfsqltype="cf_sql_varchar"
> value="#DateFormat(DateAdd("d",31,fix_this.sb_claim_date),"dd-mmm-yyyy")#">
>
> where sb_invoice_id =
> <cfqueryparam cfsqltype="cf_sql_numeric" value="#fix_this.sb_invoice_id#">
>
> </cfquery>
>
>
>
> </cfoutput>
>
>
>
> </cfif>
>
>
>
> I have changed the name of the query to a few different values, I have used
> cfloop instead of cfoutput, I added the cfif.recordcount to see if that
> helped, to no avail. Without the CFIF the error was :
>
> *Attribute validation error for tag cfoutput. *
>
>
>
> The value of the attribute query, which is currently fix_this, is invalid.
>
>
>
>
>
> Debug output shows that the query executed in 922ms and returned no rows.
>
>
>
> What am I missing?
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "cfaussie" group.
> To post to this group, send email to cfaus...@googlegroups.com.
> To unsubscribe from this group, send email to
> cfaussie+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/cfaussie?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "cfaussie" group.
> To post to this group, send email to cfaus...@googlegroups.com.
> To unsubscribe from this group, send email to
> cfaussie+unsubscr...@googlegroups.com<cfaussie%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/cfaussie?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "cfaussie" group.
> To post to this group, send email to cfaus...@googlegroups.com.
> To unsubscribe from this group, send email to
> cfaussie+unsubscr...@googlegroups.com<cfaussie%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/cfaussie?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "cfaussie" group.
> To post to this group, send email to cfaus...@googlegroups.com.
> To unsubscribe from this group, send email to
> cfaussie+unsubscr...@googlegroups.com<cfaussie%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/cfaussie?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
