On 11/15/2014 12:42 PM, Roger Austin wrote:
> Any chance to have the database engine do all that record logic?
After killing myself on this, I finally realiaed I was doing it ALL
WRONG! All those loops and everything to filter the array, then do the
insertwas taking forever.
I'm revising - BULK IMPORT EVERYTHING to a temp table. No filters. Then,
run the filters against the database and write to the final table. A
LOT faster. Duh..
Still got one problem, and it's the "duplicates filter".
So, I need to filter the inserted records and kill any duplicate email
addresses.
First, I need to be sure I'm filtering ONLY the addresses that belong to
the specific group, as folks might be subscribed to more than one group:
<cfquery name="getFULLLIST">
SELECT groups_id, email_id from nl_catREL
WHERE groups_id = <cfqueryparam value="#req.thisGROUPID#"
cfsqltype="CF_SQL_INTEGER" />
</cfquery>
<cfset fullLIST = #ValueList(getFULLLIST.email_id)# />
Now, let's see if I can return JUST the duplicates. Once that works,
it's no big deal to delete them...
<cfquery name="findDups">
SELECT distinct a.email_id
FROM nl_mailgroups a, nl_mailgroups b
WHERE a.ml_id in (<cfqueryparam value="#fullLIST#"
cfsqltype="CF_SQL_INTEGER" list="yes" />)
AND a.email_id > b.email_id
AND a.ml_email = b.ml_email
ORDER BY a.email_id
</cfquery>
That doesn't work. It returns everything ... and it takes a really long
time, even with only 150 addresses or so.
Ideas?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:359650
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
