[Dbix-class] Help with Titanic Number of Inserts

[Jess Robinson]

On Wed, 24 Jan 2007, Hartmaier Alexander wrote:

> Hi!
>
> Take a look at DBIC::Schema->populate! In 0.07xxx it just calls create
> multiple times, but in 0.08 that will change so that the statement is 
> only prepared once and executed multiple times which should boost
> performance again.

Actually it doesnt even do that, if you call populate in void context, it 
will use DBIs built-in execute_array method for inserting many rows at 
once. (It could be this is a prepare/execute under the hood, I haven't 
checked).

For really large inserts though, you'd be quicker using your databases bcp 
(bulk copy) program. Or, as we used to do with DB2 and Oracle, you can 
pretend a file of input data is an "external" table (usually a CSV file), 
and just select/insert from it as if it were a table.

> I hope that helps ;-)

Ditto ;)

Jess

> -Alex
>
>
>> -----Original Message-----
>> From: Ryan Richt [mailto:rjr2 at cec.wustl.edu]
>> Sent: Wednesday, January 24, 2007 2:01 AM
>> To: dbix-class at lists.rawmode.org
>> Subject: [Dbix-class] Help with Titanic Number of Inserts
>>
>> Hey Guys and Gals...
>>
>> 	We are just starting a DBIx::Class project here at the Washington
>> University Genome Sequencing Center in St. Louis, and __of__course__
>> we choose DBIx::Class over all that other stuff ;-).
>>
>> Anyway, we need to make > 112,000,000 inserts per day.  I _know_ that
>> DBIx::Class is not concerned with performance, but with the
>> (wonderful) design/interface/features.
>>
>> However, other than wrapping it in a transaction, do you have any
>> other tips on making this manageable?  Currently this would take 130
>> DAYS to insert w/o transactions, and something like 30 DAYS with
>> transactions.
>>
>> (As far as setup goes, this is currently in SQLite and soon to be
>> Oracle once SQLite explodes, testing on a newer dual processor  3gig
>> ram machine, and could deploy on anything from 1000+ node cluster or
>> 50 node cluster of Opertons or 5 x 100gig ram itanium quad processor
>> machines, if that could help us in any way!).
>>
>> Thank you very much!
>>
>> Ryan
>>
>> _______________________________________________
>> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
>> Wiki: http://dbix-class.shadowcatsystems.co.uk/
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>> Searchable Archive: http://www.mail-archive.com/dbix-
>> class at lists.rawmode.org/
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
> Handelsgericht Wien, FN 79340b
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> Notice: This e-mail contains information that is confidential and may be privileged.
> If you are not the intended recipient, please notify the sender and then delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>
>