Import Configurable products on big catalogs

[barbazul]

I've just stumbled upon an issue when importing a small file into a large catalog using ImportExport.

The file contained 2 simple products and a configurable product associated to them via 2 attributes (size and color).

You can find the sample file in here: https://gist.github.com/barbazul/5349433

I imported the file in a catalog of about 40k products. The data validation went ok, but the actual import failed (mutely).

Debugging through the script I found the error to happen during an inventory event (which is weird on itself).

The whole path is the following:

1. Eventually the script gets to Mage_ImportExport_Model_Import_Entity_Product_Type_Configurable::_loadSkuSuperAttributeValues()
2. The foreach in line 233 fires a call to load on a Mage_Catalog_Model_Resource_Product_Collection filtering by product type equal to 'simple', 'downloadable' or 'virtual'
3. The call to load fires the catalog_product_collection_load_after event
4. CatalogInventory has an observer for that event: Mage_CatalogInventory_Model_Observer::addStockStatusToCollection()
5. The observer in turn calls Mage_CatalogInventory_Model_Stock_Status::addStockStatusToProducts() passing the loaded collection
6. The model will walk the entire collection (40k products at this time) and collect all product ids in an array, which is then passed, in line 479 to $this->_getResource()->getProductStatus($productIds, $websiteId, $stockId)
7. Mage_CatalogInventory_Model_Resource_Stock_Status::getProductStatus() takes the array and passes it to a were IN condition in a select element (line 116)

The result is a query with a WHERE clause like this:

SELECT 
[...]
WHERE 
    product_id IN ( [40k IDs] ) 
    AND stock_id = $stockId 
    AND website_id = $websiteId

Conclussions

1. I don't think there is any need to load any inventory information at all just to assign associated products to a configurable
2. Even if it just "didn't hurt" to have inventory information at that time, there is something seriously wrong in that query
3. The whole issue is being triggered by a method that is attempting to load the full mapping of simple products to their corresponding assigned options in the supper attributes. In many cases, that means the whole catalog will attempt to be loaded into memory. Is that safe or even necessary for the task being addressed? It looks like it won't scale well on large catalogs

[s-hoffman]

@barbazul,

I am currently working with a company that has 700K Simple Products and 3 Super Attributes for most of them.
Product Import fails due to Memory overflow during the Configurable product phase. I have increased memory to 6GB or so, and it runs through that also.

The SQL call time also spikes at about 75K. The exact number varies so I am assuming that the DB has to store some of the Joined information on the disk when the total data exceeds the memory limit.

Due to the above I have written a short Module rewrite for the Configurable product that Fixes the bug.
When I test the full import, I aim to Patch ../Configurable.php and push the change.

I am working with Magento EE 1.12 but ../Configurable.php is the same in both versions so it should carry over.

[barbazul]

@s-hoffman I'd like to see that patch!

Meanwhile I spent a little time thinking about this. I think the problem is within the CatalogInventory observer which very naively assumes it is being called using a small, paged, subsection of the catalog. I think the whole query should be rewritten to assume the worst, though I am not sure how

[s-hoffman]

@barbazul I have pushed the change to my fork.
I do not have Mage 2 configured and I implemented this version slightly different then the rewrite for EE1.12.
It should work though. As you are trying to import your 40K products you can test it with little effort.

Then I can create a Pull request.

[barbazul]

Looks very promissing. I will give it a try and report back

[s-hoffman]

Any feedback yet?

[barbazul]

Sorry. Havent found the time to check this out yet.
I'll be toying with this during the weekend
El abr 19, 2013 5:55 PM, "s-hoffman" notifications@github.com escribió:

Any feedback yet?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/278#issuecomment-16688232
.

[barbazul]

I just tested in 1.7.0.2 And the ammount of ids used in the IN clause dropped from 48778 to 4 (I was creating a configurable with 3 options). The import run well, however the configurable product did not get associated simple products.

I am going to try again after refreshing all indexes (which could take several hours)

[barbazul]

I can confirm this works like a charm in 1.7.0.2. The error I found before was entirely human (misstyped the name of the options)

I don't have any magento2 instances set up with such a large dataset though

You should definitely prepare a pull-request for this with tests 👍

[barbazul]

@s-hoffman I strongly recommend you submit a pull request so this gets fixed in next release

[barbazul]

Ignore my previous comment. I just found it: #284

[verklov]

Fixed by #284. Closing this issue.