SOLVED ---Seeing customers with many purchases gets slower.

[gaston22]

Hello, as the title says, when entering the customer list, let the customer see that you have made many purchases, the result is too slow.

I am talking about customers who have more than 15 orders with x number of items.

Prestashop 1.6.1.23 (same error in previous versions of branch 1.6

PHP version: 7.0.33-0 + deb9u5

Memory limit: 1024M

Max execution time: 240

I have updated the Thirty Bees fork in another installation to see if it solved it, but it does the same.

When after 4 minutes it brings the complete information, it shows everything ok, but what it takes is terrible

Any solution?

Thank you.

Edited November 1, 2019 by gaston22 (see edit history)

[musicmaster]

Try to enable profiling in the defines.inc.php file and find at the bottom of the page which query causes the problem.

[gaston22]

Hi musicmaster, I already enabled that, besides it doesn't show anything weird, it takes 3 more times.
I think it is not a mistake, but it is because of the number of records that you have to process, I think you should bring, for example 10 orders and then choose to bring another 10 more and so ...

[musicmaster]

I don't understand what you mean with "it takes 3 more times".

It does show how much seconds each query takes. Together they must fill those 4 minutes.

[gaston22]

Sure, sorry to express myself badly.
When I enable profiling in the defines.inc.php, it shows me the list with all the data, but without any errors, and it also takes longer.
Show the query with your time nothing more.
I know it takes for the type of data you are looking for, but I should be able to accelerate.
This is normal? Or do I have to buy some plugin to change this?

I think that all stores that have many orders, with many items in their orders, the same thing should happen to them.

I am talking about Clients that have for example 40 orders, and in each order they have 30 or more items.

[musicmaster]

I don't know how to get through to you. you keep repeating yourself without acknowledging what I said. Profiling shows the times. Often of the dozens of queries you see at the bottom of the page there is one that takes by far the most of the time. You can study that. And then you can look whether it is possible to optimize it:

 - maybe it is querying tables that you don't use - for example customizations

 - maybe you can add indexes

 - you might even just use it to ask questions on the forum

[gaston22]

Ok you tell me:
- maybe these are unused tables, for example, customizations
- maybe you can add indexes

It means that I would have to touch the code, which is not something that is failing?

[musicmaster]

17 minutes ago, gaston22 said:

Ok you tell me:
- maybe these are unused tables, for example, customizations
- maybe you can add indexes

It means that I would have to touch the code, which is not something that is failing?

Yes. But there might even be possibilities without that.

[razaro]

Hi gaston22 

Just to jump in with few things

1. Like musicmaster said few times did you enable profiling, so not debug mode but profiling ?

https://dh42.com/blog/prestashop-debug-profiling/  it should be loooong list of queries. You could make a screenshot of result.

 

2. I stumbled few times on slow order page, few times it was huge number of vouchers generated and they showed on order details page.

Next, check if you have MailChimp module older versions do get hooked everywhere and some times they sync with server so it takes time.

[gaston22]

Thanks Musicmaster, now I just downloaded your prestools, I think it's fantastic, very good job!

Hi Razaro:
1. If I have enabled it as this page says:

https://dh42.com/blog/prestashop-debug-profiling/ it should be loooong list of queries. You could make a screenshot of result.

2. I don't have any mailchimp module installed

I attached the first part, because everything else is too long:
This customer has 4 carts and the total of 4 cart items is 78 items-

[razaro]

Thank you for sharing screenshot.

And that shows old PHP 5.4  and MySQL 5.0 plus just bare minimum of 128M of memory limit. Not like you mention 

"PHP version: 7.0.33-0 + deb9u5

Memory limit: 1024M

Max execution time: 240"

Strange maybe bug in profiling.

You should check in Advance Parameters > Configuration information what is says

 

But you should check also part after your screenshot, so after Stopwatch SQL - 5509 queries you could scroll down and

see if any of lines on far right have red number that stands out.

 

You did not replay about vouchers / catalog/cart rules. Do you have lot of those defined ?

[gaston22]

Of course, this screenshot is the production server, the other data is from a server where I could upload memory, php and other parameters, but with the same results.

Attached captures of the other server:

Valid orders: 7

URCHASED PRODUCTS 161

Stopwatch SQL - 57862 queries

I didn't know what you wrote to me:
You did not replay about vouchers / catalog / cart rules. Do you have lot of those defined?

 

Edited October 30, 2019 by gaston22 (see edit history)

[musicmaster]

It evades me on which page you are. Are you on the customer page - what shows 50 customers at a time? Or are you exporting a complete csv list of customers.

I checked my Customers page and it doesn't use the cart_product table at all - while in you pictures it is the top consumer. So I get the impression that you have some module that adds extra information.

As you seem a fan of  not telling too much I will add some of my statistics in a webshop where customers have smaller and less orders so that you can compare. This is an old copy of an existing shop: 

Here are my top tables from Tables stress:

And here is my top query - including the information that you see when you scroll to the right:

 

 

 

 

Edited October 31, 2019 by musicmaster (see edit history)

[gaston22]

Hi musicmaster, I don't avoid anything hahaha!!!

I go to Clients> client administration> view client button

this is the address:
http: //www.xxxx.com/admin/index.php?controller=AdminCustomers& id_customer=70&viewcustomer&token = ....

It's viewcustomer

I don't have any plugin that shows too much and I have disabled override

[musicmaster]

There was some misunderstanding: I thought you were talking about the customers overview page while you were talking about the customer detail page. So I am glad you provided the url.

I suspect that much of what you are seeing are abandoned carts. So I suggest you do a cleanup of abandoned carts. That can with Prestools (Tools&Stats->Cleanup) and there are also many other tools around.

[gaston22]

Ok, thanks anyway, maybe it's because my native language is not English.
I have gone to Customers> shopping carts and deleted all abandoned carts, but it's still the same.
another tool?

[musicmaster]

3 hours ago, gaston22 said:

Ok, thanks anyway, maybe it's because my native language is not English.
I have gone to Customers> shopping carts and deleted all abandoned carts, but it's still the same.
another tool?

Nope. It looks like it has to be this way.

However, you might consider a hack by changing the source code:

 - when you look at the customer page it lists all the carts of the customer. This is completely useless information as these carts have either been converted to orders or abandoned. So you might consider omitting this information. To change this you need to edit the source code.

 - I don't which Prestashop version you have. I have at the moment Thirty Bees as test shop but it won't make much difference. So your line numbers and code may differ a bit.

 - in /admin/controllers/admin/adminCustomersController.php you have the function renderView(). Somewhere halfway it contains the line

$carts = Cart::getCustomerCarts($customer->id);

 - That line produces a list of carts that are subsequently researched. Instead of or after this line you should put the line

$carts = array();

That will empty the list and make your code much faster.

 

 

 

[gaston22]

Well musicmaster YOU ARE A GENIUS !!!
Now fly !!!

I will detail what I did:

Prestashop version: 1.6.1.23

Edit File
\ controllers \ admin \ AdminCustomersController.php

line 762
Replace:
$ carts = Cart :: getCustomerCarts ($ customer-> id);
by:
$ carts = array ();

to enjoy!!!!