Resolved How to speed up an import of 2.5 million records

Hello,

You can be sure you are in an elite group that actually imports so many lines. There used to be one other importing a million lines and this required tuning on all levels from CSVI, the webserver and the database server. A regular hosting setup is not going to be ideal for this. What we found out at a certain point was that CSVI was pushing so hard all the queries where queued at the database server.

First of all, you need to look at what data is being imported. It is unclear from this thread which data you are trying to import. Second, you need to understand that the VirtueMart product import is a heavy import in a sense that it tries to do a lot in one go. This doesn't help performance either. Third, debugging should also be turned off. Fourth, you want to turn off all indexes because that will slow down a lot as well as the database tries to index while you are still importing. The only index you do want to keep is that of the field you are basing your import on, most likely the product_sku field. Fifth, breaking up your import into several imports may help as well. So think of importing the images after the basic product information has been imported.

I can guarantee you that with a default hosting setup you won't get an import of 2.5 million lines done in any fast time, they are simply not tuned for that. This is not a CSVI problem but more a problem of infrastructure setup. So it takes tuning on all different levels to get a decent import time.

I very much appreciate your reply. My in-house programmer also stated the above optimization criteria. I am going to try a smaller import for demonstration purposes and modify some of the options per your recommendations. Can you tell me if the indexing is turned off in the template?

Hello,
Indexing is something which is added to your database table fields and not with CSVI templates. If you go to your PhpMyAdmin and look for #__virtuemart_products table and on Structure page you would see a section for added Indexes under table structure. You can also simply disable the index by running a query and turn it on again once the import is done.

Yes, my programmer told me that after I wrote that post. I will test again with that turned off, however, I am needing this to me much faster than my preliminary tests are showing.

I'll let you know how the import when with indexing off.

I have run another test and I am only getting mild improvement on the number of lines imported within an hour. Do I need to include all these tables as well for the disable indexing:

hgmce_virtuemart_products 48,497 MyISAM 8.7 MiB
hgmce_virtuemart_products_en_gb 48,496 MyISAM 4.2 MiB
hgmce_virtuemart_product_categories 48,496 MyISAM 2.4 MiB
hgmce_virtuemart_product_customfields 48,496 MyISAM 5 MiB
hgmce_virtuemart_product_manufacturers 48,496 MyISAM 1.9 MiB
hgmce_virtuemart_product_prices 48,496 MyISAM 8 MiB

Hello,
Yes it would be better to include all those tables but you should also remember that turning off only indexing is not going show any drastic improvement. You also need to look at your server environment as Roland explained in his reply.

Hello,

Tell us, which import are you actually running? Can you run an import with 10 records, get the debug log for that and post it?

As far as indexes go, generally you don't want any with such large import except for the fields used as a lookup field, like the product_sku. Again, this will need to be analyzed from the database where things are taking their time.

We had a requirement to import 1.6 million products into one of our sites and eventually rolled our own import routine. It's not as durable as CSVi and only works on product records, but it is faster. We continue to use CSVi for all our other sites as well as the huge category import for this site.

Hello,
VirtueMart Product import is a huge one, it does everything in one import. The major one is downloading images from remote server and linking it to the product. Splitting this import into sections could do good improvement in import time. Say like one import for importing only product basic information, other import to download image and link it to product, etc...You can create import templates according to the section to be imported and use the same file to run in all these templates.