× Looking for a useful answer?

Read this first and make sure you checked all steps: csvimproved.com/support/getting-started

Don't just say it doesn't work but describe in detail what is happening and add the following information:
* Version of the extension you are using
* CSVI version (Latest is not a version number)
* Sample of the file being imported
* A copy of the template you are using. Use the maintenance menu to create a backup of your template
* Collected debug information

Without this information you won't get a useful answer. If no debug log is attached, you will get a request to post it.

Where to get the debug information?

A tutorial ( csvimproved.com/support/questions-and-answers/390-how-to-collect-debug-information ) has been written to help you collect your debug information.

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

More
1 month 3 weeks ago #60358 by tharuna
tharuna replied the topic: 8 hours to import a 4mb file with 17 fields and 12500 lines
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'

Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory

Please Log in or Create an account to join the conversation.

  • cpointcc
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
More
1 month 3 weeks ago - 1 month 3 weeks ago #60364 by cpointcc
cpointcc replied the topic: 8 hours to import a 4mb file with 17 fields and 12500 lines
This is a sample of the import file 20 lines

File Attachment:

File Name: ISI-Items-...ucts.csv
File Size:8 KB


Here is the result:

localhost/ivmgmt_sample7/sdo2301_j2store_variants/ 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
Attachments:
Last edit: 1 month 3 weeks ago by cpointcc. Reason: added sample import file

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #60365 by rolandd
rolandd replied the topic: 8 hours to import a 4mb file with 17 fields and 12500 lines
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.

Kind regards,

RolandD

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory

Please Log in or Create an account to join the conversation.

  • cpointcc
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
More
1 month 3 weeks ago - 1 month 3 weeks ago #60397 by cpointcc
cpointcc replied the topic: 8 hours to import a 4mb file with 17 fields and 12500 lines
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.
Attachments:
Last edit: 1 month 3 weeks ago by cpointcc.

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago #60398 by rolandd
rolandd replied the topic: 8 hours to import a 4mb file with 17 fields and 12500 lines
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 This email address is being protected from spambots. You need JavaScript enabled to view it.

Kind regards,

RolandD

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory

Please Log in or Create an account to join the conversation.

  • cpointcc
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
More
1 month 2 weeks ago #60400 by cpointcc
cpointcc replied the topic: 8 hours to import a 4mb file with 17 fields and 12500 lines
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.
Attachments:

Please Log in or Create an account to join the conversation.