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

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.

Lets wetransfer. I sent to contact@csvimproved.com

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.

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,

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.

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,

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.

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,

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?

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,

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.

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.

Images. there is no thumbnail generating option.

I see it is hidden because the Process images are set to No. This is an issue because we shouldn't be creating the thumbnails. We will fix this and supply a patch once fixed.
Debug is not turned on. Only enable logging. I needed to see this for error troubleshooting. | I have uploaded the patch file. I will see if that improves anything. Regarding the rest:

A little miscommunication, the logging is what we call the debug log. Of, course this is turned on as we are testing but when doing a live run, when this is turned off, the import will be faster too as it has a lot less of data to write. That is the only thing I was pointing out In my import I have a Product ID. That ID must stay the same.

Which ID is this? Is this your own ID?
I don't see anywhere where it is being added. Yes this is our product ID. I want to keep it the same. It is in the import file in the first field

Have you mapped this field to a J2Store field? It looks like the product ID is being auto incremented. See attached:

Correct, this is the J2Store database schema as most tables always have a primary key which is auto-incremental to ensure you can target a specific entry. This is the J2Store internal ID and is managed by the database.
How can I insure the integrity of the Product ID to be based on the import file data?

That is what we use the SKU for. An SKU must be unique as that is what the name is as well. Stock Keeping Unit, an identifier that identifies a specific product. Based on the SKU we will check if the product exists, so yes we will update the product, if it doesn't exist we create the product (unless you have set the option not to create new products). Our SKU is in.

Hello,
Attached is the patch file mentioned by Roland for thumbnails not to be created when Process image is set to No. Load the patch file and you should not be seeing any more image processing in your import.

Hello,

Images. there is no thumnail generating option.

I see, it is hidden because the Process images is set to No. This is an issue because we shouldn't be creating the thumbnails. We will fix this and supply a patch once fixed.

Debug is not turned on. Only enable logging. I needed to see this for error troubleshooting.

A little miscommunication, the logging is what we call the debug log. Of course this is turned on as we are testing but when doing a live run, when this is turned off, the import will be faster too as it has a lot less of data to write. That is the only thing I was pointing out.

In my import I have a Product ID. That ID must stay the same.

Which ID is this? Is this your own ID?

I don't see anywhere where it is being added.

Have you mapped this field to a J2Store field?

It looks like the product ID is being auto incremented.

Correct, this is the J2Store database schema as most tables always have a primary key which is auto-incremental to ensure you can target a specific entry. This is the J2Store internal ID and is managed by the database.

How can I insure the integrity of the Product ID to be based on the import file data?

That is what we use the SKU for. An SKU must be unique as that is what the name is as well. Stock Keeping Unit, an identifier that identifies a specific product. Based on the SKU we will check if the product exists, so yes we will update the product, if it doesn't exist we create the product (unless you have set the option not to create new products).

Let me know if you have any further questions.

OK we will continue to troubleshoot the server optimization.

Cannot compare client projects, just no issues in general. This project are only one using J2Store.

Images. there is no thumnail generating option.
Create image name set to no,
Image name based on
Image name format -JPG
Process images - No

Debug is not turned on. Only enable logging. I needed to see this for error troubleshooting.

One main issue I have just discovered. In my import I have a Product ID. That ID must stay the same. I don't see anywhere where it is being added. It looks like the product ID is being auto incremented. THIS IS A MUST. How can I insure the integrity of the Product ID to be based on the import file data?

Hello,

If your product is completely dependent on the server optimization we need some criteria to start with.

We have no special requirements for the database server to run CSVI. When we send a request and get an answer 3 seconds later it might be because the database server doesn't have enough resources to process it faster. As I mentioned before, I am not a database admin, so I don't know how to fine-tune a database server.

No other clients have had issues with the server settings at this point until now with this project.

Are they all importing the same amount of data in the same setup using J2Store?

How or what can I change to avoid this?

In your template go to the Images tab and turn off the Create thumbnails, because that is now turned on. That should make a difference.

I was wondering, did you have the debug log enabled when you ran the full import? The debug log also takes time, so having that disabled when doing the full run will also make a difference.

Our server staff is asking if you have any specific optimization settings that are optimal for your program. The MySQL has not been optimized, but any input you have would be helpful. Its a fairly new server and tweaking it to fit this project is a new task. I did however optimize all the tables in the database this am.

No other clients have had issues with the server settings at this point until now with this project. If your product is completely dependent on the server optimization we need some criteria to start with.

I have changed the char count to 40 on the sku in the j2store_variants table.

The images: I am not importing images. We are only referencing the path in the import. They are already pre-sized. How or what can I change to avoid this?

Hello,

The logfile at least points out that the index on that SKU field didn't do anything because we are still waiting for 3 seconds to get an answer. What I do spot now in your screenshot is that your SKU index is 191 characters long. I am guessing you don't have SKUs that are 191 characters long Reducing this number to a value that is more representative for your SKUs, perhaps something like 25 maybe, may help. Simply because there is less data to index and keep track off and you can get a faster hit.

It would be helpful if the server people can give any clue why this takes so long.

One other thing I noticed in the log is that you are resizing thumbnails, it may sometimes be useful to first run the product import and the image resizing in a separate import. This way you will have all the product info in place and the pictures come after that.

As far as sending the import file, the current has proprietary info in it.

Understood, no problem.

When I get the next import that will exclude that I will send it. It may not be today.

Thank you.

I don't see where to connect via private message.

You can click on our name to see our profile and there will be a button to send a private message. Alternatively you can also click on your own profile at the top right corner where there is a picture and it will have a link to the Private Messages area. To email, you can use contact@csvimproved.com.

I have attached the log file. It is inconclusive. As far as sending the import file, the current has proprietary info in it. When I get the next import that will exclude that I will send it. It may not be today.

What email do I send it too? I don't see where to connect via private message.