Bagisto Forum

    Bagisto
    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups

    1 Million products!

    General Discussion
    2
    12
    1211
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • G
      george last edited by

      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

      1 Reply Last reply Reply Quote 0
      • G
        george last edited by

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

        1 Reply Last reply Reply Quote 0
        • G
          george last edited by

          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.

          1 Reply Last reply Reply Quote 0
          • G
            george last edited by

            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.

            1 Reply Last reply Reply Quote 0
            • G
              george last edited by

              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.

              1 Reply Last reply Reply Quote 0
              • G
                george last edited by 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')
                1 Reply Last reply Reply Quote 0
                • R
                  rahul last edited by

                  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

                  1 Reply Last reply Reply Quote 0
                  • R
                    rahul last edited by

                    Hello

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

                    Thanks

                    1 Reply Last reply Reply Quote 0
                    • G
                      george last edited by

                      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.

                      1 Reply Last reply Reply Quote 0
                      • R
                        rahul last edited by

                        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

                        1 Reply Last reply Reply Quote 1
                        • G
                          george last edited by

                          This post is deleted!
                          1 Reply Last reply Reply Quote 0
                          • R
                            rahul last edited by

                            Hi @george

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

                            Thanks

                            1 Reply Last reply Reply Quote 0
                            • First post
                              Last post