> I'm still fighting with my Excel Import application a
> bit. Basically, it works pretty well, as long as
> there's a limited number of records being imported.
Was testing last night with the 15,000 record Excel sheet.
If I comment out the two filter queries in the code, it will read in the sheet
and a cfdump of the array shows everything was imported properly. Didn't time
it, but it only took a few minutes. Adding those two queries back in and trying
to import the entire sheet - the server choked and I had to call support to
reboot it. Whoops.
It's the two filter queries below that are slowing the whole thing down and
choking the server. The one optimization I can think of is the look for
duplicates query. Instead of running that against the entire database looking
for matches (several hundred thousand records last time I checked), I should do
a query of queries and run the loop ONLY against records for that one group,
which I'd prefetch before starting the loop. That should help a lot. Working on
that now...
One thought I had - I don't care how long the import of a large sheet takes. It
just can't choke the server. Would there be any benefit of putting in a DELAY
for every 500 records (or so) in the loop to give the server a "rest" to do
other stuff?
<cfif i MOD 500 EQ 0>
<cfset sleep(5000) />
</cfif>
FILTER QUERIES
<!--- Before we can save the list, we need to run two checks --->
<!--- First, is there an email address in this list that has previously
unsubscribed? --->
<cfoutput>
<cfloop from="#arrayLen(mailREC)#" to="1" step="-1" index="i">
<!--- delete the row if it was an invalid address. Got to be a way to fix
this but --->
<!--- the above "data" output block wants to add an invalide row if you
try to skip the entire row when the address is invalid --->
<cfif mailREC[i][1] eq "-">
<cfset ArrayDeleteAt(mailREC, #i#)>
<cfelse>
<!--- OK, it's a valid address. Have they unsubscribed before? --->
<cfquery name="unsubbed" datasource="#req.datasource#"
username="#username#" password="#password#">
SELECT groups_id FROM maillist_unsubscribe
WHERE dateRESUB is NULL
AND ml_email = <cfqueryparam value="#mailREC[i][1]#"
cfsqltype="CF_SQL_VARCHAR">
AND groups_id = <cfqueryparam value="#req.thisGROUPID#"
cfsqltype="CF_SQL_INTEGER" />
</cfquery>
<cfif unsubbed.recordcount EQ 1><cfset ArrayDeleteAt(mailREC,
#i#)></cfif>
</cfif>
</cfloop>
<!--- if Admin has checked the duplicate filter box on the form, but sure
nothing incoming is a duplicate already in the database for this group --->
<cfif form.dupFILTER EQ "on">
<cfloop from="#arrayLen(mailREC)#" to="1" step="-1" index="i">
<cfquery name="ckDUP" datasource = "#req.datasource#"
username="#username#" password="#password#">
Select groups_id from vw_mailLIST
WHERE groups_id = <cfqueryparam value="#trim(req.thisGROUPID)#"
cfsqltype="CF_SQL_INTEGER" />
AND ml_email = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#mailREC[i][1]#" />
</cfquery>
<cfif ckDUP.recordcount EQ 1><cfset ArrayDeleteAt(mailREC, #i#)></cfif>
</cfloop>
</cfif>
</cfoutput>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359647
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
