Bug performance page Room Type backoffice

[wdammak]

With the new modifications of the products(room types) page in the backoffice, you have added the module to filter by country, this has slowed down the page a lot because this module is not called once but for each product/room types ! so if you have 3000 room types it will make n*3000 requests... please confirm, check and correct

[wdammak]

I disabled the filter module but the problem still persists! The products page in the backoffice takes more than 30min to display! before that was not the case.

There is a part of code that goes through all the products (room types) in the database instead of just the page product limit (20, 50, 100,....) this will certainly slow down the page once the number of products increases.

[shreesh-webkul]

@wdammak

Can you let us know which module you are referring to, as there are no filter module that are attached to products page in BackOffice.

Also in your last reply you are referring to a part of code but not provided in the message, please provide us the code again so that we can debug and resolve the issue.

[wdammak]

@wdammak

Can you let us know which module you are referring to, as there are no filter module that are attached to products page in BackOffice.

Also in your last reply you are referring to a part of code but not provided in the message, please provide us the code again so that we can debug and resolve the issue.

After debugging, the problem comes from this request when it is called in getList of the AdminController class. This query is poorly optimized (abscence of an index somewhere?)

https://github.com/webkul/hotelcommerce/blob/c8bdac6cdd18b793ed4b10e37506736f444eaa10/controllers/admin/AdminProductsController.php#L196-L223

The problem may not arise when the number of products is small, but once the number becomes large this query is extremely slow! (probably because of index during a join)

[wdammak]

@shreesh-webkul The select of aa.city and shop.name AS shopname, which causes this slowdown

For shop.name AS shopname this we can add an index on the name because the table is generally not more than 10 rows.

On the other hand for the field aa.city it is necessary either to create a function which seeks its value when one needs with the id_address otherwise to create a new table address_city (id_city, id_country, id_state, name, active) and to replace address. city by address.id_city

The table address can easily exceed 30K lines (like my case) and therefore an index on city with sometimes null values its indeation will not solve the problem

The creation of an address_city table will also lighten the address table and put more organization, the cities of the whole world are almost all known, putting them in a single table will be clean to manage and present.

[wdammak]

CREATE TABLE IF NOT EXISTS PREFIX_cities ( id_city int(11) NOT NULL AUTO_INCREMENT, name varchar(30) NOT NULL, state_id int(11) NOT NULL, PRIMARY KEY (id_city) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

data all cities in the word: https://github.com/hiiamrohit/Countries-States-Cities-database/blob/master/cities.sql

[shreesh-webkul]

We are checking the issue. Will let you once we have an update on this

[wdammak]

I implemented it and it solved my problem of slowness, I also added the page where to change/add/delete the cities like states/countries. If you decided to adopt it I will communicate the commits.