NYCPHP Meetup

[Kenneth Downs]

This ain't PHP,and Python won't help.  Inserting 2.4 million rows into a 
database will take hours if you go line by line.  You are paying a big 
overhead of a round trip to the server for each row.

The best way to speed it up is to use what they call "bulk copy" on MS 
SQL Server and "copy" on PostgreSQL.  I'd have to assume mySQL has 
something by the same name.

Also, it may be that mySQL supports multiple inserts per line, in which 
case you can do 10 inserts per round trip.  This will speed it up w/o 
requiring you to do to a different command for the bulk copy.

Nicholas Hart wrote:
> I have a php 'cron job' script which is inserting some records.  It 
> uses arrays to store 12 field records along with several nested loops 
> in which it tests various currency values from a separate 4 field 
> table and sorts them to get the lowest one for insert into a new 4 
> field table record.  It has about 640,000 Product records per 4 
> Customers or apporx. 2.4 million records to insert.  It takes several 
> hours+ to complete and I am hoping there is a way to speed it up. 
>
> So far, I've tried updating instead of inserting records and changing 
> a few functions but nothing seems to make much difference.  Am I being 
> unreasonable for the size of the files here?  I'm considering using a 
> Derived Table in mySQL and will also consider another language such as 
> python.  Please let me know if you have any suggestions.  Thanks.
>
> Nicholas Hart
>
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/show_participation.php


-- 
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com    www.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20090213/bc37053a/attachment.html>