Ok - here's the final part to this issue. 1) The query IS inefficient, but it DOES work and is cached. Big deal. I will not be submitting a patch for this.
2) The problem is specifically related to ColdFusion 8 and cfquery with cachedWithin. See this post on the Adobe forums: http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=3&threadid=1289675&enterthread=y The problem appears to be a bug in CF 8 and is slated to be fixed in updater 2, which is due "very soon". Doug On Oct 25, 1:02 pm, Doug Hughes <[EMAIL PROTECTED]> wrote: > On further review, the temp table in there is 100% unnecessary. I'll > submit a patch to the bug tracker. > > Doug > > On Oct 25, 12:51 pm, Doug Hughes <[EMAIL PROTECTED]> wrote: > > > Well, this is a message being thrown from the framework, but I see > > your point. I wonder if this could be the result of a race condition > > or something? > > > The code in question (which is part of FC) DOES create a temp table, > > insert data, and then select from that. That explains why the > > problem goes away with a CF restart - the table is no longer > > associated with the connection. > > > I'll see if I can tweak this to fix the problem. Heck maybe a SQL > > service pack would do the trick. > > > Doug > > > On Oct 25, 12:08 pm, "Stephen Moretti" <[EMAIL PROTECTED]> > > wrote: > > > > On 25/10/2007, Doug Hughes <[EMAIL PROTECTED]> wrote: > > > > > A quick question for the great minds here at farcry-dev.... Has > > > > anyone seen this error before? Does anyone know what the cause might > > > > be? We get it about once a week and it will not go away (where ever > > > > it crops up) until we restart CF. > > > > > I can't say that the error always occurs on line 257 of > > > > formtools.cfc. But I do see it enough to be driven insane. :) > > > > Its probably happening whereever there is a query that needs to get the > > > top > > > x from an offset point.... > > > > Not wanting to rub it in or anything... I've often looked at that > > > nasty nasty bit of SQL and wondered whether that could be done > > > better/easier/more efficiently, but given that I don't have to use MS > > > SQL and mysql, postgres and orible all have this capability I've not lost > > > any sleep over it... > > > > So anyway, my guess is that there is something funky going on > > > with temporary tables on your DB server.... > > > > I was just talking to my SQL Server DBA collegue and he's given me these > > > two > > > alternates..... > > > > Option 1 > > > ======= > > > with myProject(id, ref, recvDate) as > > > (select p_id, p_ref, p_recvDate from project > > > ) > > > select x.*, y.* from (select top 20 * from myProject order by id desc) as > > > x > > > left join (select top 10 * from myProject order by id desc) as y on x.id = > > > y.id > > > where y.id is null > > > > Option 2 > > > ====== > > > select top 10 * from (select top 100 * from (select p_id, p_ref, > > > p_recvDate > > > from project) as x order by p_id desc) as x order by p_id asc > > > > Options 2 is apparently the most efficient. > > > > Just a thought... > > > > Regards > > > > Stephen --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "farcry-dev" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/farcry-dev?hl=en -~----------~----~----~----~------~----~------~--~---
