SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    London
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help on best way to insert many items quickly

    Hi,

    My client has a product database od around 20000 items. And it is updated every few days when he gets a catalog from the supplier.

    It's supposed to work like this: if there already is a product in the database with that ID (primary key), just UPDATE the price, and if there is no product with that ID it should do an INSERT for that item.

    Now I've tried it in several ways and I can't seem to get it to execute in under 20 seconds (max execution time is 30s, so just to be sure).

    I've tried doing an update for every item (UPDATE product SET price=$price where ID=$id) and if I get a mysql_affected_rows()==0 (a PHP function that returns the number of affected rows) then I do an insert. This takes too long for 20000 items.

    I've also tried collecting all IDs in one array and then doing an in_array() check (in_array() in PHP checks whether a value is in array) for every ID value, and if it exists in the array I do an update in MySQL, and if not I do an insert. This also takes too long (although less than the first method).

    Of course I can have the script redirect to itself after 20 seconds and continue from where it left off, but this seems kind of lame.

    Since the client wants to enter extra data for products and not lose it when he does a price update, I can't just replace everything on every update, just the price.

    Changing the hosting is out of the question (he paid 2 years in advance and has like 100000 emails set up in the control panel).



    I'm sorry if this does not belong in the databases forum, should I post it to PHP? And sorry for the explanations of PHP functions, just in case...




    rgds

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would load all the new products into a holding table and then use a single query or two to update and insert the data

    any time you try to do queries in a loop in php, performance suffers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •