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

I just did a manual file test import. 19 lines. 3 minutes to import after the patch. This is still not a good result.

See log file: com_csvi.log.8.zip

Also now none of my images show up in the display. I have the fields in the import, the files are pre-uploaded to the server. Why isn't the path in the field upon import? I'm so confused!

Maybe I'm not clear on the language of image processing. This import is not uploading any images, only the file path.

Please advise.

Hello,

I have uploaded the patch file. I will see if that improves anything.

In the short file you did it won't have much impact but I can see that the patch is working because the thumbnail is no longer being generated.

Yes this is our product ID. I want to keep it the same. It is in the import file in the first field

Our SKU is in.

These two belong together. As I mentioned before we use the SKU to identify products, this also means that the product_id is ignored because you cannot have 2 unique identifiers, it is one or the other. Using the SKU the database still controls the product_id values, if you want to use the product_id field you must take control of the IDs and ensure they exist before import. The reason for this is that Joomla only does updates on primary keys. If you just need this ID be linked to the product you may consider adding it as a custom field. Either on the article or on the product as we are dealing with Joomla Articles and J2Store products here. If you decide to go ahead to force your ID as primary key, the field you need is j2store_product_id. Out of curiosity, why using 2 unique identifiers for a single product?

This is still not a good result.

Yes because the original issue is still not resolved and in some cases is even worse. That 3 second query is taking 5 seconds in some cases. Is the database log not saying anything about this query?

We have gone ahead and are doing some tests with our own file with 25,000 records but no final conclusions yet. To do some local testing, I saw you already posted a sample CSV file earlier. Can you post a backup of your import template as well? This can be done by going to Maintenance -> CSVI Pro -> Backup templates and select your template. This file can be uploaded here.

I think I have spotted something that can be optimized but want to be sure to check it against your template.

While typing this I see you have updated your post with another question.

This import is not uploading any images, only the file path.

Image processing is set to No, so no thumbnails are created or set, only the filepath is stored. If you want the thumbnails processed you can set Process images to Yes on the Image tab. The thumbnail options will show and CSVI will generate the thumbnails. I hope it makes sense that when Process images is set to No, it doesn't process images.

Geez, sorry for the delay. I just got your email notification that you replied.



I have also added a test file with 30 products.

I will test again with the images set to process on 18 fields, I added alias as a test for something but have abandonded.

Hello,

Thank you so much for the template and test file. We restored the template on 2 local computers and 2 online servers, and used it to import the 30 line file without any modifications. In all 4 instances the 30 lines were imported in 1 second. The reason I also used an online server is because they are different setup than local computers. So I wanted to see if we could reproduce the 3 second delay we see in your debug log. No such issue. You can even see it for yourself if you load the template and file on our demo server.

A few months back we did a VirtueMart to J2Store migration using CSVI and have not seen such an issue either. We know of other users that use CSVI with J2Store and have not seen this issue either.

This brings us back to the question we had early on, why is the database taking 3 seconds to answer the request? The server people can't find anything related to this?

Here is my cron job log file from the server. I started it at 3:35 pm and it finished at 2:39 a.m. I am not able to send the log file through here too big.
You cron finished at 2:39.

The server staff are backed up and my other resource is off for the next 3 days.

I guess my next option is to test this on the clients hosting??


-----Original Message-----
From: Cron Daemon [mailto:root@whm.ivhost.org]
Sent: Thursday, April 04, 2019 2:39 AM
To: tech@ivnet.com
Subject: Cron <ivmgmt@whm> /usr/local/bin/php /home/ivmgmt/public_html/sample7/administrator/components/com_csvi/helper/cron.php --key="timeisticking.2019" --template_name="Product File Import Optimized" --file="/home/ivmgmt/public_html/sample7/ISI-Items-19-04-...

Use --help to see the help information
Processing started...
Template Product File Import Optimized
Processed 12490 lines
Total Result Status
Content Updated: 12447
Joomla Content Error: 29
Product Skipped: 14
Product Added: 12447
Product Updated: 29
Product Image Added: 12476
Product image helper Incorrect: 2627
Variant Added: 12447
Variant Updated: 29
Import has finished

Hello,

I am not able to send the log file through here too big.

That is OK. As long as those 3-5 seconds for that one query isn't changing, the import will remain to take a long time.

Another thing we saw in the template is that the Time between imports is set to 30 seconds. This means that after every 1000 lines, a 30 second wait is executed. Our default value is 5 seconds. This is 5 seconds because on most hosting environments you only get 30 seconds per request. Since this is your own server, I guess it is not a problem. It only adds about 5 minutes of waiting to the total import. A drop in the bucket at the moment on the total import time.

I guess my next option is to test this on the clients hosting??

If you have a test site available on another server, it would be good to give it a run there. You can try with the 30 line file as well, no need for the full file. The 30 line file will also show if that 1 query is taking a long time there too.

I think it is ridiculous that I am having so much trouble with this. Every expert we have points it back to the 3 second delay but no one can seem to find the problem or the time to troubleshoot it. Last nights cron job was 13 hours. At this point I feel like I've made a bad choice.

I'm not sure where to turn now. I will try the clients server, but feel pretty discouraged as it is a shared hosting environment just like this one.

Hello,

no one can seem to find the problem or the time to troubleshoot it.

We are trying to help you as much as we can but troubleshooting your database server is not part of our service nor do I even know how to do this. This is on the assumption it is the database because it is the database query we are waiting for.

At this point I feel like I've made a bad choice.

If you mean a bad choice in chosing CSVI? If so, I am sorry to hear that but I really think that this issue is not caused by our code, rather that CSVI is exposing an issue in the server setup.

I'm not sure where to turn now.

We will still be here to help as much as we can. My first inkling is to setup a site with a different provider and sees how that goes.

but feel pretty discouraged

for what it is worth, I am not happy either that we are not moving forward. We have spent quite a few hours so far trying to figure out what is going on.

The last thing I can offer is that we get an Akeeba Backup of your site and run it on our development machine and see if we can reproduce the 3 second delay. That it may not be the database but something else on your site.

Hello,
no one can seem to find the problem or the time to troubleshoot it.

We are trying to help you as much as we can but troubleshooting your database server is not part of our service nor do I even know how to do this. This is on the assumption it is the database because it is the database query we are waiting for. * I was talking mostly about my server staff and db programmer.
At this point I feel like I've made a bad choice.

If you mean a bad choice in chosing CSVI? If so, I am sorry to hear that but I really think that this issue is not caused by our code, rather that CSVI is exposing an issue in the server setup. * I will try to not be too critical here. I'm more concerned that this may never get resolved and a lot of time and money wasted.
I'm not sure where to turn now.

We will still be here to help as much as we can. My first inkling is to setup a site with a different provider and sees how that goes. *Still not feeling this is the best next test. I may have less support with their 3rd party support
but feel pretty discouraged

for what it is worth, I am not happy either that we are not moving forward. We have spent quite a few hours so far trying to figure out what is going on. *I truly appreciate your time and troubleshooting.

The last thing I can offer is that we get an Akeeba Backup of your site and run it on our development machine and see if we can reproduce the 3 second delay. That it may not be the database but something else on your site. I'd like to do that. The back is 175mb though. How to get to you?

Hello,

There are several ways to do this, first is Wetransfer and the other is to email us FTP details to the folder where we can download the file.

Lets wetransfer. I sent to contact@csvimproved.com

That is the correct address but I haven't received the email yet. Not in spam or quarantaine or see any trace in the mail logs either. If you have the download link, perhaps email it directly.

HI Roland, The email content below shows this was sent: (I see now there is a typo in the email address!) I sent link via email directly a moment ago.

Files sent to
contact@csviimproved.com

1 file, 174 MB in total ・ Will be deleted on 11 April, 2019
Thanks for using WeTransfer. We'll email you a confirmation as soon as your files have been downloaded.


Recipients
contact@csviimproved.com


1 file
site-sample7.ivhost.org-20190404-084514.zip
Message
sample7 akeeba backup attached.

Hello,

Thank you for forwarding the link as you can see there is an error in the email-address. There is a double i where there should be 1 i No worries, I was able to download and restore the site locally. Running the same 30 line import file you posted earlier went without a hitch. I do not see the 3-5 second delay here on that query.

This makes it pretty definitive that it has something to do with the server setup since there is nothing in your website that influences this delay.

OK, thanks for confirming this issue.

Good day,

I transferred the website to the client's actual hosting in a subdirectory. The preliminary test shows 20 lines took 2 minutes to import. This is no improvement. I am totally screwed if we cannot find what is causing the delay. See the attached log file from this test.

Your last response was you did not show any delay for the test file. is this a dedicated hosting server? I'm curious as to what can be the culprit.

I'd love to test this on your demo server with my data and my files and see that this is only taking 1 second.

Here’s proof that the select statement is NOT the source of the delay:


Here’s where the delay is happening for each line… whatever is happening between the query (which we’ve proved to run just fine) and ‘Executed store’ … whatever than means … is the problem.



That last one didn’t actually do an update because the value had already been set. I changed the number just to verify the query is still performant… Here’s the results:

[attachment]484cc05689765742dfeea9fd256d6ece[/attachment]

Hello,

I transferred the website to the client's actual hosting in a subdirectory. The preliminary test shows 20 lines took 2 minutes to import. This is no improvement. I am totally screwed if we cannot find what is causing the delay. See the attached log file from this test.

The client's hosting is on same server or on different server? Also check the database type set, to do that go to System -> Global Configuration -> Server, is it set to MySQLi? If not, enable the MySQLi option.

I'd love to test this on your demo server with my data and my files and see that this is only taking 1 second.

Yes, please run the import with your template and import file. That way you can see there is no delay with select query.

Here’s proof that the select statement is NOT the source of the delay:

Here’s where the delay is happening for each line… whatever is happening between the query (which we’ve proved to run just fine) and ‘Executed store’ … whatever than means … is the problem.

Running the query in PhpMyAdmin is fine. It could be the database server which responds to the query is taking takes time. You need to check on that with your hosting.

Let us know how your tests goes on our demo server.

Hello,

In case you do not know, our demo server is at https://demo.csvimproved.com/administrator You can import your template using the Maintenance menu and then run the import with your test file. That is how I did it before.

I will check it out. The client's site is on a completely different server with a different host. My server is where I am developing the site. The server is set for MySQLi.

I had thought this am that may have something to do with the Asset ID. I am submitting that in the import. The images are named based on that number, however, the path is included in the import file. Are you sure that the process images, even though the files are not being uploaded in the import aren't making this delay?

My client is beyond frustrated at this point as well as me.

I don't see where there is an option in the demo site to import template under the CSVI option

Hello,

I don't see where there is an option in the demo site to import template under the CSVI option

You used Backup to make a backup of the template. Restore is to restore a template. So the option Restore is visible in your screenshot, you can use that.

whatever is happening between the query (which we’ve proved to run just fine) and ‘Executed store’ … whatever than means … is the problem.

We are looking into this as well. Perhaps we can add more logging to see what happens.

My client is beyond frustrated at this point as well as me.

As I mentioned before, we are doing as much as we can to help you. Since this problem only manifests itself on your setup, there isn't very much concrete we can do until we know what the real issue is.