You learn something new every day - awesome, Thanks Chris!
As always, please contact me if I can be of any futher assistance. Gavin "Beau" Baumanis Senior Application Developer PalCare Pty. Ltd. P: +61 -3 9380 3513 M: +61 -438 545 586 E: b...@palcare.com.au W: http://palcare.com.au On 04/10/2010, at 4:22 PM, christophe albrech wrote: > 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 m...@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. > 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. > > > -- > 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. > 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.
