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

Hello,

Thank you for the update. Of course hindsight is 20/20 but it has been a learning experience for sure. What I realized the other day is that such an issue can also occur for those using just the Joomla content import as it uses the same tree. This looks more like a core Joomla issue than anything else. Sure, adding 1 article by hand is not as noticeable as importing 12,000 items

Glad to see you on the path of recovery, have a good weekend.

Hi Roland,

Just wanted you to know I have successfully imported all the products 12491 in 11 minutes with image processing. Without it was about 5.

Huge difference. I wish I knew then what I know now .

Ok, thanks.

Just wanted to give you a heads up. I started a VM test. I will report back to you after a few trials.

Hello,

So to clarify, if I skip existing, but there is a change in the data, it would recognize that and import anyway?

No, existing data will not be updated but only the new products will be imported.

So to clarify, if I skip existing, but there is a change in the data, it would recognize that and import anyway? That is one of my issues. If a price changed or a description would it still import the modified info. Of course, we can unpublish the item if it is no longer available.

Hello,

These items aren't changing very much if at all, then a manual addition can be done.

This way I don't think we have to worry too much about the delay, especially if the majority of the products are just updates. You could even specify in the template to skip any existing records, that way only new products would be added and existing ones are left alone.

I'd hate to start all over.

Given that the products change very little it might be an idea to run the big import and go from there. It might take some time but at least they are in and any subsequent import should be faster, especially if you skip existing products. If you do not skip them, then they would just be updates and not needing to insert anything into the asset table.

Hello,
I do apologize for my comment. My frustration got the best of me.

No worries, all good.

Thank you!

Can you tell me if based on my data if you feel that J2Store vs. Virtuemart is not a good choice for this volume of data?

The feeling I have is that you were just bitten by Murphy's Law, whatever could go wrong did go wrong in the worst possible order. It may prove worthwhile to just setup an empty site, with just Joomla + J2Store + CSVI and run your import and see how that goes. This will give you a good idea of what happens on a clean site.

OK

Looking towards the future, the 1 million dollar question is, are you constantly inserting (not updating) 12,000 new products. If they are inserted in the middle of the tree, you will run into the delay. If they are added at the end, it won't be an issue. That is really looking into a glass ball. Updating records isn't an issue since we don't need to create the entry in the tree.

The data is being managed on a different inventory server with no website interface. This is to give a website for their product catalog. At this point they have only set up a full export of products to be imported. They are working on modified dates, etc, but to keep it going that is how it currently is.

If I were to use VM, does it utilize the assets table the same way or is VM more contained within its own tables since it isn't using the Articles as the front end display?

VM doesn't use the asset table, this is just a by-product if you will from J2Store using Joomla articles. VM is only using it's own tables. That's what I thought.
I wanted my client to only have to do 1 import step, not multiple.

By now, I am not fully clear anymore on what steps/imports had to be run. If there are multiple steps you could still make it easy for the client to run a cron job to run the imports needed based on whatever file the client has put on the server. Yes, trying to make it as easy as possible.

I understand the desire to do everyting in a single import but it is also impossible to enter product, category and manufacturer info into a single view in J2Store/VirtueMart. They are different data types. That is something to keep in mind. If the basics are all set most of the time you can do with a single import.

These items aren't changing very much if at all, then a manual addition can be done. They would have to add it to the current inventory manually as well..

I guess the first decision to take is whether you want to continue with J2Store or switch to VM.

I'd hate to start all over. A lot of time has gone into this (at least from the troubleshooting. I guess it's worth looking at by now, but I thought I was close to being done.

Hello,

I do apologize for my comment. My frustration got the best of me.

No worries, all good.

Can you tell me if based on my data if you feel that J2Store vs. Virtuemart is not a good choice for this volume of data?

The feeling I have is that you were just bitten by Murphy's Law, whatever could go wrong did go wrong in the worst possible order. It may prove worthwhile to just setup an empty site, with just Joomla + J2Store + CSVI and run your import and see how that goes. This will give you a good idea of what happens on a clean site.

Looking towards the future, the 1 million dollar question is, are you constantly inserting (not updating) 12,000 new products. If they are inserted in the middle of the tree, you will run into the delay. If they are added at the end, it won't be an issue. That is really looking into a glass ball. Updating records isn't an issue since we don't need to create the entry in the tree.

If I were to use VM, does it utilize the assets table the same way or is VM more contained within its own tables since it isn't using the Articles as the front end display?

VM doesn't use the asset table, this is just a by-product if you will from J2Store using Joomla articles. VM is only using it's own tables.

I wanted my client to only have to do 1 import step, not multiple.

By now, I am not fully clear anymore on what steps/imports had to be run. If there are multiple steps you could still make it easy for the client to run a cron job to run the imports needed based on whatever file the client has put on the server.

I understand the desire to do everyting in a single import but it is also impossible to enter product, category and manufacturer info into a single view in J2Store/VirtueMart. They are different data types. That is something to keep in mind. If the basics are all set most of the time you can do with a single import.

I guess the first decision to take is whether you want to continue with J2Store or switch to VM.

Hi Roland,

I was deleting the products by dropping the data from tables directly using queries to do so. I was not deleting the assets table data.

You have gone above to support me on this issue. I do apologize for my comment. My frustration got the best of me. This is my first time using the J2Store component vs. using Virtuemart, which I have more experience with. Can you tell me if based on my data if you feel that J2Store vs. Virtuemart is not a good choice for this volume of data? I screened the topic with their support prior to making that decision. Now I am second guessing my decision. If I were to use VM, does it utilize the assets table the same way or is VM more contained within its own tables since it isn't using the Articles as the front end display?

I am concerned about the long term processes here. Of course, during the testing and quality assurance phases, the data layouts have changed based on what is available from the fields. Beyond separately adding the categories, I wanted my client to only have to do 1 import step, not multiple.

Hello,

It doesn't matter if you import 12000 articles or 1. If an entry needs to be created within the tree, this can lead to that 3-5 second delay depending on the server resources.

CSVI does do the integrity checks when you import data. If it didn't we wouldn't have to take care of the nested tree and you wouldn't see that delay.

I have no clue how you are deleting your products. If you just delete the Joomla articles, Joomla will clean up the tree. You gave the impression that you wanted to delete data directly from the database table.

I do not feel I am dropping the ball here. Contrary, I do think we have gone beyond regular support to help you figure out what is going on. We will assist further with your imports. However I do want to draw a line that we are not here for all your technical questions. We will help where possible but within reason. I have given my insights on how you could proceed.

Now I feel like you are dropping the ball on me. The data integrity is based on the products being imported by your tool. Am I seriously the only person that has 12k products importing into J2Store?

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.

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.

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.

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?

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,

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.

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.

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.