Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 17
Poll
Do you think the product import in Magento is too slow?
Yes, it should be improved! 613
No, it’s fine. There are more important things to work on. 19
What are you talking about? 1
Total Votes: 633
You must be a logged-in member to vote
Import Speed / Performance optimization
 
gunner1095
Jr. Member
 
Total Posts:  6
Joined:  2008-12-30
 

Hi all,

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!

Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
gunner1095
Jr. Member
 
Total Posts:  6
Joined:  2008-12-30
 

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?

THANKS!

 
Magento Community Magento Community
Magento Community
Magento Community
 
brendanb
Mentor
 
Total Posts:  1093
Joined:  2008-07-16
London, United Kingdom
 

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.

brendan

 
Magento Community Magento Community
Magento Community
Magento Community
 
jeff.d
Sr. Member
 
Total Posts:  103
Joined:  2008-05-26
 

Hi,

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.

Jeff

 
Magento Community Magento Community
Magento Community
Magento Community
 
gunner1095
Jr. Member
 
Total Posts:  6
Joined:  2008-12-30
 

Thanks for the advice!

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
jeff.d
Sr. Member
 
Total Posts:  103
Joined:  2008-05-26
 

You also have to update cataloginventory_stock_status. There is a quantity field and an in/out of stock flag if I remember right.

 
Magento Community Magento Community
Magento Community
Magento Community
 
gunner1095
Jr. Member
 
Total Posts:  6
Joined:  2008-12-30
 

Awsome, thanks! I missed that…

So the updated query looks like this:

UPDATE cataloginventory_stock_item s, cataloginventory_stock_status ss, catalog_product_entity p, temp t
SET s.qty = t.qty, s.is_in_stock = IF(t.qty > 0, 1, 0), ss.qty = t.qty, ss.stock_status = IF(t.qty > 0, 1, 0)
WHERE s.product_id = p.entity_id AND s.product_id = ss.product_id AND p.sku = t.sku;

Query took 0.0914 sec <- That’s a far cry from the 60+ minutes it takes to do this with dataflow.

Thanks for the help everyone!

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

Thanks from me too.

Using your examle i made a script that updates all my 8312 products wiith all required and some custom fields less then in 9 min.!!!

Awesome!!!

If somebody need - mail me.

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

Made some improvements. Now update time is 1min 23 sec.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Lynx
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2009-01-09
Pretoria, South Africa
 
michael_g08 - 16 March 2009 08:11 PM

Made some improvements. Now update time is 1min 23 sec.

Would be interested in being able to use your script

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

Frak! Cant attach file to post it.

Who wants to use the script just mail me.

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

Checkout the latest version 1.3.0 - Flat catalog seems to be promising a lot. Maybe import is also faster.

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
RevolutionPoet
Jr. Member
 
Total Posts:  9
Joined:  2009-04-03
 

@michael

would you mind posting your script here?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Discovery
Enthusiast
 
Total Posts:  767
Joined:  2007-12-29
 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

@Discovery

Sorry but I can’t manage what version of the script You are talking about......

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 17