Not yet! But I shall, once I've checked a couple of other things out. Just tried the IN() with 7000+ on a.n.others server, without problems - admittedly with the #CreateODBCDate(Now())# replaced with my todays_date variable.
As I initially had that #CreateODBCDate(Now())# in the 'IN()' query on the live server, and seeing it took 11 minutes to process on my (admittedly slow and memory-starved) machine, I'm wondering if, even if the live server took half that time, it may have crapped out before completing. I'll check back when I have the/a solution. Cheers Terry ----------Original Message--------- > Have u tried putting a try/catch block around the query to see if it > errors > doing the IN statement? > > > -----Original Message----- > > From: Terry Riley [mailto:[EMAIL PROTECTED] > > Sent: 23 April 2004 14:40 > > To: [EMAIL PROTECTED] > > Subject: Re: [ cf-dev ] ValueList problem? > > > > > > To Hell with it! > > > > Just in case there is some limitation, somewhere, on the > > length of the > > IN() list, I've decided to go the other route: > > > > <cfset todays_date = #CreateODBCDate(Now())#> > > > > <cftransaction> > > <cfloop list="#user_update_list#" index="i"> > > <cfquery name="foo_update" datasource="#request.DSN#"> > > UPDATE userlist > > SET update_request = #todays_date# > > WHERE UID = #i# > > </cfquery> > > </cfloop> > > </cftransaction> > > > > which actually seems to be only half as fast as doing it in > > one hit using > > the IN(#user_update_list#) construct! And that despite 8000+ db hits > > instead of the one? > > > > > > Anyone see any real problems with this? > > > > > > As an aside - in testing the various options, I found that : > > > > <cfquery name="foo_update" datasource="#request.DSN#"> > > UPDATE userlist > > SET update_request = #CreateODBCDate(Now())# > > WHERE UID IN (#user_update_list#) > > </cfquery> > > > > took about 11 *minutes* to complete. Whereas: > > > > <cfset todays_date = #CreateODBCDate(Now())#> > > <cfquery name="foo_update" datasource="#request.DSN#"> > > UPDATE userlist > > SET update_request = #todays_date# > > WHERE UID IN (#user_update_list#) > > </cfquery> > > > > took only 19 *seconds*. Beware, therefore, using > > #CreateODBCDate()# within > > such an update - evidently, the #CreateODBCDate# is processed > > separately > > for every row updated. Can't think of any other explanation..... > > > > Cheers > > Terry > > > > ----------Original Message--------- > > > > > i don't think there's a problem with the size of a ValueList, but > > > there > > > might be an issue on how many values you put into an IN ( ) > > clause in > > > SQL. > > > > > > > > > > > > > > > > > > > > > [EMAIL PROTECTED] (Terry Riley) > > > 23/04/2004 11:28 > > > Please respond to dev > > > > > > > > > To: [EMAIL PROTECTED] > > > cc: > > > Subject: Re: [ cf-dev ] ValueList problem? > > > > > > Unfortunately, Duncan, I don't have (easy) access (someone > > else's live > > > server), but the original failure seems to have happened at > > end March, > > > so doubt if they had data that far back, even if I could! > > > > > > I'll ask, though... > > > > > > Cheers > > > Terry > > > > > > --------Original Message--------- > > > > > > > any errors reported in application.log? > > > > > > > > > > > > > > > > > > > > > > > > > > > > [EMAIL PROTECTED] (Terry Riley) > > > > 23/04/2004 10:58 > > > > Please respond to dev > > > > > > > > > > > > To: [EMAIL PROTECTED] > > > > cc: > > > > Subject: [ cf-dev ] ValueList problem? > > > > > > > > In one of our apps (CF5), we send out a bulk email (well, > > about 8000 > > > > max) once in a while to encourage subscribers (job > > seekers) to either > > > > update their subscription preferences or remove > > themselves from the > > > > mailing list. > > > > > > > > At some future point, we check who has bothered to update, and if > > > > they haven't within a certain period, we assume > > non-interest (or even > > > > non-delivery of the email) and delete them automagically using a > > > > batch program. > > > > > > > > Part of the original mailing code is to create a valuelist of IDs > > > > for > > > > those requiring to be mailed, which, after CFMAIL has > > done its bit, > > > > is used to update a datetime field (SQL2K) with the day's > > date (see > > > > below). > > > > > > > > On our development server (XP) it works as advertised, even with a > > > > valuelist containing 8000+ 4-digit IDs. However, on the > > live server > > > > (NT), this part of the code does not appear to have executed, > > > > although the 8000 emails have been sent. > > > > > > > > Is there a limit to the size of a ValueList()? If not, anyone have > > > > any possible explanation as to why this doesn't work? > > > > > > > > > > > > <!--- initial query ---> > > > > > > > > <cfset user_update_list = #ValueList(get_users.UID)#> > > > > > > > > <!--- cfmail stuff ---> > > > > > > > > <cfquery name="foo_update" datasource="#request.DSN#"> > > > > UPDATE userlist > > > > SET update_request = #CreateODBCDate(Now())# > > > > WHERE UID IN (#user_update_list#) </cfquery> > > > > > > > > > > > > Cheers > > > > Terry Riley -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED]
