Resolved 8 hours to import a 4mb file with 17 fields and 12500 lines

Hello,

So with that new information, is there a way to track the actual import time?

We need to check your latest debug log. You can increase Number of lines in log file from CSVI Dashboard >> Click Options >> Site tab and run the import again. Debug log will also tell us if the time has improved with the select query which was taking 3 seconds previously.

Any other information that you can provide to help with this process is greatly appreciated.

Can you also post your import file? We will run the import on our local server and check if we can reproduce your issue. You can send it as an email or in a private message.

Hi Roland,

I did run a new cron job yesterday. Of course the log files are only showing a portion of the import. I have no idea how long it really took to import. The dashboard shows:

Action Action type Template name Start End User Records Run cancelled Debug log
Import Product Product File Import Optimized 01-04-2019 16:49:01 01-04-2019 16:49:02 12489 No Show | Open | Download
Import Product Product File Import Optimized 28-03-2019 10:58:01 28-03-2019 10:58:01 12489 No Show | Open | Download
Import Product Product File Import Optimized 27-03-2019 10:58:01 27-03-2019 10:58:01 12489 No Show | Open | Download

I know the 3/27/19 import took 8 hours because I was here watching it on the server. Last night I let it run.

I did index the SKU column of the sdo2301_j2store_variants table.

So with that new information, is there a way to track the actual import time?

I will look further into the site tables and optimization.

It is our server, I will talk to the server manager on this issue.

Any other information that you can provide to help with this process is greatly appreciated.

Hello,

Well I don't feel like we are beating around the bush here. I have given you a clear description of how the situation is and I stand by that it is not a magic fix in CSVI alone.

Your link to the Speed up Product Import is for VM. Not J2Store. Doesn't really help me.

I did say that it is for VirtueMart and only that a similar fix may be needed for J2Store. I never said this is your fix.

We have identified one issue already, that is the select Tharuna pointed out. At this moment, this takes around 2,5 hours of your import. As CSVI we just ask the database for some data and then CSVI has to wait 3 seconds before it receives an answer. A first step would be to add an index on the sku column of the sdo2301_j2store_variants table. How this is done, is explained in the tutorial I linked earlier. This has nothing to do with how CSVI works but hopefully speeds up the answer CSVI is waiting for. That is a first step we can take.

As for optimizing the database server, I have no knowledge of that as I am not a database administrator. So that is something you may need to ask your hosting partner to take a look at. Perhaps they can see some bottlenecks.

The partitions missing in your screenshots, you don't need the partitions, it is the indexes we are looking at.

Be aware, once the import is up-to-speed, your site can be slow because of the queries done by the extensions you use for retrieving the stored data. You may need to do optimizing in that area as well. That is just a heads up not saying that it will happen but could happen.

Also, I am not getting my email notification. What email should I be whitelisting?

Emails are coming from contact@csvimproved.com

Well, I definitely need some help. Your link to the Speed up Product Import is for VM. Not J2Store. Doesn't really help me. I have several questions but feel like I need your help to keep moving forward on this as the project isn't going to stop since we are already vested in quite a bit of time and money to get to this point.

What is the best way to move forward with your help. the J2Store folks are putting this issue on your component. Seems someone is always pointing a finger to the other. That doesn't help me.

Below is the j2store products table and indexes. Note: No partitioning defined!
Also is the com_content for the Joomla Articles where the J2Store data is also published as well as the J2Store-products-quantity

Also, I am not getting my email notification. What email should I be whitelisting?

Please advise.

Hello,

Just want to add my 2 cents here. This is may be totally unacceptable to you but it is not only a CSVI issue. CSVI is dependent on how your database performs. If CSVI sends a query and gets a response after 3 seconds, there is nothing we can change codewise to speed up your database server. The issue lies in the database at that point. If the processing of some value in CSVI itself takes, 3 seconds, then we need to look at the code on how to improve that. In addition to this it might also be a case of the J2Store database structure setup not designed for mass imports. A similar issue exists for VirtueMart if you look at the VirtueMart speed up product import. A similar change may be needed for J2Store. Do know that adding indexes is not a magic trick as in, adding more and more indexes will speed things up, the wrong indexes can also slow things down.

It is almost a jigsaw puzzle that needs to be put together and not just a change of code in CSVI.

Of course we will help to try and figure this out.

This is a sample of the import file 20 lines ISI-Items-19-03-22-20-products.csv

Here is the result:

localhost/ivmgmt_sample7/sdo2301_j2store_variants/ https://whm.ivhost.org:2083/cpsess7583644161/3rdparty/phpMyAdmin/db_sql.php?db=ivmgmt_sample7
Showing rows 0 - 0 (1 total, Query took 0.0188 seconds.)

SELECT * FROM sdo2301_j2store_variants WHERE `sku` = '0000-6817-20'



38437 23266 1 0000-6817-20 Please Call for Assistance
NULL
standard 0 {"product_css_class":""} 0.00000 0.00000 0.00000 0 0.00000 0 962 0 0 0
NULL

NULL
0.00000 0 0.00000 0 0.00000 0 0
NULL
0 0

Hello,
Checking your debug log, there is a select query which takes 3 seconds for each record and so for 3000 records its 9000 seconds which comes to around 2.5 hours. We now need to check why this specific select query is taking so long. Can you run the below query directly in your phpMyAdmin and see how long it takes?

SELECT * FROM sdo2301_j2store_variants WHERE `sku` = '0000-6817-20'

I am not seeing much improvement here. The cron job forecast is not better. I've tried 2 imports 1st ran for 2 hours - I got 3000 lines imported, then I killed the job. Deleted data from the tables and restarted with a new test and got 800 in 1/2 hour. This is totally unacceptable. I could use some help here.

Attached is the last cron log file com_csvi.log.1.zip com_csvi.log.2.zip com_csvi.log.3.zip

It was a manual import. I will test a cron job today.

I will test the cron job and get back to you. Images were uploaded prior and only the field with the image path reference was imported.

Hello,
Also, are you trying to import external images? Can you post the debug log and sample of import file to check? Importing external images could be one cause for long import hours. Debug log will tell us what is taking so long. Please post it to check. To get the debug log check How to collect debug information? document.

Hello,
8 hours for processing 12500 lines is way too much of time. Did you run the import manually or using cron? Setting the import to run using cron should be faster. Did you try that?

Is there a way to import directly via MySQL to this?

You cannot take values from one file to multiple tables using direct MySQL import. CSVI import processes the data and takes them to relevant tables in MySQL. You just need to find why your import is taking too much of time and a way to run it faster.