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

Bagisto

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

1 Million products!

General Discussion
2
12
1.2k
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 24 Oct 2019, 06:54

    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 24 Oct 2019, 06:59

      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 24 Oct 2019, 15:07

        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 25 Oct 2019, 07:03

          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 25 Oct 2019, 08:31

            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 28 Oct 2019, 07:48 28 Oct 2019, 07:46

              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 29 Oct 2019, 06:28

                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 29 Oct 2019, 06:33

                  Hello

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

                  Thanks

                  1 Reply Last reply Reply Quote 0
                  • G
                    george last edited by 29 Oct 2019, 07:52

                    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 30 Oct 2019, 07:46

                      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 30 Oct 2019, 10:43

                        This post is deleted!
                        1 Reply Last reply Reply Quote 0
                        • R
                          rahul last edited by 31 Oct 2019, 14:25

                          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
                          1 out of 12
                          • First post
                            1/12
                            Last post