NYCPHP Meetup

[Hans Zaunere]

> 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.

Why would this be considered a result of PHP being slow?  You're doing many
millions of database queries (both INSERT and SELECT from what you
describe), which is the real culprit.

Some things to consider:

-- minimize lookup queries by caching, or loading your lookup tables into
PHP arrays, thus avoiding database queries

-- make sure that any lookup queries you do perform are against a well
indexed table - similarly, consider key buffer sizes, buffer pool sizes, and
all that good stuff

-- attempt to batch inserts (see http://dev.mysql.com/insert for various
techniques)

-- look at dev.mysql.com and google for mysql bulk insert and mysql bulk
insert configuration - there are a lot of tunables pertaining to these types
of operations, including temp. turning off indexes, etc.

The only PHP specific item I'd consider at this point is to explicitly free
memory and resources to keep the memory footprint as small as possible.
PHP's garbage collection cannot always be, well, perfect, for long running
processes.

H