INSERT INTO email_spool (recipient, sender, cc, bcc, replyto, subject, port, mailer_id, timeout, spool_enable, text_message, html_message, send_date) VALUES (, , , , , , , , , , , , ) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => hans [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 5 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> DELETE FROM email_spool SELECT MAX(t1.UserID) AS userid, MAX(t1.Username) AS username, MAX(t1.FirstName) AS firstname, MAX(t1.LastName) AS lastname, t1.EmailAddress AS emailaddress, MAX(t1.RegDate) AS regdate FROM Users t1 LEFT OUTER JOIN do_not_email t2 ON t1.EmailAddress = t2.email_address LEFT OUTER JOIN email_spool t3 ON t1.EmailAddress = t3.recipient WHERE t1.SendMarketingEmail = AND t1.isBadEmail = AND ( t1.isowner = OR t1.isbidder = OR (t1.isowner = AND t1.isbidder = ) OR AND t1.regdate <= AND t1.regdate >= 1=2) AND t2.email_address IS NULL AND (t3.recipient IS NULL) GROUP BY t1.EmailAddress #user_array[variables.array_index][2]# #user_array[variables.array_index][3]#, #attributes.text_message# About this email: You are receiving this email because you are a registered user of SITE. To change your communication preferences, please login to your account by clicking "My Account" on SITE.com and navigating to "My Information". You registered with us on #DateFormat(user_array[variables.array_index][5],"m/d/yyyy")# with the username #user_array[variables.array_index][6]#. Please note that this email was sent from an unattended mail box, and any replies will not be answered. If you need to get in touch with a SITE representative, please visit SITE.com and click "Contact Us". #user_array[variables.array_index][2]# #user_array[variables.array_index][3]#,

#attributes.html_message#



You are receiving this email because you are a registered user of SITE.
To change your communication preferences, please login to your account by clicking "My Account" on SITE.com and navigating to "My Information"
You registered with us on #DateFormat(user_array[variables.array_index][5],"m/d/yyyy")# with the username #user_array[variables.array_index][6]#.
Please note that this email was sent from an unattended mail box, and any replies will not be answered.
If you need to get in touch with a SITE representative, click here.
#user_array[variables.array_index][2]# #user_array[variables.array_index][3]#, #attributes.text_message# About this email: You are receiving this email because you are a registered user of SITE. To change your communication preferences, please login to your account by clicking "My Account" on SITE.com and navigating to "My Information" You registered with us on #DateFormat(user_array[variables.array_index][5],"m/d/yyyy")# with the username #user_array[variables.array_index][6]#. Please note that this email was sent from an unattended mail box, and any replies will not be answered. If you need to get in touch with a SITE representative, please visit SITE.com and click "Contact Us".
SELECT t1.EmailAddress AS emailaddress, MAX(t1.NotificationID) AS notificationid, MAX(t1.uuid) AS uuid, MAX(t1.Added) AS added FROM NewAuctionNotification t1 LEFT OUTER JOIN do_not_email t2 ON t1.EmailAddress = t2.email_address LEFT OUTER JOIN email_spool t3 ON t1.EmailAddress = t3.recipient WHERE t1.SendMarketingEmail = AND t2.email_address IS NULL AND t3.recipient IS NULL AND t1.isactive [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => hans [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 9 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Obviously pick a time based on how long an individual database request should take. [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Ian [ip] => ian.skinner@blo [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 12 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Possible cfquery bug? - Coldfusion Database Access

Possible cfquery bug? - Coldfusion Database Access

This really sounds like a bug, can some take a look? We have a script that loops over several tables and aggregates them down to one table. It handles ~35K records. On each iteration of the loop, it performs an insert. This works fine until around 20K iterations. ColdFusion bogs down. Threads start piling up in cfstat, and eventually everything comes to a halt. DB/Sec gradually goes to 0 and running requests climbs. Processor usage stays relatively low. The server has to be restarted by killing jrun as the service just hangs. I've isolated it down to the cfquery statement ...

  1. #1

    Default Possible cfquery bug?

    This really sounds like a bug, can some take a look?

    We have a script that loops over several tables and aggregates them down to
    one table. It handles ~35K records. On each iteration of the loop, it performs
    an insert.

    This works fine until around 20K iterations. ColdFusion bogs down. Threads
    start piling up in cfstat, and eventually everything comes to a halt. DB/Sec
    gradually goes to 0 and running requests climbs. Processor usage stays
    relatively low. The server has to be restarted by killing jrun as the service
    just hangs. I've isolated it down to the cfquery statement that performs the
    insert. If I take that out, everything runs fine.

    Here's what I've tried already:

    Using a cfstoredproc instead of cfquery for the insert

    Creating a separate dsn for this insert

    Separating the client variables dsn from the regular dsn

    Applying all hotfixes for CF7

    Updated to the 3.5 DataDirect drivers

    Updated to SDK 1.4.2_14

    Created an ODBC dsn instead of SQL Server

    I've dumped the threads during the hang. It seems that everything starts
    hanging on cfquery tags. However, I also try to run a page with NO cfquery tags
    (not even a cfapplication tag), and that hangs as well. During the beginning of
    the hang, there around 4 threads being used in cfstat. Eventually that will
    climb to over 20, but initially there are still threads available that CF isn't
    allocating.

    Also, I tried running queries in Query yzer during the hang, and those run
    fine. So I don't think SQL Server is causing the issue, as it is still
    responsive.

    Environment:
    CF7 fully patched
    SQL Server 2000
    Win2K server

    I can post the code if necessary, but it is essentially looping over an array
    and calling a custom tag with cfmodule. That custom tag performs the insert. If
    I remove that insert query, everything works with no problem. There is no
    special processing in the query other than cfqueryparam tags.

    Any insight is greatly appreciated.

    hans Guest

  2. #2

    Default Re: Possible cfquery bug?

    Interestingly, today I noticed the problem starts happening right at the 10 minute mark...
    hans Guest

  3. #3

    Default Re: Possible cfquery bug?

    hans blix wrote: 
     

    Please do.

    Jochem

    --
    Jochem van Dieten
    Adobe Community Expert for ColdFusion
    Jochem Guest

  4. #4

    Default Re: Possible cfquery bug?

    maybe something like this will work better

    insert into sometable
    (fields)
    <cfloop>
    select distinct values from some_small_table
    <cfif loop not done>
    union
    closing tags
    Dan Guest

  5. #5

    Default Re: Possible cfquery bug?

    The code for the cfmodule is attached. This is the tag that is looped over and
    called 30K + times. If I comment out the cfquery, the problem goes away. The
    cfstoredproc tag I tried is in there as well and is commented out.


    <cfsilent>
    <!----------------------------------
    Description: Custom tag for spooling mail to the database before sending.
    Created: 6/11/2003
    ----------------------------------->
    <!--- Default vars --->
    <cfparam name="attributes.to" default="">
    <cfparam name="attributes.from"
    default="#application.email_campaign_email_address #">
    <cfparam name="attributes.replyto"
    default="#application.email_campaign_reply_email_a ddress#">
    <cfparam name="attributes.cc" default="">
    <cfparam name="attributes.bcc" default="">
    <cfparam name="attributes.subject" default="">
    <cfparam name="attributes.port" default="25">
    <cfparam name="attributes.mailer_id" default="">
    <cfparam name="attributes.timeout" default="7">
    <cfparam name="attributes.spool_enable" default=true>
    <cfparam name="attributes.send_date" default="">
    <cfparam name="attributes.text_message" default="">
    <cfparam name="attributes.html_message" default="">

    <!--- Perform validation --->
    <cfif NOT Compare(attributes.to,"")>
    <cfthrow message="act_mail requires a TO address.">
    </cfif>
    <cfif NOT Compare(attributes.text_message,"") and NOT
    Compare(attributes.html_message,"")>
    <!--- No message was specified --->
    <cfthrow message="Please specify a message to be sent.">
    <cfelseif Compare(attributes.html_message,"") and NOT
    Compare(attributes.text_message,"")>
    <!--- HTML was sent but not text --->
    <cfthrow message="Please specify the text version of this message.">
    </cfif>
    <!--- Perform the insert --->
    <!--- <cfstoredproc username="#application.q_user#"
    password="#application.q_pass#" procedure="spInsEmailSpool"
    datasource="#application.datasourceBackup#">
    <cfprocparam type="In" variable="recipient" value="#attributes.to#"
    cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="sender" value="#attributes.from#"
    cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="cc" value="#attributes.cc#"
    cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="bcc" value="#attributes.bcc#"
    cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="replyTo" value="#attributes.replyTo#"
    cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="subject" value="#attributes.subject#"
    cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="port" value="#attributes.port#"
    cfsqltype="CF_SQL_NUMERIC">
    <cfprocparam type="In" variable="mailerID" value="#attributes.mailer_ID#"
    cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="timeout" value="#attributes.timeout#"
    cfsqltype="CF_SQL_NUMERIC">
    <cfprocparam type="In" variable="spoolEnable"
    value="#attributes.spool_enable#" cfsqltype="CF_SQL_BIT">
    <cfprocparam type="In" variable="textMessage"
    value="#attributes.text_message#" cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="htmlMessage"
    value="#attributes.html_message#" cfsqltype="CF_SQL_VARCHAR">
    <cfprocparam type="In" variable="sendDate" value="#attributes.send_date#"
    cfsqltype="CF_SQL_TIMESTAMP" null="#NOT(Compare(attributes.send_date,""))#">
    </cfstoredproc> --->
    <cfquery username="#application.q_user#" password="#application.q_pass#"
    name="query_insert_mail" datasource="#application.datasourceBatch#">
    INSERT INTO email_spool (recipient,
    sender,
    cc,
    bcc,
    replyto,
    subject,
    port,
    mailer_id,
    timeout,
    spool_enable,
    text_message,
    html_message,
    send_date)
    VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
    value="#attributes.to#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#attributes.from#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#attributes.cc#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#attributes.bcc#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#attributes.replyto#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#attributes.subject#">,
    <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#attributes.port#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
    value="#attributes.mailer_id#">,
    <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#attributes.timeout#">,
    <cfqueryparam cfsqltype="CF_SQL_BIT"
    value="#attributes.spool_enable#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
    value="#attributes.text_message#">,
    <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
    value="#attributes.html_message#">,
    <cfif compare(attributes.send_date,"")>
    <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP"
    value="#attributes.send_date#">
    <cfelse>
    <cfqueryparam null="Yes">
    </cfif>
    )
    </cfquery>
    </cfsilent>

    hans Guest

  6. #6

    Default Re: Possible cfquery bug?

    Also, something else I noticed that was interesting today.

    The problems start happening at exactly 10 minutes into the execution of the
    task. Could Windows be closing TCP sessions or something similar?

    hans Guest

  7. #7

    Default Re: Possible cfquery bug?

    hans blix wrote: 

    And the calling code? For instance, are you running this in a
    transaction? Do you flush the buffer in between?

    Jochem

    --
    Jochem van Dieten
    Adobe Community Expert for ColdFusion
    Jochem Guest

  8. #8

    Default Re: Possible cfquery bug?

    > Also, something else I noticed that was interesting today. 

    What's your request and session time outs?

    Is there any way of doing a bulk insert rather than using a 20000-iteration
    CF loop to do all this data loading? I'm not sure CF is the right tool for
    the job, here.

    What is the source of the loop (ie: what are you looping over which has
    20000 elements).

    --
    Adam
    Adam Guest

  9. #9

    Default Re: Possible cfquery bug?

    The calling code is attached. It builds arrays off of a couple tables, then
    loops over those arrays populating the email_spool table with the custom tag
    from above. It isn't being called within a cftransaction, and I'm not calling
    cfflush.

    Note that URL and form vars are being converted to the attributes scope in
    application.cfm.

    The request timeout is set to 30 seconds in the admin, but is increased to
    5000 seconds with the cfsetting tag in the attached code. Session vars timout
    in 3 days, Application vars in 5 days.

    20K records is big, but it isn't huge. Please let me know if there is an issue
    with my code. I've been thru it countless times and nothing stands out as a
    problem.

    <cfsilent>
    <cftry>
    <!----------------------------------
    Description: Action script for creating a user email campaign
    ----------------------------------->
    <!--- Set timeout --->
    <cfsetting requesttimeout=5000>
    <!--- Default vars --->
    <cfparam name="attributes.owner_send" default=false>
    <cfparam name="attributes.bidder_send" default=false>
    <cfparam name="attributes.browser_send" default=false>
    <cfparam name="attributes.guest_send" default=false>
    <cfparam name="attributes.notification_send" default=false>
    <cfparam name="attributes.use_greeting" default=false>
    <cfparam name="attributes.notification_active_status" default=1>
    <cfparam name="attributes.register_before_date" default="">
    <cfparam name="attributes.register_after_date" default="">
    <cfparam name="attributes.campaign_start_date" default="">
    <cfparam name="attributes.campaign_start_time" default="">
    <cfparam name="attributes.subject" default="">
    <cfparam name="attributes.text_message" default="">
    <cfparam name="attributes.html_message" default="">
    <cfparam name="variables.error_message" default="">
    <cfset variables.campaign_total = 0>

    <!--- Purge the spool --->
    <cfquery username="#application.q_user#" password="#application.q_pass#"
    name="query_purge_spool" datasource="#application.datasource#">
    DELETE
    FROM email_spool
    </cfquery>
    <cfoutput>
    <!--- Begin queries --->
    <cfif attributes.owner_send or attributes.bidder_send or
    attributes.browser_send>
    <!--- This is a users table query --->
    <cfquery username="#application.q_user#" password="#application.q_pass#"
    name="query_users" datasource="#application.datasource#">
    SELECT MAX(t1.UserID) AS userid,
    MAX(t1.Username) AS username,
    MAX(t1.FirstName) AS firstname,
    MAX(t1.LastName) AS lastname,
    t1.EmailAddress AS emailaddress,
    MAX(t1.RegDate) AS regdate
    FROM Users t1
    LEFT OUTER JOIN do_not_email t2 ON t1.EmailAddress = t2.email_address
    LEFT OUTER JOIN email_spool t3 ON t1.EmailAddress = t3.recipient
    WHERE t1.SendMarketingEmail = <cfqueryparam cfsqltype="CF_SQL_BIT"
    value="1">
    AND t1.isBadEmail = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0">
    AND (
    <cfif attributes.owner_send>
    t1.isowner = <cfqueryparam cfsqltype="CF_SQL_BIT" value="1"> OR
    </cfif>
    <cfif attributes.bidder_send>
    t1.isbidder = <cfqueryparam cfsqltype="CF_SQL_BIT" value="1"> OR
    </cfif>
    <cfif attributes.browser_send>
    (t1.isowner = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"> AND
    t1.isbidder = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0">) OR
    </cfif>
    <cfif IsDate(attributes.register_before_date)>
    AND t1.regdate <= <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP"
    value="#attributes.register_before_date#">
    </cfif>
    <cfif IsDate(attributes.register_after_date)>
    AND t1.regdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP"
    value="#attributes.register_after_date#">
    </cfif>
    1=2)
    AND t2.email_address IS NULL
    AND (t3.recipient IS NULL)
    GROUP BY t1.EmailAddress
    </cfquery>
    <cfif query_users.recordcount>
    <!--- Set the campaign total --->
    <cfset variables.campaign_total = variables.campaign_total +
    query_users.recordcount>
    <!--- Populate the array if records were returned --->
    <cfloop query="query_users">
    <cfset user_array[query_users.currentrow][1] = query_users.userid>
    <cfset user_array[query_users.currentrow][2] = query_users.firstname>
    <cfset user_array[query_users.currentrow][3] = query_users.lastname>
    <cfset user_array[query_users.currentrow][4] = query_users.emailaddress>
    <cfset user_array[query_users.currentrow][5] = query_users.regdate>
    <cfset user_array[query_users.currentrow][6] = query_users.username>
    </cfloop>
    </cfif>
    <cfif ArrayLen(user_array)>
    <!--- Loop over the array and begin population of the email spool --->
    <cfif Compare(attributes.html_message,"")>
    <!--- HTML message --->
    <cfloop from="1" to="#ArrayLen(user_array)#"
    index="variables.array_index">
    <!--- Save the content of the rendered text message --->
    <cfsavecontent variable="variables.rendered_text_message">
    <cfif attributes.use_greeting>#user_array[variables.array_index][2]#
    #user_array[variables.array_index][3]#,
    </cfif>
    #attributes.text_message#


    About this email:
    You are receiving this email because you are a registered user of SITE.
    To change your communication preferences, please login to your account by
    clicking "My Account" on SITE.com and navigating to "My Information".
    You registered with us on
    #DateFormat(user_array[variables.array_index][5],"m/d/yyyy")# with the username
    #user_array[variables.array_index][6]#.
    Please note that this email was sent from an unattended mail box, and any
    replies will not be answered.
    If you need to get in touch with a SITE representative, please visit SITE.com
    and click "Contact Us".
    </cfsavecontent>
    <!--- Save the content of the rendered html message --->
    <cfsavecontent variable="variables.rendered_html_message">
    <body text=##000000 bgcolor=##ffffff>
    <font face="Verdana,Geneva,Arial,Helvetica,sans-serif" size="2"
    color="##000000">
    <cfif attributes.use_greeting>#user_array[variables.array_index][2]#
    #user_array[variables.array_index][3]#,<br><br></cfif>

    #attributes.html_message#
    </font>
    <br><br><br>
    <font face="Verdana,Geneva,Arial,Helvetica,sans-serif" size="1"
    color="##000000">
    You are receiving this email because you are a registered user of SITE.<br>
    To change your communication preferences, please login to your account by
    clicking "My Account" on SITE.com and navigating to "My Information"<br>
    You registered with us on
    #DateFormat(user_array[variables.array_index][5],"m/d/yyyy")# with the username
    <b>#user_array[variables.array_index][6]#</b>.<br>
    Please note that this email was sent from an unattended mail box, and any
    replies will not be answered.<br>
    If you need to get in touch with a SITE representative, <a
    href="#application.SITE_base_href##application.url _help_comments#">click
    here</a>.
    </font>
    </body>
    </cfsavecontent>
    <!--- Insert the message into the email spool --->
    <cfmodule
    name="common.action.act_mail"
    to="#user_array[variables.array_index][4]#"
    subject="#attributes.subject#"
    send_date="#variables.start_date#"
    text_message="#variables.rendered_text_message#"
    html_message="#variables.rendered_html_message#">
    </cfloop>
    <cfelse>
    <!--- Text only email --->
    <cfloop from="1" to="#ArrayLen(user_array)#"
    index="variables.array_index">
    <!--- Save the rendered text message --->
    <cfsavecontent variable="variables.rendered_text_message">
    <cfif attributes.use_greeting>#user_array[variables.array_index][2]#
    #user_array[variables.array_index][3]#,
    </cfif>
    #attributes.text_message#

    About this email:
    You are receiving this email because you are a registered user of SITE.
    To change your communication preferences, please login to your account by
    clicking "My Account" on SITE.com and navigating to "My Information"
    You registered with us on
    #DateFormat(user_array[variables.array_index][5],"m/d/yyyy")# with the username
    #user_array[variables.array_index][6]#.
    Please note that this email was sent from an unattended mail box, and any
    replies will not be answered.
    If you need to get in touch with a SITE representative, please visit SITE.com
    and click "Contact Us".
    </cfsavecontent>
    <!--- Insert the message into the mail spool --->
    <cfmodule
    name="common.action.act_mail"
    to="#user_array[variables.array_index][4]#"
    subject="#attributes.subject#"
    send_date="#variables.start_date#"
    text_message="#variables.rendered_text_message#">
    </cfloop>
    </cfif>
    </cfif>
    </cfif>
    <cfif attributes.notification_send>
    <!--- Send to notification users --->
    <!--- Create the container array --->
    <cfset user_array = ArrayNew(2)>
    <!--- user_array[1] = user id --->
    <!--- user_array[2] = First name --->
    <!--- user_array[3] = Last name --->
    <!--- user_array[4] = Email address --->
    <!--- user_array[5] = Registration date --->
    <!--- This is an email to users on the notifications list --->
    <cfquery username="#application.q_user#" password="#application.q_pass#"
    name="query_users" datasource="#application.datasource#">
    SELECT t1.EmailAddress AS emailaddress,
    MAX(t1.NotificationID) AS notificationid,
    MAX(t1.uuid) AS uuid,
    MAX(t1.Added) AS added
    FROM NewAuctionNotification t1
    LEFT OUTER JOIN do_not_email t2 ON t1.EmailAddress = t2.email_address
    LEFT OUTER JOIN email_spool t3 ON t1.EmailAddress = t3.recipient
    WHERE t1.SendMarketingEmail = <cfqueryparam cfsqltype="CF_SQL_BIT"
    value="1">
    AND t2.email_address IS NULL
    AND t3.recipient IS NULL
    <cfif attributes.notification_active_status>
    AND t1.isactive
    hans Guest

  10. #10

    Default Re: Possible cfquery bug?

    hans blix wrote: 

    cfflush shouldn't be necessary because you have this wrapped in
    cfsilent, but adding it with a 100 byte interval certainly won't hurt.

     

    I don't see any direct problems, but this code seems rather memory
    intensive. You have a 20K records query, an equally long array and then
    some more. Could it be that this page is creating enough memory pressure
    so the garbage collector is running constantly?

    Jochem


    --
    Jochem van Dieten
    Adobe Community Expert for ColdFusion
    Jochem Guest

  11. #11

    Default Re: Possible cfquery bug?

    I added a cfflush tag and ran it again. Same behavior.

    This time I watched the jrun process memory. It starts around 680,000K (which
    is typical usage for this server). It steadily climbs to around 1,125,000K. At
    about this time ColdFusion is running at a crawl, inserts to that table are
    trickling in, and even the cfstat utility is lagging.

    Then I tried running it without the cfquery tag that does the insert.
    Everything runs great, jrun memory usage remains relatively flat, and the task
    completes.

    So cfquery (and cfstoredproc) seems to be leaking memory, or at least eating
    large chunks of it. This seems to point at a problem with:
    -the sql driver (datadirect)
    -the cfquery tag
    -sql server

    I've updated the sql driver to 3.5. I've also turned off "maintain
    connections" on that driver to see if there was a pooling problem. I've also
    switched to ODBC to rule out the driver, and had the same issues there too.
    I've monitored SQL server during the run and that seems very responsive.

    So the only thing I can point to is an underlying problem with cfquery and
    cfstoredproc. Is this possible, or am I missing something?

    hans Guest

  12. #12

    Default Re: Possible cfquery bug?

    So the only thing I can point to is an underlying problem with cfquery
    and cfstoredproc. Is this possible, or am I missing something?

    The amount of time a single query/stored procedure takes to run? Could
    the loop be running at a pace that is piling up SQL request upon SQL
    request faster then the database can process them. They start queuing
    up and taking up more and more memory waiting for the database to be
    available to run more processes?

    A simple test, if you can spend the time, is put a little sleep pause in
    your loop. This will obviously take a very long time to run, but if it
    smooths out the memory problem could point to a more robust solution.

    Add this before your massive loop:
    <cfset thread = CreateObject("java", "java.lang.Thread")>

    Then in each iteration of the loop have it sleep for a few moments to
    give time for the database to process that request.
    <cfset thread.sleep(100)><!--- Number of milliseconds to pause --->

    Obviously pick a time based on how long an individual database request
    should take.
    Ian Guest

  13. #13

    Default Re: Possible cfquery bug?

    hans blix wrote: 

    What is the maximum memory you set for CF? If you increase that, does CF
    still crawl?

     

    Try something else with that query: remove its name attribute.


    Do you have debugging enabled? Make sure it is switched off.

    Jochem


    --
    Jochem van Dieten
    Adobe Community Expert for ColdFusion
    Jochem Guest

  14. #14

    Default Re: Possible cfquery bug?

    This is interesting:

    I tried calling that cfquery with a cfc instead of with cfmodule. It bogged
    down again.

    I tried with a custom tag, same problem.

    I put the cfquery inline in the calling template. It ran with no problem!

    So, that makes completely no sense to me.

    I think that rules out SQL statements piling up in a queue, although that
    really sounded plausible.

    I also tried bumping up the memory another 500M, same problem.

    Going to test removing the query "name" attribute and disabling debugging
    (which was turned on).




    hans Guest

  15. #15

    Default Re: Possible cfquery bug?

    I tried calling that cfquery with a cfc instead of with cfmodule. It
    bogged down again.

    I tried with a custom tag, same problem.

    I put the cfquery inline in the calling template. It ran with no problem!

    So, that makes completely no sense to me.

    Well, custom tags either as a cfmodule or directly called and cfc do
    create their own memory scopes. I can imagine it is possible you where
    filling up your memory with 20,000 little pieces of memory reserved for
    use by many many custom tags/cfcs. In line would not do this of course.



    Ian Guest

  16. #16

    Default Re: Possible cfquery bug?

    Removed "name" attribute from cfquery - same problem
    Disabled debugging - runs fine

    So, seems like if a cfquery tag is within an external component or template,
    debugging starts to choke after thousands of iterations. Presumably because the
    memory almost doubled while debugging was enabled. Debugging has no problem
    when the query is inline, but but that's not really a solution.

    I also tried with cfsetting showdebugoutput="No", but had the same problem. I
    think that just disables output and doesn't kill debugging for that thread.

    I'm not sure if this is a bug or not, but it's certainly inconsistent
    behavior. Anyone know a way to kill debugging on a per request basis?

    hans Guest

  17. #17

    Default Re: Possible cfquery bug?

    "I also tried with cfsetting showdebugoutput="No", but had the same
    problem. I think that just disables output and doesn't kill debugging
    for that thread."

    Yes, just as it is described in the doentation.

    "I'm not sure if this is a bug or not, but it's certainly inconsistent
    behavior. Anyone know a way to kill debugging on a per request basis?"

    Well, not a bug I would say, debugging takes a lot of resources, doing
    it repeatedly 20,000 times takes an insane amount of resources. That is
    why debugging it not recommended as a production setting.

    I don't know about a per request basis, but the administration api of
    the latest versions of CF may give you control to programmatically turn
    debugging on or off. I have never looked so I'm not sure what is there.
    Ian Guest

Similar Threads

  1. cfquery bug...still?
    By jlerath in forum Coldfusion Server Administration
    Replies: 2
    Last Post: November 30th, 06:06 PM
  2. cfquery and T-SQL
    By doug777 in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: March 17th, 10:01 AM
  3. cfquery
    By ducman in forum Macromedia ColdFusion
    Replies: 3
    Last Post: August 2nd, 07:09 AM
  4. need help with cfquery
    By mkauspe in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 24th, 11:11 PM
  5. CFQUERY with IF THEN ELSE
    By mike2004 in forum Coldfusion Database Access
    Replies: 10
    Last Post: March 4th, 03:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •