1 Million products!

[george]

Hi, guys!

Trying to import 1M products, and have got some speed degradation (metrics on 100 and 200 000 products).

• in "categories" section: infinite loading, blank screen, 100% cpu, only fix is rebooting
• "product form" page: loading time from less then 1s goes to 3-5s
• importing, add 1 product: 3x time to add
• admin panel, products: loading time from 1s goes to 7s

Could you help me please,

• how can i get a query monitoring for optimization?
• is there the best product fields organization? all fields to flat table or simpler?

thank you

[george]

Forget to ask, will it work on 6M products? Do you try it already?

[george]

There is the query that executes 2 seconds on opening "product form":

select * from product_flat where url_key = '7637325' and locale = 'en' and channel = 'default'

There are 330 000 products already.

[george]

just added indexes to fields 'url_key', 'locale', 'channel',
now query lasts only 0.0053 sec!
Note that to my 300mb base added 30mb of indexes.

[george]

In admin panel, products section page (/admin/catalog/products)

• 5 seconds! /packages/Webkul/Ui/src/DataGrid/DataGrid.php:262
  select count(*) as aggregate from product_flat left join products on product_flat.product_id = products.id left join attribute_families on products.attribute_family_id = attribute_families.id left join product_inventories on product_flat.product_id = product_inventories.product_id where channel = 'default' and locale = 'en' group by product_flat.product_id

plus

• 7 seconds! /packages/Webkul/Ui/src/DataGrid/DataGrid.php:262
  select product_flat.product_id as product_id, product_flat.sku as product_sku, product_flat.name as product_name, products.type as product_type, product_flat.status, product_flat.price, attribute_families.name as attribute_family, SUM(product_inventories.qty) as quantity from product_flat left join products on product_flat.product_id = products.id left join attribute_families on products.attribute_family_id = attribute_families.id left join product_inventories on product_flat.product_id = product_inventories.product_id where channel = 'default' and locale = 'en' group by product_flat.product_id order by product_id desc limit 20 offset 0

please, help me to optimize this for big catalog.

[george]

In file /packages/Webkul/Ui/src/DataGrid/DataGrid.php:

abstract class DataGrid -> public function getCollection()

this function is slow on big amount of products

$this->collection = $this->queryBuilder->orderBy($this->index, $this->sortOrder)->paginate($this->itemsPerPage)->appends(request()->except('page'))

it's generate aggregation query:

select count(*) as aggregate from product_flat left join products on product_flat.product_id = products.id left join attribute_families on products.attribute_family_id = attribute_families.id left join product_inventories on product_flat.product_id = product_inventories.product_id where channel = 'default' and locale = 'en' group by product_flat.product_id

on my 700 000 products it takes 8sec.
please, help to get faster this query on big catalog!

Second query become faster than comment code in public function prepareQueryBuilder() in class ProductDataGrid extends DataGrid of ProductDataGrid.php:
comment ->leftJoin('attribute_families'...
comment ->leftJoin('product_inventories'...

edit ->select('product_flat.product_id as product_id'... to

->select('product_flat.product_id as product_id', 'product_flat.sku as product_sku', 'product_flat.name as product_name', 'product_flat.status', 'product_flat.price')

[rahul]

Hello @george

We gonna apply check on itemPerPage, which can not be more than 100, because due to this it is taking more time.

Thanks

[rahul]

Hello

You can apply it from configuration - https://prnt.sc/pphf6i

Thanks

[george]

Hello, Rahul, it's not helped.
Problems on big catalog (over 300 000 products):

• "main page" loading about 10sec.
• "/categories/xxxx" not working (blank screen, infinite loading)
• admin panel, products: loading 5-10 sec
• product form: 3-5sec.
  Partially has fixed some code, but need some help.

Raw SQL queries with "default items per page" set to 1 or 99 is equally slow.
There are needed some optimization, adding indexes, simplify the queries, and etc.
Could we discuss this problem directly? Then you may add to your marketing phrase "big data" or something.

[rahul]

Hi @george

Can you send me dump of your database so that we can check and optimise it, because it is a big concern.

Thanks

[george]

This post is deleted!

[rahul]

Hi @george

I advice you to generate a ticket for same at https://bagisto.uvdesk.com/en/customer/create-ticket/

Thanks