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

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,

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,
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.

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,

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

Is this at different hosting companies as well?

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?

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,

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?

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,

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.

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]

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

Here is the hosting company response: " I have checked both of those logs files and unfortunately I am not seeing any real issues or errors it just looks like its importing info to the database and there really isn't any issues or errors as far as I can tell."

I did send back the screenshot of the delayed import image explaining that there are not errors, it just takes 8-12 hours to import a 4mb file.

I don't know what else to do.

Next response:

Can you please try to import a database using PHPMyAdmin? You should be able to access this from the "Databases" Section in cPanel, and then
use the "Import" function as outlined here:
https://docs.phpmyadmin.net/en/latest/import_export.html

If you are unable to do so, if you can provide details, and access for us to test this script we can attempt to compare the two locally.

I don't even know if this is possible. Is this an option to import directly?
Both of those logs appear to show similar execution time as com_csvi.log.2-demo-server-import-4-12-19 shows a start and end time of:
2019-04-12 18:59:11 --- 2019-04-12 18:59:23

With 19 actions having been taken over about 12 seconds.

com_csvi.log.23-owh-server-import-4-12-19 shows a start and end time of:
2019-04-12 19:00:55 --- 2019-04-12 19:01:11

taking 19 actions in about 16 seconds.

Without knowing what the Demo servers configuration is, this difference of ~4 seconds would
easily be explained by server differences, such as if the account from the demo service has more resources allocated to it, or has a different
PHP configuration.

In order to lessen the differences between these two servers I would recommend using the MutliPHP INI Editor, and MultiPHP Manager available through cPanel
to ensure that the two PHP configurations match as closely as possible.

So overall I am still not getting anywhere with this.

Hello,

I have been doing yet another round of tests and was still unable to reproduce what you are seeing. So I re-read this thread again and again and then one remark you made caught my eye. You did run a query in PhpMyAdmin but nothing was updated. So I checked the test imports I have done but I never saw that particular query in my logs but it showed up in your logs.

After numerous more tests I finally figured that you were doing new inserts instead of updating existing products. So I emptied the database of J2Store products (this is an option in CSVI) and ran the 30-line test import file again. Low and behold, I am seeing that 4 second delay. After that I ran the import again, doing an update of existing products and there is no delay. After that I emptied the database again, ran the import and see that 4 second delay. Now I have a consistent way of reproducing it, I can look further into it.

Once I figure this out further I will update you.

Hello,

As mentioned in my previous post I was able to reproduce the issue and now have a clear understanding of what is going on. Let me start by saying, this is not a bug of some sort but a result of the large dataset and how data is stored by Joomla.

Since I was now able to reproduce the issue using your test site I could setup debugging and walk through the code as the import was taking place. So the delay happens when importing a J2Store product. Since J2Store doesn't store products itself but uses Joomla content for that, a Joomla article is created, which holds the product details.

Joomla articles are subject to the Joomla access control system. This system controls who has access or not to a particular item. So as CSVI asks Joomla to store the article, Joomla does a check for the access control called an asset. When importing a new product, Joomla will create a new placeholder for the asset. Since the assets are stored in what is called a tree which uses left and right values to determine the place of an item, Joomla is creating this space by executing these 2 queries:

UPDATE sdo2301_assets

SET lft = lft + 2

WHERE lft > 252493

UPDATE sdo2301_assets

SET rgt = rgt + 2

WHERE rgt >= 252493

The first is to make space for the left value and the second is to make space for the right value. This way it creates a hole in the tree that will be filled by the product being imported.

This is where the delay of 3-5 seconds kick in. The left value needs to update everything that has an ID greater than 252493, that are 96714 items. The right has to to the same and that are 96716 items. So in total 193430 updates for 1 product. Looking at our test file of 30 records this brings the total to 5,802,900 updates in total. You can imagine how many queries that are for the total file.

This also explains why we couldn't reproduce it. Our asset table is 16KB compared to the 40MB your site has.

What is the solution?
As long as Joomla doesn't have to insert it somewhere in the middle of the tree, the delay is not there. Adding an item to the end of the tree only requires a small update, nothing like the one I described above. Updating existing products won't be a problem either because the asset for an existing product already exists.

First order of business would be to clean up the products and articles. Perhaps clean them all out. After that clean up the assets table. Since this is a precarious job, I would recommend to use the PWT ACL extension for that. This will make sure all references are intact.

The other option of course is to setup a clean site.

Once you have that, you can import the file. Now if I am not mistaken, this should go without the 4 second delay because the products are inserted after each other, assuming categories are not mixed etc. Doing the 30 file import after setting up a clean site, I no longer saw the delay.

This does not mean that in future imports the delay will never occur. The bigger the asset table, the more likely a delay will be if products need to be inserted somewhere in the tree rather than at the end. In this case you are going to need more server resources to process that asset table faster.

For what it is worth, I am glad to have found the root cause of the delay.

I am relieved that you have found that. I will report back to my server staff (we had a meeting about it today ourselves). We were sure it is part of the code. I will get back to you tomorrow with our plan. Honestly, the only really new content going into this site will be products. I really dont' understand the asset control so anything you can explain is very helpful. I will drop the data on my test site, and clean up the assets table. I'll report back to you tomorrow.

Hello,

We were sure it is part of the code.

Yes, code is part of it, server resources are another of course. If you have more server resources that could negate the code part.

I really dont' understand the asset control so anything you can explain is very helpful.

I have tried to explain it but going to give it another try in hopefully laymen terms.

A Joomla article has restrictions on who can view/edit the article. These settings are stored in the asset table. Does that make it clear?

Yes, the code is part of it, server resources are another of course. If you have more server resources that could negate the code part.
The server resources are set fairly high already. It is a shared environment though. Even the clients hosting now after talking with their support is set higher than
normal.

A Joomla article has restrictions on who can view/edit the article. These settings are stored in the asset table. Does that make it clear?
Yes, I understood what you said, I just don't understand the asset table end result, but I'm sure I will get to know that better.

I will get back to you (maybe not today pretty busy schedule today) on dropping the data and following your recommendations.

Thank you again for continuing to support this project.

I am ready to move forward with some test but I am concerned about the assets table.

You state: First order of business would be to clean up the products and articles. Perhaps clean them all out. I have run queries to do this. After that clean up the assets table. Since this is a precarious job, I would recommend using the PWT ACL extension for that. This will make sure all references are intact. Regarless of the tool, what exactly am I looking for here. I know that I want to remove any data references for these products. Without the products, the site only has 6 pages of articles .

It seems again like overkill to download and purchase this PWT ACL. First I dont' even know what I would do to this table data.

Please advise.

Hello,

First I dont' even know what I would do to this table data.

You have to make sure you keep the data integrity in place. This means that the nested tree is correct, in other words all lft and rgt values must be correct. That is what you need to take care of.

I only gave PWT ACL as an option, I didn't say you must buy it. You are free to do as you like.

Cleaning up database tables is really not something we support as this has nothing to do with our product. Although I have just given you the info that you need, I can't go into the details of how to do this step-by-step.