Send data tab takes 22 minutes to load

  • leomoon
    Participant
    4 years, 9 months ago #12453

    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
    4 years, 9 months ago #12454

    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
    4 years, 9 months ago #12455

    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
    4 years, 9 months ago #12456

    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
    4 years, 9 months ago #12457

    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
    4 years, 9 months ago #12458

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

    leomoon
    Participant
    4 years, 9 months ago #12460

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

    wpsolr
    Keymaster
    4 years, 9 months ago #12461

    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
    4 years, 9 months ago #12462

    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
    4 years, 9 months ago #12463

    The queries are the ones you sent me already.

    wpsolr
    Keymaster
    4 years, 9 months ago #12499

    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
    4 years, 9 months ago #12500

    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
    4 years, 9 months ago #12503

    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 years, 9 months ago #12532

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

    • This reply was modified 4 years, 9 months 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.