Efficiently synchronizing warehouse information

Question

Ok, let's see if you Wizards can help me with this problem I'm banging my head against.

The scenario is a pretty standard setup with a point of sale (that's where I am) and a backoffice keeping track of warehouse information. The problem is that I need to synchronize the database at the point of sale with the current stock information from the backoffice. Right now this is done by fetching a catalog from the backoffice via Web. The catalog contains information such as currently available units, current price and unique IDs for each type. In my database I have the exact same information and I update it after fetching the catalog. It is important for me to insert new unit types as they appear, update already existing types with availability information and price, and finally delete types (or mark them unavailable) once they go below a certain threshold.

The current implementation just marks all items as unavailable in case they were dropped from the catalog, then for each item in the catalog issues a query like this:

INSERT INTO store (id, quantity, price)VALUES(x, y, z)
ON DUPLICATE KEY quantity=y, price=z;

The id is set as unique to avoid duplicate data that is out of date in the database, and cause a collision that triggers the update instead. The main problem is that this issues one query for each type in the catalog, which may grow rapidly.

Is there a faster solution to this problem?

Answer 1

INSERT INTO store (id, quantity, price) VALUES (x, y, z), (x2,y2,z2)

But i dont know how this jives with your ON DUPLICATE KEY statement... you could perform some logic to split the data into groups for update and insert independently.

Answer 2

REPLACE INTO store (id, quantity, price) VALUES (x, y, z), (x2,y2,z2);

I'd also suggest not sending them to the webapp as SQL (horrible security) and sending them as large batchs (1000 rows at a time) in some format your comfortable with. The webapp does all the REPLACE statements and returns success.