NYCPHP Meetup

[Kenneth Downs]

I'll bet you'll see major speedup with two items:

1) Indexes required: VItems.mcn, VItems.V_ID, VItems.mcn

2) Break up the command into multiple commands, varying for CN3, CN4 etc:

update VItems set
       VItems.Inventory= feed.cn3quan
      ,VItems.ItemCst  = feed.unitcost
      ,VItems.ItemPrce = feed.unitcost
  from feed
 where VItems.MCN=feed.mcn
   and VItems.V_ID = 'CN3'

(Disclaimer: I don't use MySQL so you may have to tweak the SQL for
syntactical variations).

Here is why this should work.

FIRST, Transaction performance goes *exponentially* as the number of rows
in the transaction.  Consider 10 transactions of 5000 rows each, you get
(5000^2) * 10 = 2.5e8 "units" of time.  But one transaction of (50,000^2)
or 2.5e9 "units" of time, 10 times as long!

SECOND, It's really easy to test.  If the theory is right, the first
command will take 1/10 of 1/10 of the total time, or 1/10 of ( 1/10 of 4
hours ) = 2.4 minutes.  This is of course only a rule of thumb, but at
least it won't take 4 hours to find out :)

THIRD.  If this works, the best thing to do is cut the cron job down to
every five minutes.  I'm assuming  you clear the "feeds" table every time
you finish the job, so if you go every 5 minutes the transactions are
always very tiny.  You could possibly go back to one command in that case.

On a totally different track, we call this entire operation a "transpose".
 In particular it is a de-normalizing transpose, turning a normalized feed
into a denormalized feed.  These are always bad, and this will give you
trouble forever.  If you have control over the database and can get rid of
this your life would be much better for it.

Hope this helps.


> Hello guys,
>
> I have a sql query that basically copies data from one table to another,
> here is the query
>
> use mydb;
> update Vtems left join  feed on Vtems.MCN=feed.mcn
> set VItems.Inventory= case (VItems.V_ID)
> when 'CN3' then feed.cn3quan
> when 'CN4' then feed.cn4quan
> when 'CN5' then feed.cn5quan
> when 'CN6' then feed.cn6quan
> when 'CN7' then feed.cn7quan
> when 'CN8' then feed.cn8quan
> when 'CN9' then feed.cn9quan
> when 'CN10' then feed.cn10quan
> when 'CN11' then feed.cn11quan
> when 'CN16' then feed.cn16quan
> else '' end
> ,VItems.ItemCst = feed.unitcost,
> VItems.ItemPrce = feed.unitcost
> commit;
>
>
> We have a php cron job which executes and runs this query, however it
> takes
> anywhere between 4-8 hours for this to run on 50,000 records, now this
> number will also grow and we have determined that the left join is what is
> slowing it down.  What i need to know is do you have any suggestions on a
> better method for doing this that would speed it up considerably.
>
> I know we could run this on the mysql command line and we have tried.  It
> seems to take just as long on the command line as the php cron, however we
> are also wishing to implement emails when there are errors etc..  The avg
> time is 6 hours on the production server to complete the import.
>
> Thanks,
> --
> Joseph Crawford Jr.
> Zend Certified Engineer
> Codebowl Solutions, Inc.
> 1-802-671-2021
> codebowl at gmail.com
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org


-- 
Kenneth Downs
Secure Data Software
631-379-0010
ken at secdat.com
PO Box 708
East Setauket, NY 11733