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

I have sent a support ticket to the host with log files and screenshots. Hopefully, they can help here. I'll let you know.

As we discussed before in my import I have a product id. That id is coming from the inventory database. The images are named based on that product ID. Can you tell me if that product ID on the import is being used after the import? Could this be causing the issue? I don't see where that id is in the J2Store nor the Joomla article ID. Where is that going?

The field product_id is for linking a product to all its related details tables, tables like #__j2store_variants, #__j2store_productfiles, #__j2store_productimages, #__j2store_product_filters etc. This product_id is the value of primary key field j2store_product_id which is an auto incremented value from #__j2store_products table. So the process goes like this, CSVI checks for a product in database using sku field. If the product is not available with the given sku, it inserts the product and generates a new j2store_product_id. This j2store_product_id will be used as product_id for other tables during import. If a product with sku already exists, CSVI reads the value of j2store_product_id from the #__j2store_products table and assigns it to product_id. So even if you import your product_id, CSVI will only use the value of j2store_product_id as product_id which is retrieved using sku field. That is the reason you are not seeing id from your import file anywhere in your tables. Hope it is clear. [color=red]Clear as mud [/color]


The query I ran didn't process anything. How can I know if it is useful for a time test?

It did not process anything because a row with the asset_id 236576 is already there. The time taken for the query is seen in your screenshot as (Query took 0.0007 seconds).[color=red] OK[/color]

I don't understand how on your server it can be running fine and fast, and not on 2 other servers with the identical website.

You did not answer Roland's question, Are these 2 servers are of different hosting? [color=red]YES[/color]

Can you think of any specific question to pose to the server staff to be looking for? This is like a needle in a haystack.

You can post the select query and show the screenshot of the log to explain the select query is taking 3 seconds to 5 seconds to process. [color=red]OK[/color]

Hello,

As we discussed before in my import I have a product id. That id is coming from the inventory database. The images are named based on that product ID. Can you tell me if that product ID on the import is being used after the import? Could this be causing the issue? I don't see where that id is in the J2Store nor the Joomla article ID. Where is that going?

The field product_id is for linking a product to all its related details tables, tables like #__j2store_variants, #__j2store_productfiles, #__j2store_productimages, #__j2store_product_filters etc. This product_id is the value of primary key field j2store_product_id which is an auto incremented value from #__j2store_products table. So the process goes like this, CSVI checks for a product in database using sku field. If the product is not available with the given sku, it inserts the product and generates a new j2store_product_id. This j2store_product_id will be used as product_id for other tables during import. If a product with sku already exists, CSVI reads the value of j2store_product_id from the #__j2store_products table and assigns it to product_id. So even if you import your product_id, CSVI will only use the value of j2store_product_id as product_id which is retrieved using sku field. That is the reason you are not seeing id from your import file anywhere in your tables. Hope it is clear.

The query I ran didn't process anything. How can I know if it is useful for a time test?

It did not process anything because a row with the asset_id 236576 is already there. The time taken for the query is seen in your screenshot as (Query took 0.0007 seconds).

I don't understand how on your server it can be running fine and fast, and not on 2 other servers with the identical website.

You did not answer Roland's question, Are these 2 servers are of different hosting?

Can you think of any specific question to pose to the server staff to be looking for? This is like a needle in a haystack.

You can post the select query and show the screenshot of the log to explain the select query is taking 3 seconds to 5 seconds to process.

This is still greek to me at this point. My host doesn't know what is causing the delay. I don't know what is causing the delay either.

As we discussed before in my import I have a product id. That id is coming from the inventory database. The images are named based on that product ID. Can you tell me if that product ID on the import is being used after the import? Could this be causing the issue? I don't see where that id is in the J2Store nor the Joomla article ID. Where is that going?

The query I ran didn't process anything. How can I know if it is useful for a time test?

Sorry - this is all a bit over my head at this point.

I don't understand how on your server it can be running fine and fast, and not on 2 other servers with the identical website.

Also not getting my email notifications on your replies again either, thus the reason for my delay in responding.

Can you think of any specific question to pose to the server staff to be looking for? This is like a needle in a haystack.

Hello,

I'm not really clear on this table or how it is propagated via J2Store or CSVI Improved.

This is because you are using J2Store, which uses Joomla articles for products. The Joomla articles uses the assets table to determine ACL (Access Control List). That is why this table must be updated.

it is making an asset for each SKU item

That is correct, because each SKU is an article and each article has ACL in Joomla.

I didn't drop the table prior. Should I have?

Definitely do not do this, you will break the complete site.

As per your screenshot, the query is instant as it is for me. What does the webhost say about the delay?

Here is a screenshot of the result.

I'm not really clear on this table or how it is propagated via J2Store or CSVI Improved. It looks auto-generated to me. Upon browsing it is making an asset for each SKU item. Does this not happen on your end? I didn't drop the table prior. Should I have?

Hello,

So I have analyzed the new log and something may have given some clue. At some point the asset_id is updated with this command:

UPDATE `sdo2301_content` SET asset_id = 236576 WHERE `id` = '236290' 

Taking the log from this particular part:

2019-04-11	14:11:58	1	[DEBUG]	Run the query to store Joomla content

2019-04-11	14:11:58	1	[DEBUG]	Query for Content

2019-04-11	14:12:02	1	[DEBUG]	Executed store

2019-04-11	14:12:02	1	[QUERY]	 UPDATE `sdo2301_content` SET asset_id = 236576 WHERE `id` = '236290'

At 14:11:58 the UPDATE is executed and control comes back to CSVI at 14:12:02 and that is when CSVI is able to write the log line with the actual query.

Looking at the local copy I have of your site, the assets table has over 220,000 rows in it. Although this is not a lot in database terms, it could mean it has something to do with the available resources the database has.

When I run this query

 UPDATE `sdo2301_content` SET asset_id = 236576 WHERE `id` = '236290'

on the local copy it is instant. Is that also instant when you run it via PhpMyAdmin?

Hello,

This being the case on 2 completely different servers is quite frustrating.

Is this at different hosting companies as well?

Here you go. I hope this is helpful. Thanks again for your continued troubleshooting. This being the case on 2 completely different servers is quite frustrating.

Hello,
With the attached patch file, we have added as many log messages as possible between the select query and the update query so debug log can help us find the delay. Can you load the attached patch file and run import on your server? Post the debug log of your latest import.

Hello,

Even if the images are not being uploaded, can you confirm that this is not the issue?

Confirm that this is not the issue. The images is not the problem here because they are processed as last, so this is after the 3 second delay in your log files. You can see that for yourself as well. This is identified in the logs with the line [DEBUG] Process file:

This is not a reliable test.

That is based on the images that are processed as last?

So with the website backup that I provided you and all the images, are you telling me that you had no delay on your server importing?

Correct. Attached is the debug log of the 30 lines import file. You can see the images are processed here.

I am just at a loss at this point.

We are going to take a look to see if we can find anything between the select and update (those 3 seconds). As there seems to be no support from your server people, we are flying pretty blind here.

Hello,

The 20 line import was instant, but there are no images on your server. This is not a apples to apples test. I think the images are being processed on my server and that is what is taking all the time. Even if the images are not being uploaded, can you confirm that this is not the issue? The log file attached for my test on your demo server shows zero image processing lines vs. my 20 line import log file. This is not a reliable test.

I really need to get this resolved.

So with the website backup that I provided you and all the images, are you telling me that you had no delay on your server importing?

I am just at a loss at this point.

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.

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,

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.

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.

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]

OK, thanks for confirming this issue.

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.

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.