[Dbix-class] update a row only if any column has changed

[Rajeev Prasad]

thx Brendan,
the source is a text file. I guess I will use MySQL tools to load it into t=
he database everyday, deleting the table completely before recreating.....
=A0
i guess thats the best way?=A0


________________________________
From: Brendan Byrd <Perl at ResonatorSoft.org>
To: Rajeev Prasad <rp.neuli at yahoo.com>; DBIx::Class user and developer list=
 <dbix-class at lists.scsys.co.uk> =

Sent: Friday, April 5, 2013 10:01 PM
Subject: Re: [Dbix-class] update a row only if any column has changed


On Fri, Apr 5, 2013 at 7:45 PM, Rajeev Prasad <rp.neuli at yahoo.com> wrote:

hello,
>
>I have a table with around 2,000,000 records (15 columns). I have to sync =
this from an outside source once everyday. not all records are changed/remo=
ved /new-added everyday. so what is the best way to update only those which=
 have changed/added/or deleted?
>
>i can use update_or_create but that will update (re-write the row) even if=
 nothing has changed in the row/record. wont that be an overhead? how can i=
 escape that? what would be the fastest and least resources consuming way t=
o do this table update?
>
>I also have another table with 500,000 rows and i wish to implement the sa=
me solution to that too.
>
Geesh, when your data loads are hitting 6 and 7 digits rows figures, you sh=
ould seriously consider the tools that come with the RDBMS, like replicatio=
n. =A0Is your outside source a modern RDBMS that features master/slave repl=
ication?

Otherwise, without some sort of field indicator on the source side saying t=
hat this was a modified row, you can't tell if it's been changed or not. =
=A0That means DBIC, or whatever other ETL middleware you choose, will have =
to compare every single row to see if it exists or not. =A0With that many r=
ows, it's better off to use the "UPSERT" SQL statements for mass updating. =
=A0In MySQL, it's commands like "REPLACE" and "INSERT ... ON DUPLICATE KEY =
UPDATE ...". =A0In Oracle, it's MERGE.

You also mentioned "removed". =A0So you want an exact copy of the source ta=
ble? =A0Yeah, screw all of the tip-toeing around the existing data and just=
 TRUNCATE the table, followed by the best BULK INSERT command your RDBMS re=
commends. =A0(For example, MySQL recommends LOAD DATA INFILE.) =A0And make =
sure you don't do something crazy, like reading the entire source table int=
o memory via Perl variables. =


Keep in mind that almost none of this recommends using DBIC. =A0DBIC is for=
 making changes in smaller quantities than what you're asking. =A0YMMV, so =
consult your DBA folks first before you start dumping massive quantities of=
 data into their servers.
-- =

Brendan Byrd <Perl at ResonatorSoft.org>
Brendan Byrd <BBYRD at CPAN.org> =

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130406/cc8=
cef33/attachment.htm