Two slow queries detected about product-import

[haelbichalex]

@patrick-bigbridge I'm sorry, was quite busy the last weeks. Your change to exec the query only once did the trick for me 👍 Successfully importing all my products in ~30 hours, so I'm closing this issue.

Thank you for your awesome work!

[patrick-bigbridge]

Hi!

It's good that you ask. The query that you mention loads all attribute option values into memory. It's executed once every batch (500 products) in OptionResolver::refresh().

I suspect that executing the query mainly takes a lot of time because all option value data has to be transferred from the database to the PHP process. I think you may have hundreds of thousands of option values. If you execute the query in a mysql client (replacing the '?' by 4) you can see.

There are two types of data that the library reloads every batch: category information and option value information. The reason: it is possible that other processes than the library itself change categories and option values while the importer is running. It seemed like a small overhead. Apparently it is not always so.

I am going to think about a good solution for your problem. In the meanwhile, for my information, can you tell me something about your import: what types of products do you import? Do you import mainly simple attributes or more complex ones as well? How many categories? Do you support multiple store views? And how much time does the complete import currently take? I'd like to collect some reference information. :)

[haelbichalex]

Thanks for your quick answer.
Executing the query in phpmyadmin shows 174097 results in 0.0013s.

---

Information about the import:

what types of products do you import?

Only simple products

Do you import mainly simple attributes or more complex ones as well?

I have 54 custom attributes, 12 of them select, 5 multiselect

How many categories?

No categories in the import. 1371 categories in the db.

Do you support multiple store views?

No.

And how much time does the complete import currently take?

Can't answer that question precisely. We ran an import without any attributes, just simple products which took about 2 days. Then added the attributes and the import now would take a view weeks.

[patrick-bigbridge]

Thanks for informing me about your import. It helps me to understand your situation better.
You have quite a number of option values, so it takes some time to load them all (I think phpmyadmin adds a limit to the query before executing it). This is reason for me to not load all of them each batch again, but just once. You will still have the slow query (0.6s) once every import, but since it occurs only once this is fine.

I made another change that helps you to import many option values. The library used to create the correct sort_order for each new option. I changed this, because this process takes quite some time (looking up the highest sort_order present for the attribute, in an unindexed column). I fixed this sort order on 10000. This should not cause any problems, but will give some extra speed.

New release is 1.2.3

You may also want to check out this text I just wrote for some tips to speed up an import.

Just let me know if you have any more problems.

[patrick-bigbridge]

@haelbichalex I am just curious how you are doing.