I recently installed Magento, imported my products, configured google analytics, paypal, customized the look and feel, etc. Everything went relatively easily. Love Magento!
However, I have about 1400 products in my database and I have to update the quantity information every 2 hours. No problem, I wrote a little script that will download the information from my inventory control software and creates a Magento specific import file.
BUT! The magento import process, in my opinion, is ridiculously slow! It works great, but it becomes almost useless because it takes almost an hour for it process 1400 products and update the inventory levels.
Now it takes me less then a second to export this data from my inventory control system. For the import to take this long is really unacceptable, in my opinion.
So, I would like to know if anyone could point me in the right direction to bypass Data Flow and import directly into the database. What tables are in play here? I’m reasonably certain that I can write a query, or number of queries, that would do the job in seconds.
Any ideas or comments would be greatly appreciated!
Ok, so as far as I can tell the tables involved are: catalog_inventory_stock_item and catalog_product_entity
So, the query to get the current inventory levels would look something like this
SELECT s.qty
FROM catalog_inventory_stock_item s, catalog_product_entity p
WHERE s.product_id = p.entity_id
If there are any experienced Magento developers here could someone please verify that this is correct?
If it is I could simply do a LOAD DATAFILE INTO to read my csv stock data into a temp table and then use an UPDATE statement to update the Magento structure above based on the SKU in catalog_product_entity.
Can anyone please verify that my assumptions are correct?
have you thought about using the API to achive this?
in theory yes, but i would be extremely carefull and try this out of a dev system first.
i havent looked into the inventory side of magento myself, but you might also have to take into consideration store ID’s also.
Another thing to consider is a fallback strategy. Like prior to running any updates, make a backup of your db.
You have to keep in mind that dataflow is making sure all records are correctly loaded. Whereas your SQL updates could cause cascading errors in your db. There are many FK relationships that could be updated incorrectly if you make an error.
I do this with my production database on a nightly basis via ODBC. You’re correct, the cataloginventory_stock_item is the table you need. I can offer a couple pieces of advice: First, make sure to only do updates on records that are different, not the whole database. Also, if you have an item that was set to zero on hand but is being increased you need to flip the is_in_stock switch from 0 to 1.
I’m using this query to update my inventory. I first read the contents of my excel file to a temp table using LOAD DATA INFILE (very fast). Then, I run this to update the quantities:
UPDATE cataloginventory_stock_item s, catalog_product_entity p, temp t
SET s.qty = t.qty, s.is_in_stock = IF(t.qty > 0, 1, 0)
WHERE s.product_id = p.entity_id AND p.sku = t.sku;
Using the above method I can update my inventory of ~1500 items in less than a second. Using dataflow it took over an hour!!!! I’d say that’s quite a nice improvement!
If anyone can point out any problems with this method I would really appreciate the input.
About Flat Catalog. I use 1.3.1 and tried to use dataflow as an expirement. Nothing is going to be faster.
Flat Catalog is to make search and work with categories faster but not the import or update.
Could not get that version of the script to work - even just running phpmyadmin on a copy of the db, never mind encoded up in a ‘script’. Since I have prices and other guff to get right I used a few more calls. If you do have a working version of the script or detailed instructions then that would be handy.
