What are the Slack Archives?

It’s a history of our time together in the Slack Community! There’s a ton of knowledge in here, so feel free to search through the archives for a possible answer to your question.

Because this space is not active, you won’t be able to create a new post or comment here. If you have a question or want to start a discussion about something, head over to our categories and pick one to post in! You can always refer back to a post from Slack Archives if needed; just copy the link to use it as a reference..

Hi, we have ~ 1.8 million customers in our db, and a similar number of orders. In Zed, the search on

U019WGU7SM9
U019WGU7SM9 Posts: 24 🧑🏻‍🚀 - Cadet
in Help

Hi, we have ~ 1.8 million customers in our db, and a similar number of orders. In Zed, the search on the listing pages for orders and customers has become really slow - sometimes 8-15 seconds. Does anyone have any good tips to speed it up?

Comments

  • Stanislav Matveyev
    Stanislav Matveyev Sprykee Posts: 211 🧑🏻‍🚀 - Cadet

    You can try to overwrite \Spryker\Zed\Gui\Communication\Table\AbstractTable::runQuery

    in \Spryker\Zed\Sales\Communication\Table\OrdersTable and change SQL to be more efficient.

  • U019WGU7SM9
    U019WGU7SM9 Posts: 24 🧑🏻‍🚀 - Cadet

    What would be more efficient though? The query run has a condition like

    WHERE  "spy_customer"."anonymized_at" IS NULL
       AND ( Lower("spy_customer"."id_customer" :: text) LIKE '%searchtext%'
              OR Lower("spy_customer"."email" :: text) LIKE '%searchtext%'
              OR Lower("spy_customer"."first_name" :: text) LIKE '%searchtext%'
              OR Lower("spy_customer"."created_at" :: text) LIKE '%searchtext%'
              OR Lower("spy_customer"."last_name" :: text) LIKE '%searchtext%' 
       )
ORDER  BY "spy_customer"."id_customer" ASC

    Postgres wouldn’t be able to use an index here even if there was a big compound one (which propel doesn’t support anyway)

  • U019WGU7SM9
    U019WGU7SM9 Posts: 24 🧑🏻‍🚀 - Cadet

    I can remove a couple of fields to make it a bit faster, but postgres still has to scan the whole table

  • U019WGU7SM9
    U019WGU7SM9 Posts: 24 🧑🏻‍🚀 - Cadet

    I thought about adding a generated column as a compound of all the searchable fields, but propel doesn’t support that either

  • Stanislav Matveyev
    Stanislav Matveyev Sprykee Posts: 211 🧑🏻‍🚀 - Cadet

    it was just an idea to try...

  • giovanni.piemontese
    giovanni.piemontese Spryker Solution Partner Posts: 871 🧑🏻‍🚀 - Cadet

    the problem is the count query that the table run... the count query take so much time, above all in psql..
    .. because have to scan all rows to check if it visible for the count etc.. (countable)...

    The problem is already known in Spryker since ca. 1 Year and someone has already tried to find a solution but without good results...

    We have tables in ZED with ca. 15Mio rows, 3M Products etc etc... Zed Tables are for us not very manageable at the moment.

  • giovanni.piemontese
    giovanni.piemontese Spryker Solution Partner Posts: 871 🧑🏻‍🚀 - Cadet

    @U019WGU7SM9 try only for joke to disable the count query that happend in the AbstractTable Class and see if the performance will be better or not..

    Don't forget that in the case of search filter, the query executed from AbstractTable (Datatables generic) are 3 !!!

    And on top there is another bug in the search field..: when u tipp something then the JS trigger more table-action in XHR and the last that render the results table in frontend have to wait till the other requests are completed.... (really ridiculous) instead to abort the previuos one and run only one etc...

  • U019WGU7SM9
    U019WGU7SM9 Posts: 24 🧑🏻‍🚀 - Cadet

    ah yes that’s a good idea - I don’t know if I can get away with not showing the number of results however

  • UQJBF1UKW
    UQJBF1UKW Posts: 5 🧑🏻‍🚀 - Cadet

    I recall we had a similar issue and removing th leading % in the LIKE comparision already had a major impact.

    Also the auto submit on the search box should be increased to e.g. 5 seconds or entirely removed

  • U019WGU7SM9
    U019WGU7SM9 Posts: 24 🧑🏻‍🚀 - Cadet

    Hi Tim! Yes I suppose that would make it a lot quicker - and thinking about it it seems like it wouldn’t be a great loss - no-one needs the result of a partial order ref, and searching by customer name is almost pointless with that many orders in the table

Categories