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]
