Better manage bulk update process

[Agha Mehdi]

All,

I have a bulk update process for our products catalog. It needs to delete
old data before it loads new data. Sometimes it fails to load new data after
deleting old data, which causes product catalog to be empty. I don't have
CFTransaction wrapped around it but I want to put it in place. What is the
recommended method to do it?

Thanks


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328307
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Alan Rother]

Well... There are a number of techniques for ensuring data integrity.

In cases where I'm using CF to manage a bulk import (rather than the
database itself) I tend to use a staging database to import the data and
then I only transfer it to the production database IF the import passes some
sort of checksum test. Then I manage the data transfer from the staging
database to the production database using a DTS package(MS SQL) rather than
using CF as database specific data migration tools tend to work far more
quickly and a far more reliable.

=]

--
Alan Rother
Adobe Certified Advanced ColdFusion MX 7 Developer
Manager, Phoenix Cold Fusion User Group, AZCFUG.org


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328316
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Agha Mehdi]

Tell me about it. I would take that approach if it was an option. It has to
be done through cf on a live production db before tomorrow morning.

On Thu, Nov 12, 2009 at 1:02 PM, Alan Rother <alan.rother@...> wrote:

>
> Well... There are a number of techniques for ensuring data integrity.
>
> In cases where I'm using CF to manage a bulk import (rather than the
> database itself) I tend to use a staging database to import the data and
> then I only transfer it to the production database IF the import passes
> some
> sort of checksum test. Then I manage the data transfer from the staging
> database to the production database using a DTS package(MS SQL) rather than
> using CF as database specific data migration tools tend to work far more
> quickly and a far more reliable.
>
> =]
>
> --
> Alan Rother
> Adobe Certified Advanced ColdFusion MX 7 Developer
> Manager, Phoenix Cold Fusion User Group, AZCFUG.org
>
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328322
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Maureen-2]

I would put the transaction and a catch/try around each insert.  Then
if it fails, you know exactly where it fails, and can handle the error
cleanly.

I would also do a record count after the delete to confirm that old
data is being cleared, and reset any auto-increment fields, triggers,
and rebuild the indexes.

On Thu, Nov 12, 2009 at 10:13 AM, Agha Mehdi <aghaimehdi@...> wrote:

>
> All,
>
> I have a bulk update process for our products catalog. It needs to delete
> old data before it loads new data. Sometimes it fails to load new data after
> deleting old data, which causes product catalog to be empty. I don't have
> CFTransaction wrapped around it but I want to put it in place. What is the
> recommended method to do it?
>
> Thanks

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328329
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Fisher-3]

Just a note that the CFTRANSACTION needs to be around the entire block
of CFQUERY statements, or else it does you no good ...

<cftransaction>
<cftry>
<cfquery>
DELETE some stuff
</cfquery>
<cfquery>
INSERT some stuff
</cfquery>
<cfquery>
UPDATE some other stuff
</cfquery>
<cfcatch type="Database">
trap your error and report back to yourself
</cfcatch>
</cftry>
</cftransaction>

That will make sure that any error on the DB side gets caught and
reported, but all changes get reset to where they were before the first
query ever started.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328337
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Alan Rother]

Hmm...

Ok, any chance you could create Temp tables that match the production ones?
Run your import on them, then once complete, table by table TRUNCATE the
prod data and then transfer the new data over to the real tables?

=]


--
Alan Rother
Adobe Certified Advanced ColdFusion MX 7 Developer
Manager, Phoenix Cold Fusion User Group, AZCFUG.org


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328338
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Agha Mehdi]

Yup. that's what I am doing now. CFTransaction is an option but it won't do
me any good if for some reason the processing just stops without any error.
It is strange but that's what's happening. I haven't found any errors in the
logs. Also, it gets more interesting as the task runs every 12 hours. the
one that runs in the evening is fine and the one running in the morning has
started to not run after deleting records from two tables. I am also going
to start writing each row's insert/update success to a log file to see
exactly what's going on.

On Thu, Nov 12, 2009 at 4:01 PM, Alan Rother <alan.rother@...> wrote:

>
> Hmm...
>
> Ok, any chance you could create Temp tables that match the production ones?
> Run your import on them, then once complete, table by table TRUNCATE the
> prod data and then transfer the new data over to the real tables?
>
> =]
>
>
> --
> Alan Rother
> Adobe Certified Advanced ColdFusion MX 7 Developer
> Manager, Phoenix Cold Fusion User Group, AZCFUG.org
>
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328339
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Maureen-2]

Is there any chance the morning run is conflicting with another task,
like a backup, or another import, and just stalling?

On Thu, Nov 12, 2009 at 4:29 PM, Agha Mehdi <aghaimehdi@...> wrote:
>
> Yup. that's what I am doing now. CFTransaction is an option but it won't do
> me any good if for some reason the processing just stops without any error.
> It is strange but that's what's happening. I haven't found any errors in the
> logs. Also, it gets more interesting as the task runs every 12 hours. the
> one that runs in the evening is fine and the one running in the morning has
> started to not run after deleting records from two tables. I am also going
> to start writing each row's insert/update success to a log file to see
> exactly what's going on.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328342
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Agha Mehdi]

there are other tasks that run at the same time too but none that would
interact with the same tables. One thing I did notice though that there is
only one task that runs at 8:00 PM (which is this one) and there are 4
different tasks that run at 8:00 AM (including this task). Could that be an
issue?

On Thu, Nov 12, 2009 at 6:25 PM, Maureen <mamamaureen@...> wrote:

>
> Is there any chance the morning run is conflicting with another task,
> like a backup, or another import, and just stalling?
>
> On Thu, Nov 12, 2009 at 4:29 PM, Agha Mehdi <aghaimehdi@...> wrote:
> >
> > Yup. that's what I am doing now. CFTransaction is an option but it won't
> do
> > me any good if for some reason the processing just stops without any
> error.
> > It is strange but that's what's happening. I haven't found any errors in
> the
> > logs. Also, it gets more interesting as the task runs every 12 hours. the
> > one that runs in the evening is fine and the one running in the morning
> has
> > started to not run after deleting records from two tables. I am also
> going
> > to start writing each row's insert/update success to a log file to see
> > exactly what's going on.
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328349
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Agha Mehdi]

I also just thought of something. I am using the following setting;

Frequency: Daily every 12 hours start time: 11:00 PM. Do I need to specify
End time too?

On Fri, Nov 13, 2009 at 8:58 AM, Agha Mehdi <aghaimehdi@...> wrote:

> there are other tasks that run at the same time too but none that would
> interact with the same tables. One thing I did notice though that there is
> only one task that runs at 8:00 PM (which is this one) and there are 4
> different tasks that run at 8:00 AM (including this task). Could that be an
> issue?
>
>
> On Thu, Nov 12, 2009 at 6:25 PM, Maureen <mamamaureen@...> wrote:
>
>>
>> Is there any chance the morning run is conflicting with another task,
>> like a backup, or another import, and just stalling?
>>
>> On Thu, Nov 12, 2009 at 4:29 PM, Agha Mehdi <aghaimehdi@...> wrote:
>> >
>> > Yup. that's what I am doing now. CFTransaction is an option but it won't
>> do
>> > me any good if for some reason the processing just stops without any
>> error.
>> > It is strange but that's what's happening. I haven't found any errors in
>> the
>> > logs. Also, it gets more interesting as the task runs every 12 hours.
>> the
>> > one that runs in the evening is fine and the one running in the morning
>> has
>> > started to not run after deleting records from two tables. I am also
>> going
>> > to start writing each row's insert/update success to a log file to see
>> > exactly what's going on.
>>
>>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328353
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[DURETTE, STEVEN J (ATTASIAIT)]

I'd say no.  The end time is only if you want the job to run between
certain hours.

For example I have a job that runs every 5 minutes between 8am and 4pm.

Steve


-----Original Message-----
From: Agha Mehdi [mailto:aghaimehdi@...]
Sent: Friday, November 13, 2009 12:58 PM
To: cf-talk
Subject: Re: Better manage bulk update process


I also just thought of something. I am using the following setting;

Frequency: Daily every 12 hours start time: 11:00 PM. Do I need to
specify
End time too?

On Fri, Nov 13, 2009 at 8:58 AM, Agha Mehdi <aghaimehdi@...>
wrote:

> there are other tasks that run at the same time too but none that
would
> interact with the same tables. One thing I did notice though that
there is
> only one task that runs at 8:00 PM (which is this one) and there are 4
> different tasks that run at 8:00 AM (including this task). Could that
be an
> issue?
>
>
> On Thu, Nov 12, 2009 at 6:25 PM, Maureen <mamamaureen@...>
wrote:
>
>>
>> Is there any chance the morning run is conflicting with another task,
>> like a backup, or another import, and just stalling?
>>
>> On Thu, Nov 12, 2009 at 4:29 PM, Agha Mehdi <aghaimehdi@...>
wrote:
>> >
>> > Yup. that's what I am doing now. CFTransaction is an option but it
won't
>> do
>> > me any good if for some reason the processing just stops without
any
>> error.
>> > It is strange but that's what's happening. I haven't found any
errors in
>> the
>> > logs. Also, it gets more interesting as the task runs every 12
hours.
>> the
>> > one that runs in the evening is fine and the one running in the
morning
>> has
>> > started to not run after deleting records from two tables. I am
also
>> going
>> > to start writing each row's insert/update success to a log file to
see
>> > exactly what's going on.
>>
>>



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328356
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Fisher-3]

No, you shouldn't need to specify end, but make sure there's a timeout
sufficient for the process to complete.
 



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328362
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Maureen-2]

Do any of the other tasks use the tables you are deleting or updating?

On Fri, Nov 13, 2009 at 8:58 AM, Agha Mehdi <aghaimehdi@...> wrote:
>
> there are other tasks that run at the same time too but none that would
> interact with the same tables. One thing I did notice though that there is
> only one task that runs at 8:00 PM (which is this one) and there are 4
> different tasks that run at 8:00 AM (including this task). Could that be an
> issue?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328385
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Agha Mehdi]

nope, the other tasks don't do anything to those tables.

so here's what I did.

wrapped cftransaction around the process.
created two staging tables that hold the process updates.
once the process finishes, i call a stored proc, which deletes old data from
prod tables, copy data from staging to prod tables and truncates staging
tables.

This is working without crashing and the web site doesn't lose any data
while the import job is running.

thanks everyone for pitching in. that's why i love this list and rejoined
after 3 years of absence. :)

On Fri, Nov 13, 2009 at 4:18 PM, Maureen <mamamaureen@...> wrote:

>
> Do any of the other tasks use the tables you are deleting or updating?
>
> On Fri, Nov 13, 2009 at 8:58 AM, Agha Mehdi <aghaimehdi@...> wrote:
> >
> > there are other tasks that run at the same time too but none that would
> > interact with the same tables. One thing I did notice though that there
> is
> > only one task that runs at 8:00 PM (which is this one) and there are 4
> > different tasks that run at 8:00 AM (including this task). Could that be
> an
> > issue?
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328387
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4