Send data tab takes 22 minutes to load

  • leomoon
    Participant
    # 1 month ago

    Tab 2.3. takes about 22 minutes to load. My site may be pushing the limits of Woocommerce as a platform, but this long of a loading time started somewhere about version 21.4 or so.

    Since wordfence won’t let me copy/paste sql statements here, here is a link to a text file containing three super-slow queries:

    https://1drv.ms/t/s!Ah6mDhPS2VbWiKpKTi5lO5EiepH2lQ

    Also, the indexing itself is super-slow. I used to be able to index 65000 products in a matter of minutes, but now it takes like half an hour for each 300 chunk.

    wpsolr
    Keymaster
    # 1 month ago

    Can you deselect all taxonomies in screen 2.1, to keep only your post types, and see if things are faster?

    Or you can deselect taxonomies in the indexing screen to check the indexing speed.

    Which screen 2.3 ? (“Search” or “suggestions”)

    leomoon
    Participant
    # 1 month ago

    Sorry, not 2.3, but just tab 3: Send your data.

    I’ll try 2.1. deselections now (waiting for that page to load too).

    leomoon
    Participant
    # 1 month ago

    Indeed, upon deselecting taxonomies and leaving only the Product type, both tab 3 and indexing is super fast as before. I’ll try to add taxonomies one at the time.

    leomoon
    Participant
    # 1 month ago

    Adding just a single taxonomy, such as a book author increases 3. tab loading time to almost 10 minutes and slows down indexing to the point of unusability.

    wpsolr
    Keymaster
    # 1 month ago

    I guess “book author” is a product attribute. How many terms in it?

    leomoon
    Participant
    # 1 month ago

    Yes, it is an attribute. It has some 25000+ terms. There are about 65000 products themselves.

    wpsolr
    Keymaster
    # 1 month ago

    I will make stress tests.

    The difference between taxonomy and post types indexing is that taxonomy terms does not have a publication date. Hence, no way to perform incremental indexing. So, the query that retrieves taxonomy terms to index is a join with a custom table that stores the last indexed date for the taxonomy terms. This join could be the reason of the slowness.

    leomoon
    Participant
    # 1 month ago

    I understand. If you have a query I could execute to test if this is a problem, please do send it to me and I’ll run it against my DB.

    wpsolr
    Keymaster
    # 1 month ago

    The queries are the ones you sent me already.

    wpsolr
    Keymaster
    # 1 month ago

    I made my tests with 1000 terms, and everything was good.

    But with 10,000 terms, the SQL to retrieve terms is really slow. It appears a “sql inner join” between 10 thousands and 10 thousands terms is not viable.

    I made some preliminary tests to replace it with a slightly modified ” sql union”, and it’s very fast for the same 10 thousand terms.

    After verification of the speed, I’m now checking if all the other parts of the indexing code can be adapted too. Not trivial, because the code was custom built around the “sql inner join”.

    wpsolr
    Keymaster
    # 1 month ago

    Here is a new version with the fix: https://www.dropbox.com/s/r9zyw53xx2kat03/wpsolr-pro-taxonomies-indexing-fast.zip?dl=0

    Tested with 10,000 terms indexed in Elasticsearch. At least as fast as with post types indexing.

    leomoon
    Participant
    # 1 month ago

    Thanks for working on this so quickly. It looks like this table:

    WordPress database error: [Table ‘woo.wpsolr_acceptance_wpsolr_index_history’ doesn’t exist]

    Is not being created at any point. I tried removing/re-adding plugin, but it still gives that error on send data.

    wpsolr
    Keymaster
    leomoon
    Participant
    # 4 weeks, 1 day ago

    Send data loads in seconds now and tables are there so no errors about that.

    • This reply was modified 4 weeks ago by  wpsolr. Reason: Asked author to move text to another topic
Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic.